MySQL – 資料庫設計與正規化

主題：避免資料重複與異常

---

簡介

在實務開發中，資料庫是系統的核心，它負責儲存、查詢與維護所有業務資訊。若資料表設計不當，最常見的問題就是 資料重複（duplicate data）與 資料異常（anomaly）。這不僅會浪費儲存空間，還會導致：

1. 更新不一致：同一筆資訊在多個位置被修改時，若忘記同步，就會出現衝突。
2. 插入/刪除限制：因為資料依賴關係錯亂，可能無法順利新增或刪除資料。
3. 查詢效能下降：重複資料增加了掃描的行數，索引也變得不夠精確。

因此，正規化（Normalization） 成為資料庫設計的基礎技術。本文將以 MySQL 為例，說明如何透過正規化避免重複與異常，並提供實作範例、常見陷阱與最佳實踐，幫助初學者到中級開發者建立可靠的資料模型。

---

核心概念

1. 正規化的目的與階段

正規化是一套 將資料分解成多個相關聯的表格、以消除冗餘並確保資料一致性的理論。常見的正規形（Normal Form）包括：

正規形	目的	典型條件
1NF（第一正規形）	消除欄位重複與多值屬性	每個欄位只能儲存原子值（不可再分）
2NF（第二正規形）	消除部分依賴（非主鍵欄位只依賴於主鍵的一部份）	必須先符合 1NF，且所有非鍵欄位完整依賴於整個主鍵
3NF（第三正規形）	消除傳遞依賴（非鍵欄位依賴於其他非鍵欄位）	必須先符合 2NF，且每個非鍵欄位直接依賴於主鍵
BCNF（巴斯-科德正規形）	處理主鍵候選鍵之間的依賴	每個決定因素（determinant）都是候選鍵

實務上，大多數系統只需要達到 3NF 或 BCNF，即可有效避免重複與異常。

---

2. 常見的資料異常類型

異常類型	說明	例子
插入異常（Insertion Anomaly）	新增資料時，因缺少必要欄位而無法完成	若「員工」表同時儲存「部門資訊」，新增僅屬於新部門的部門資訊時，需要同時有員工資料，造成限制
更新異常（Update Anomaly）	同一筆資訊在多個位置需同步更新，若漏更新會產生不一致	部門負責人變更，若部門名稱在多筆員工記錄中重複，需要逐筆更新
刪除異常（Deletion Anomaly）	刪除某筆資料時，意外刪除了其他重要資訊	刪除最後一位員工後，部門資訊也被一起刪除，導致部門資料遺失

---

3. 從「未正規化」到「正規化」的步驟

以下以 訂單系統 為例，說明如何一步步將資料表正規化。

3.1 未正規化的範例（僅 1NF）

CREATE TABLE OrderInfo (
    order_id      INT PRIMARY KEY,
    order_date    DATE,
    customer_id   INT,
    customer_name VARCHAR(50),
    product_ids   VARCHAR(255),   -- 以逗號分隔的多值欄位 (違反 1NF)
    product_names VARCHAR(255),   -- 重複儲存產品名稱
    total_amount  DECIMAL(10,2)
);

問題：

• product_ids、product_names 為 多值屬性，違反 1NF。
• customer_name 重複出現在每筆訂單，若客戶名稱變更，需要更新所有訂單，產生 更新異常。

3.2 轉換至 1NF（拆分多值欄位）

CREATE TABLE OrderInfo (
    order_id      INT PRIMARY KEY,
    order_date    DATE,
    customer_id   INT,
    total_amount  DECIMAL(10,2)
);

CREATE TABLE OrderProduct (
    order_id    INT,
    product_id  INT,
    quantity    INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES OrderInfo(order_id),
    FOREIGN KEY (product_id) REFERENCES Product(product_id)
);

此時已符合 1NF，每個欄位皆為原子值。

3.3 轉換至 2NF（消除部分依賴）

若 OrderInfo 中的 customer_name 仍然存在，會形成 部分依賴（customer_name 只依賴 customer_id）。將客戶資訊抽出：

CREATE TABLE Customer (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(50),
    email         VARCHAR(100)
);

ALTER TABLE OrderInfo
    ADD CONSTRAINT fk_customer
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id);

現在 OrderInfo 僅保留外鍵 customer_id，符合 2NF。

3.4 轉換至 3NF（消除傳遞依賴）

假設 Product 表還有 category_name，而 category_name 依賴於 category_id，若直接放在 Product 中會產生 傳遞依賴。抽出類別表：

CREATE TABLE Category (
    category_id   INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE Product (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    price        DECIMAL(10,2),
    category_id  INT,
    FOREIGN KEY (category_id) REFERENCES Category(category_id)
);

此時所有非鍵欄位皆直接依賴於主鍵，達到 3NF，資料重複與異常已大幅降低。

---

4. 程式碼範例（實作與測試）

以下提供 3~5 個實用範例，示範如何在 MySQL 中檢查、預防與解決重複與異常問題。

範例 1：使用 UNIQUE 約束防止重複

-- 防止同一客戶的 email 重複
CREATE TABLE Customer (
    customer_id   INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL,
    email         VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_email (email)   -- <--- 重要！保證唯一性
);

說明：UNIQUE 會在插入或更新時自動檢查，若違反則拋出錯誤，避免 重複資料。

範例 2：使用 ON DELETE RESTRICT 防止刪除異常

CREATE TABLE OrderInfo (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    order_date  DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id)
        REFERENCES Customer(customer_id)
        ON DELETE RESTRICT   -- 若客戶仍有訂單，禁止刪除
);

