MySQL ─ 資料庫設計與正規化：一到三正規化 (1NF‧2NF‧3NF)

---

簡介

在關聯式資料庫的設計過程中，正規化是確保資料一致性、降低冗餘、提升查詢效能的核心技巧。即使在 MySQL 等現代資料庫系統中，若表格結構未經正規化，仍會出現更新異常（Update Anomaly）、刪除異常（Delete Anomaly）或插入異常（Insert Anomaly），長期下去會導致資料品質難以維護。

本篇文章聚焦於 第一正規化 (1NF)、第二正規化 (2NF) 以及第三正規化 (3NF)，從概念說明、實作範例到常見陷阱與最佳實踐，帶領讀者一步步將「雜亂」的資料表轉變成 結構清晰、易於維護 的模型。文章適合剛踏入資料庫設計的初學者，也能為已有開發經驗的中級工程師提供實務參考。

---

核心概念

1️⃣ 第一正規化 (1NF) – 原子性與唯一列

定義：每一個欄位的值必須是不可再分的原子值，且同一欄位內的資料型別必須一致。

為什麼重要：

• 允許使用標準的 SQL 語法（SELECT、WHERE、JOIN）進行精確查詢。
• 防止「欄位裡塞多筆資料」的情況，避免在程式碼中自行拆解字串，降低錯誤率。

範例 1：未正規化的訂單明細

-- 假設一筆訂單的商品資訊被存成逗號分隔的字串
CREATE TABLE orders_raw (
    order_id   INT PRIMARY KEY,
    customer   VARCHAR(50),
    product_list VARCHAR(200)   -- 例如: 'P001:2,P005:1,P009:3'
);

問題：product_list 同時包含多筆商品編號與數量，違反 1NF 的「原子性」原則，查詢「所有購買了商品 P005 的訂單」將非常困難。

範例 2：符合 1NF 的訂單與訂單明細

CREATE TABLE orders (
    order_id   INT PRIMARY KEY,
    customer   VARCHAR(50)
);

