資料庫設計

為什麼要先設計

前兩課看到的 employee 資料庫有五張表格：department、employee、employeeSkills、client、assignment。 為什麼不把所有資料放在一張大表格裡，一列記一位員工的全部資料就好？

一張大表格看起來簡單，但同一份資訊（例如「部門 128 叫 Research and Development」）會在很多列裡重複出現。 重複的資料不只浪費空間，更麻煩的是它們可能彼此矛盾：改了其中一列、漏了另一列，資料庫就不知道哪一個才對。

資料庫設計的目標是：每一項事實只存一次，表格之間用「鍵」連起來，需要時再用查詢（第 07 課的 JOIN）組合回來。 這一課先建立設計的概念，再用正規化的步驟把一張大表格拆開。

實體、屬性與關聯

設計資料庫時，先從需求裡找出三樣東西：

關聯依照數量分成三種：

employee 資料庫的關聯可以畫成：

department 1 ──── N employee 1 ──── N employeeSkills
                        │
                        N
                   assignment（clientID、employeeID、workdate、hours）
                        N
                        │
                     client 1

「一位員工有多項技能」也是一對多：技能不是單一個值，所以另外放在 employeeSkills，一項技能一列。

主鍵、外鍵與三種完整性

主鍵（primary key）是能唯一識別一列的欄位或欄位組合。MySQL 對主鍵有兩個要求：值不可重複、不可以是 NULL。 department 的主鍵是 departmentID；employeeSkills 的主鍵是 (employeeID, skill) 兩個欄位的組合，稱為複合主鍵。

外鍵（foreign key）是「指向另一張表格主鍵」的欄位。employee.departmentID 指向 department.departmentID： 每位員工的部門編號，必須是 department 表格裡真的存在的部門。

原課程把資料的正確性整理成三種完整性（integrity）：

外鍵只在 InnoDB 儲存引擎有作用（MySQL 8.0 的預設引擎）。MyISAM 表格會接受外鍵的寫法，但不會檢查（第 09 課）。

多對多：中介表格

「員工替客戶工作」是多對多。如果在 employee 加一個 clientID 欄位，一位員工就只能有一個客戶； 在 client 加 employeeID，一個客戶就只能有一位員工。兩邊都不對。

做法是另外建一張中介表格 assignment，一列代表「某位員工在某一天替某個客戶工作幾小時」：

主鍵是 (clientID, employeeID, workdate) 三個欄位的組合：同一位員工可以在同一天替不同客戶工作， 也可以在不同天替同一個客戶工作，但「同一位員工、同一天、同一個客戶」只能有一列。

employeeSkills(employeeID, skill) 的結構也一樣：主鍵是兩個欄位的組合，同一位員工可以有很多技能，但同一項技能不會記兩次。

沒有正規化的表格：三種異常

假設把員工資料全部放進一張表格：

employee_flat(employeeID, name, job, departmentID, departmentName, skills)

departmentID 決定了 departmentName：128 一定是 Research and Development，42 一定是 Finance。 但部門名稱跟著每位員工重複存了好幾次，於是產生三種異常（anomaly）：

另外，skills 欄位把好幾項技能寫在同一格（'C, Perl, Java'），想找「會 Java 的員工」只能用 LIKE '%Java%'， 連 JavaScript 也會被找出來；想刪掉其中一項技能，也得改寫整個字串。

正規化：第一到第三正規化

正規化（normalization）是一套逐步拆分表格的規則，每一步消除一種會造成異常的相依關係。 先說明功能相依：如果知道 A 的值就能確定 B 的值，稱為「B 相依於 A」，寫成 A → B。例如 departmentID → departmentName。

以投影片的例子一步一步做：

原始表格：employee(employeeID, name, job, departmentID, departmentName, skill)，skill 一格放好幾項技能。

1NF：skill 拆成一項一列，主鍵變成 (employeeID, skill)。Nora 有 3 項技能，就變成 3 列，姓名、部門跟著重複 3 次。

2NF：主鍵是 (employeeID, skill)，但 name、job、departmentID、departmentName 只要知道 employeeID 就確定了， 和 skill 無關，這是部分相依。把它們移出去：

• employeeSkills(employeeID, skill)
• employee(employeeID, name, job, departmentID, departmentName)

3NF：在新的 employee 表格裡，employeeID → departmentID，而 departmentID → departmentName， 所以 departmentName 是經由 departmentID 間接相依於主鍵，這是遞移相依。把部門名稱移出去：

• employee(employeeID, name, job, departmentID)
• department(departmentID, departmentName)

結果就是 employee 資料庫的三張表格。原本的投影片把「拆出 department」放在第二正規化； 嚴格依照定義，它是遞移相依，屬於第三正規化，最後拆出來的表格相同。

正規化到 3NF 之後，每一項事實只存在一個地方：部門名稱只在 department 存一次，改名只要改一列，沒有員工的部門也記得進去。

練習：訂單表格

投影片的練習題是一張訂單表格：

order(date, customerID, customerName, productID, productName, price, quantity)

找出功能相依：

• customerID → customerName：知道顧客編號就知道顧客姓名
• productID → productName, price：知道商品編號就知道品名與單價
• quantity 要知道「哪一筆訂單的哪一項商品」才能確定

拆開之後：

• customer(customerID, customerName)
• product(productID, productName, price)
• orders(orderID, orderDate, customerID, productID, quantity)：新增流水號 orderID 當主鍵

這是投影片的答案，它假設一張訂單只買一種商品。如果一張訂單可以買好幾種商品， orderDate 與 customerID 只相依於 orderID，quantity 卻相依於 (orderID, productID)，還要再拆成：

• orders(orderID, orderDate, customerID)
• orderItems(orderID, productID, quantity)，主鍵是 (orderID, productID)

另外，order 是 SQL 的保留字（ORDER BY），拿來當表格名稱每次都得加反引號，所以改叫 orders。

重點整理

正規化不是越拆越好：拆開之後查詢要用 JOIN 組回來。實務上通常做到 3NF， 少數為了查詢效能刻意保留重複資料的做法稱為反正規化（denormalization），要清楚知道代價才使用。

下一課用 CREATE DATABASE、CREATE TABLE 把這個設計實際建立起來。