說明：RESTRICT 可保護參照完整性，避免因刪除主表資料而產生孤兒記錄。

範例 3：使用 TRIGGER 自動同步更新（解決更新異常）

DELIMITER $$

CREATE TRIGGER trg_update_customer_name
AFTER UPDATE ON Customer
FOR EACH ROW
BEGIN
    UPDATE OrderInfo
    SET customer_name = NEW.customer_name   -- 假設 OrderInfo 仍保留副本欄位
    WHERE customer_id = NEW.customer_id;
END$$

DELIMITER ;

說明：若因業務需求必須在訂單表保留客戶名稱快照，可使用觸發器自動同步，減少手動更新錯誤。

範例 4：使用 VIEW 產生彙總查詢，避免重複寫 Join

CREATE VIEW v_customer_orders AS
SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM Customer AS c
JOIN OrderInfo AS o ON c.customer_id = o.customer_id;

說明：VIEW 把正規化後的多表查詢封裝起來，讓開發者在應用層只需要簡單 SELECT，降低 查詢錯誤 的機會。

範例 5：使用 CHECK 約束保證資料合理性（防止異常資料）

CREATE TABLE Product (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price        DECIMAL(10,2) NOT NULL,
    stock_qty    INT NOT NULL,
    CHECK (price >= 0),               -- 價格不可為負
    CHECK (stock_qty >= 0)            -- 庫存不可為負
);

說明：CHECK 約束在 MySQL 8.0+ 已支援，可直接在資料層防止不合理的數值輸入。

---

常見陷阱與最佳實踐

陷阱	為什麼會發生	推薦做法
過度正規化	把資料拆得太細，導致大量 Join，效能下降	只正規化至 3NF，若查詢頻繁可考慮適度反正規化（如加入冗餘欄位作快取）
忽略索引	正規化後的外鍵欄位未加索引，JOIN 成本高	為每個 外鍵 加上 INDEX，並根據查詢模式建立複合索引
使用 NULL 代替缺失資料	NULL 會使唯一性約束失效，產生隱藏重複	盡量使用 預設值 或 分離表（例如 CustomerContact）來表示缺失資訊
未使用事務	多表寫入時若出錯，資料會不一致	使用 START TRANSACTION … COMMIT/ROLLBACK 包住相關 DML，確保原子性
忘記資料類型的選擇	用 VARCHAR 存數字、日期等，浪費空間且比較慢	依據實際屬性選擇 INT、DATE、DECIMAL 等合適類型

最佳實踐總結：

1. 先設計概念模型（ER Diagram），確認實體與關聯，再逐步轉換為正規化表格。
2. 每新增一個欄位或表格，都檢查依賴關係，確保不會產生部分或傳遞依賴。
3. 使用外鍵、唯一鍵、檢查約束 強化資料完整性。
4. 建立適當的索引，同時注意索引的維護成本。
5. 寫測試腳本（INSERT/UPDATE/DELETE）驗證是否仍會出現異常情況。

---

實際應用場景

1. 電子商務平台的訂單管理

在大型電商系統中，訂單、商品、客戶、庫存 等資訊分散在多個表格。若只保留單一 Orders 表，會導致：

• 每筆訂單都重複儲存客戶地址、商品名稱、庫存狀態。
• 客戶地址變更時，需要更新所有歷史訂單，違反 GDPR「不可更改歷史紀錄」的要求。

透過正規化，我們將 客戶資訊、商品資訊、庫存 分別抽出，並使用 外鍵 連結。這樣：

• 插入異常 消失：新增一筆訂單只需要提供客戶 ID、商品 ID、數量即可。
• 更新異常 消失：客戶地址變更只在 Customer 表更新一次。
• 刪除異常 可控：若刪除商品，只能在沒有相關訂單的前提下執行，或使用 軟刪除（is_deleted 標記）維持歷史紀錄。

2. 人力資源系統的員工與部門

假設部門主管變更，只需要在 Department 表更新 manager_id，而不必遍歷所有員工記錄。若未正規化，可能會在 Employee 表同時存 department_name、manager_name，導致 更新異常。

3. 金融帳務的交易日誌

金融系統要求 不可遺漏、不可重複 的交易紀錄。使用 唯一索引（如 transaction_id）與 CHECK 約束，確保每筆交易的金額、日期範圍正確，同時把 帳戶資訊、交易類型 分離，避免交易表因重複儲存帳戶資料而產生 插入異常。

---

總結

• 正規化 是避免資料重複與異常的根本方法，從 1NF → 2NF → 3NF（甚至 BCNF）逐步消除多值屬性、部分依賴與傳遞依賴。
• 在 MySQL 中，外鍵、唯一鍵、CHECK 約束、索引 等機制是實作正規化的關鍵工具。
• 實務上，過度正規化會影響效能，適度的反正規化（如快取欄位、物化視圖）是必要的妥協。
• 常見的 插入、更新、刪除異常 可透過正規化與事務（Transaction）避免，亦可使用觸發器（Trigger）或視圖（View）提升開發效率。
• 最後，持續檢視資料模型、寫測試腳本、監控效能，才能讓資料庫在高速變化的業務需求下保持 一致性、完整性與可擴展性。

關鍵一句話：「設計一次，維護永遠」——一次正確的正規化設計，能為未來的系統演進省下無數的時間與成本。祝你在 MySQL 的資料庫設計旅程中，寫出乾淨、可靠且高效的資料模型！