CREATE TABLE order_items (
    order_id   INT,
    product_id VARCHAR(10),
    qty        INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

說明：將商品資訊拆成 order_items 子表，每筆記錄只存一個商品與數量，完全符合 1NF。

---

2️⃣ 第二正規化 (2NF) – 消除部分函式相依

定義：在已符合 1NF 的前提下，所有非主鍵欄位必須完整依賴於整個主鍵（Composite Key），不能只依賴於主鍵的一部份。

為什麼重要：

• 防止 重複資料（例如同一商品的價格在多筆明細中重複）。
• 減少 更新異常：只要修改一次即可同步所有相關記錄。

範例 3：仍未 2NF 的訂單明細（含商品價格）

CREATE TABLE order_items_bad (
    order_id   INT,
    product_id VARCHAR(10),
    qty        INT,
    price      DECIMAL(10,2),   -- 商品單價
    PRIMARY KEY (order_id, product_id)
);

問題：price 只依賴於 product_id，而不是整個 (order_id, product_id) 主鍵。若同一商品在不同訂單中價格變動，需要在多筆記錄中同步更新，容易遺漏。

範例 4：符合 2NF 的設計

CREATE TABLE products (
    product_id VARCHAR(10) PRIMARY KEY,
    name       VARCHAR(100),
    price      DECIMAL(10,2)   -- 單價只存一次
);

CREATE TABLE order_items_good (
    order_id   INT,
    product_id VARCHAR(10),
    qty        INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

說明：將商品資訊（包括單價）抽出成 products 表，order_items_good 只保留「數量」與外鍵，所有非主鍵欄位均完整依賴於主鍵，達到 2NF。

---

3️⃣ 第三正規化 (3NF) – 消除傳遞函式相依

定義：在已符合 2NF 的前提下，非主鍵欄位之間不得相互依賴（即不存在 A → B → C 的傳遞相依關係），所有非主鍵欄位必須直接依賴於主鍵。

為什麼重要：

• 進一步降低 資料冗餘，避免因屬性變更而產生多筆不一致的記錄。
• 讓 資料模型更易於擴充，新增屬性不會破壞現有正規化結構。

範例 5：仍未 3NF 的客戶資料

CREATE TABLE customers_bad (
    customer_id   INT PRIMARY KEY,
    name          VARCHAR(50),
    zip_code      VARCHAR(5),
    city          VARCHAR(30),   -- 依 zip_code 推算出城市
    state         VARCHAR(20)    -- 依 zip_code 推算出州/省
);

問題：city 與 state 皆傳遞相依於 zip_code（zip_code → city/state），而 zip_code 本身是主鍵的直接屬性。若同一郵遞區號的城市名稱變更，需要在每筆客戶資料中同步更新，容易出錯。

範例 6：符合 3NF 的客戶與郵遞區號表

CREATE TABLE zip_codes (
    zip_code VARCHAR(5) PRIMARY KEY,
    city     VARCHAR(30),
    state    VARCHAR(20)
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(50),
    zip_code    VARCHAR(5),
    FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
);

說明：將 city、state 抽離成 zip_codes 表，customers 只保留 zip_code 外鍵，所有非主鍵欄位皆直接依賴於主鍵，完成 3NF。

---

常見陷阱與最佳實踐

陷阱	可能的後果	解決方式
把多值欄位直接寫在同一欄（違反 1NF）	查詢變得複雜、資料不一致	使用關聯子表或 JSON（若真的需要）但仍保持可分解性
將屬性放在錯誤的表格（未達 2NF）	更新異常、重複儲存	先列出功能相依圖，確定每個屬性只屬於唯一實體
傳遞相依未被發現（未達 3NF）	大量冗餘資料、維護成本升高	在設計階段畫 ER 圖，檢查每條非主鍵屬性是否直接依賴主鍵
過度正規化	需要大量 JOIN，效能下降	依需求 適度去正規化（Denormalization），如報表或讀取密集的情境
忽略索引	正規化後的 JOIN 變慢	為外鍵與常用查詢欄位建立 適當的索引（如 INDEX (product_id)）

最佳實踐

1. 從需求出發：先寫出功能需求與查詢頻率，再決定正規化的深度。
2. 使用 ER 圖：視覺化實體與關聯，方便檢查相依關係。
3. 保持命名一致：外鍵名稱建議使用 <referenced_table>_id，提升可讀性。
4. 測試資料完整性：利用 FOREIGN KEY、UNIQUE、CHECK 等限制，讓 DBMS 幫你 enforce 正規化規則。
5. 定期檢視：隨著業務變更，重新檢查正規化程度，必要時進行調整。

---

實際應用場景

1️⃣ 電商平台的訂單系統

• 1NF：將一筆訂單的商品資訊拆成 order_items 表，避免以逗號分隔的字串。
• 2NF：商品價格、名稱放在 products 表，避免在每筆 order_items 中重複儲存。
• 3NF：若商品屬於「品牌」與「類別」，再分別建立 brands、categories 表，避免 products 裡同時存 brand_name、category_name 的傳遞相依。

2️⃣ 員工管理系統

• 1NF：員工的電話號碼、地址等欄位保持原子性，若同一員工有多個電話，可建立 employee_phones 表。
• 2NF：部門資訊（部門名稱、主管）抽離成 departments 表，employees 只保留 department_id。
• 3NF：若部門主管本身也是員工，使用 manager_id 外鍵指向 employees.employee_id，避免在 departments 中同時存 manager_name。

3️⃣ 醫院資訊系統

• 1NF：病患的過往診斷、藥物使用以關聯表 patient_diagnoses、patient_medications 表示。
• 2NF：醫師的專科、執照號碼抽出成 doctors 表，診療紀錄只保存 doctor_id。
• 3NF：若專科屬於醫院的科別（如「內科」），再建立 departments 表，使 doctors 不直接存 department_name。

---

總結

正規化是 資料庫設計的基石，從 1NF 的原子性、2NF 的完整相依，到 3NF 的消除傳遞相依，我們逐層剖析、拆解資料，使其結構化、易於維護。透過本文的範例與實務建議，你應該能：

• 判斷一個表格是否符合 1NF、2NF、3NF。
• 以 MySQL 為例，快速將雜亂的設計轉換成正規化的模型。
• 在開發過程中避免常見陷阱，並依需求適度去正規化以取得最佳效能。

掌握正規化的思考方式，未來不論是設計電商、ERP、或是大型資訊系統，都能建立 可靠、可擴充 的資料基礎。祝你在 MySQL 的世界裡，寫出乾淨、健全的資料庫結構！