實作練習~公司員工訂餐系統

某公司希望設計一個提供員工中午訂餐的服務,該系統需求如下:

(1)該系統有數個餐廳的餐點提供員工點餐。
(2)該系統在每天早上讓員工線上點餐。
(3)為方便結帳,該系統採用預付制,也就是先存錢給秘書,然後依照訂餐扣款。
(4)希望每個訂餐依照所訂購的每位同事帳戶下扣款。
(5)每個每個人可以訂購多項餐點,也可以跨不同餐廳訂購。
(6)員工帳戶資料要能夠記載預付時間、金額,以及扣款時間、金額及對應的餐點。
(7)該系統必須能夠提供每位員工統計報告,記載每月的餐費。
(8)該系統必須能夠統計各餐廳/餐點的消費紀錄,以便知道員工對於餐廳/餐點的喜好。

該系統的 ERD應該如何設計呢? 該系統的實際資料庫應該如何設計呢?

步驟一: 先找出物件 (Entity)


員工(Employee)、餐廳(Restaurant)、餐點(Item)、帳戶(Account)、帳戶紀錄(Account Log)、訂單(Order)、訂單紀錄(Order Item),我們也可以再多出一個部門物件,以紀錄員工的部門。

所以總共八個物件。

步驟二: 再找出物件間的關係 (Relationship)


餐廳(Restaurant) --> 餐點(Item)
員工(Employee) --> 訂單(Order)
訂單(Order) --> 訂單紀錄(Order Item)
訂單(Order) --> 帳戶紀錄(Account Log)
員工(Employee) --> 帳戶(Account)
帳戶(Account) --> 帳戶紀錄(Account Log)
訂單紀錄(Order Item) --> 餐點(Item)
員工(Employee) --> 部門(Department)


步驟三: 檢視ERD的可能錯誤,及補上應該補上的屬性 (Attribute)


員工(Employee) : 員工編號(eid)、部門編號(did)、姓名(ename)
部門(Department) : 部門編號(did)、部門名稱(dname)

餐廳(Restaurant) : 餐廳編號(rid)、餐廳名稱(rname)
餐點(Item) : 餐廳編號(rid)、餐點流水號(serial)、餐點名稱(iname)、餐點價格(iprice)

訂單(Order) : 訂單編號(oid)、訂購人員(eid)、訂單日期(odate)、訂單時間(otime)
訂單紀錄(Order Item) : 訂單編號(oid)、訂單流水號(serial)、餐點代號(rid+serial)、數量(amount)、餐點價格(oprice)

為何訂單紀錄需要餐點價格? 因為餐點(Item)中的餐點價格價格可能變動,如果沒有把價格抓到訂單紀錄中,很可能會跟帳戶紀錄有出入。

例如,2015/01/01訂購了陽春麵,價格是40元,因此帳戶紀錄扣除了40元。但是到2015/04/01可能調漲成為45元,Item表單中的iprice就變成45元,這時的訂購單的陽春麵都變成45元,但是帳戶紀錄上卻是40元,可能造成對帳出現問題,因此最好在訂單紀錄(Order Item)也加入餐點價格的欄位,每次訂購餐點時,把價格寫進來。

帳戶(Account) : 員工編號(eid)、帳戶餘額(balance)
帳戶紀錄(Account Log) : 員工編號(eid)、紀錄流水號(serial)、紀錄日期(adate)、紀錄時間(atime)、金額(amount)、訂單對應代號(oid) ~ 如果是入賬就沒有訂單對應代號

上述的資料表,為了要滿足(4)希望每個訂餐依照所訂購的每位同事帳戶下扣款。所以每個訂購單只能包含一位訂購人的餐點,不能多人合寫一張訂購單。


mysql> create database myorder
    -> character set utf8
    -> collate utf8_general_ci;
Query OK, 1 row affected (0.05 sec)

mysql> use myorder;
Database changed

mysql> create table employee (
    -> eid char(5) not null,
    -> did char(5),
    -> ename varchar(20),
    -> primary key(eid));
Query OK, 0 rows affected (0.16 sec)

mysql> create table department (
    -> did char(5) not null,
    -> dname varchar(20),
    -> primary key (did));
Query OK, 0 rows affected (0.14 sec)

mysql> create table restaurant (
    -> rid char(5) not null,
    -> rname varchar(20),
    -> primary key (rid));
Query OK, 0 rows affected (0.15 sec)

mysql> create table item (
    -> rid char(5),
    -> serial char(5) not null,
    -> iname varchar(20),
    -> iprice int(4),
    -> primary key (rid,serial));
Query OK, 0 rows affected (0.17 sec)

mysql> create table myorder (
    -> oid char(5) not null,
    -> eid char(5) not null,
    -> odate char(8),
    -> otime char(4),
    -> primary key(oid));
Query OK, 0 rows affected (0.17 sec)

mysql> create table orderitem (
    -> oid char(5) not null,
    -> serial char(5) not null, 
    -> uniqueid char(10),
    -> amount int(3),
    -> oprice int(4),
    -> primary key (oid,serial));
Query OK, 0 rows affected (0.19 sec)
上面的serial其實也可以省略
把pk改為oid, uniqueid
因為一個訂單內相同餐點不會重複
如果重複,更改amount即可

mysql> create table account (
    -> eid char(5) not null,
    -> balance int(6),
    -> primary key(eid));
Query OK, 0 rows affected (0.14 sec)
其實account表單是否需要,也可以討論

mysql> create table accountlog (
    -> eid char(5) not null,
    -> serial char(5) not null,
    -> adate char(8),
    -> atime char(4),
    -> amount int(6),
    -> oid char(5),
    -> primary key (eid,serial));
Query OK, 0 rows affected (0.19 sec)
有oid值的紀錄,表示是扣款
沒有oid值的紀錄,表示是存款

我們如何使用該系統來提供每位員工統計報告,記載每月的餐費呢?
select eid,sum(amount) from accountlog where oid is not null group by substr(adate,1,6), eid;




我們如何使用該系統知道員工對於餐廳/餐點的喜好呢?
select substr(uniqueid,1,5) restaurant, sum(amount) from orderitem group by restaurant;

可以看到餐廳被點餐的次數如下


select uniqueid, sum(amount) from orderitem group by uniqueid;
可以看到餐點被點餐的次數如下


select iname,iprice from item i,orderitem o where concat(i.rid,i.serial)= o.uniqueid;
可以看到曾經被點的餐點名稱及價格




其實最初,原本想出來的舊結構是長如下的樣子 .... 但是發現有些問題,才慢慢修改上面的樣子。

你可以參考看看,以下舊結構可能有甚麼問題呢?

mysql> show tables; (resturant打錯字)
+-------------------+
| Tables_in_myorder |
+-------------------+
| account           |
| accountlog        |
| employee          |
| food              |
| myorder           |
| orderitem         |
| resturant         |
+-------------------+
7 rows in set (0.00 sec)

mysql> desc account; (發現根本不需要aid,以eid就可以了)
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| aid     | char(5) | NO   | PRI | NULL    |       |
| eid     | char(5) | YES  |     | NULL    |       |
| balance | int(4)  | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> desc accountlog; (去除aid,lid改以eid+serial代替,並以oid為扣款依據)
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| lid    | char(5)  | NO   | PRI | NULL    |       |
| aid    | char(5)  | YES  |     | NULL    |       |
| adate  | char(10) | YES  |     | NULL    |       |
| amount | int(3)   | YES  |     | NULL    |       |
| uniqid | char(10) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql> desc employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid   | char(5)     | NO   | PRI | NULL    |       |
| ename | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> desc food;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| fid    | char(5)     | NO   | PRI | NULL    |       |
| fname  | varchar(20) | YES  |     | NULL    |       |
| fprice | int(3)      | YES  |     | NULL    |       |
| rid    | char(5)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)

mysql> desc myorder; (原本設計是多人可以同單,但是反而增加複雜度,只需規定一人一單即可簡化資料庫,所以myorder跟orderitem都變了)
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| oid   | char(5)  | NO   | PRI | NULL    |       |
| odate | char(10) | YES  |     | NULL    |       |
| otime | char(4)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> desc orderitem;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| oiid   | char(5) | NO   | PRI | NULL    |       |
| oid    | char(5) | YES  |     | NULL    |       |
| amount | int(3)  | YES  |     | NULL    |       |
| ctime  | char(4) | YES  |     | NULL    |       |
| eid    | char(5) | YES  |     | NULL    |       |
| fid    | char(5) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> desc resturant;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| rid   | char(5)     | NO   | PRI | NULL    |       |
| rname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

當然有些條件的實現,還要靠程式來完成,例如最後account的balance計算等,並沒有在資料庫設計上實現,所以你可以思考看看,那些功能是可以透過程式來完成? 那些是資料庫來完成? 哪種方式會比較簡化或是降低開發與維護成本?

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理