[DEE-100] 正規化

INFO

預設正規化到第三正規化（3NF）。僅在有量測證據顯示特定查詢無法達到延遲目標時才進行反正規化。

背景

正規化是組織關聯式資料庫以減少資料冗餘並保護資料完整性的過程。E.F. Codd 於 1970 年引入了這個概念以及第一正規化（1NF），接著在 1971 年提出 2NF 和 3NF，並於 1974 年與 Raymond Boyce 共同提出 Boyce-Codd 正規化（BCNF）。

沒有正規化，資料表會遭受三種眾所周知的異常：

更新異常 -- 相同事實儲存在多列中。在一列中更改而其他列未更改會造成不一致。
插入異常 -- 無法在不插入無關資料（或使用 NULL 佔位符）的情況下記錄新事實。
刪除異常 -- 刪除一列會意外地銷毀儲存在同一列中的無關事實。

正規化透過分解資料表來系統性地消除這些異常，使每個非鍵欄位都依賴於「鍵、完整的鍵、且僅依賴於鍵」。

原則

你 MUST 在考慮反正規化之前，將新的 schema 至少正規化到 3NF。
你 SHOULD 記錄任何刻意的反正規化，包括它所受益的查詢和證明其合理性的基準測試。
你 MAY 在資料表有重疊的複合候選鍵時，超越 3NF 到 BCNF。
你 MUST NOT 在沒有量測的查詢計畫或負載測試顯示正規化設計無法達到 SLA 的情況下「為了效能」而反正規化。

圖解

以下圖表展示了正規化的進程，每一步消除特定類別的依賴問題。

範例

未正規化資料表（UNF）

一個儲存訂單及重複產品資訊的單一資料表：

order_id	customer_name	customer_email	product_name	product_price	qty
1	Alice	alice@example.com	Widget	9.99	2
1	Alice	alice@example.com	Gadget	24.99	1
2	Bob	bob@example.com	Widget	9.99	5

問題：更新 Alice 的 email 需要修改訂單 1 的每一列（更新異常）。刪除訂單 2 會遺失 Bob 的 email 是 bob@example.com 這個事實（刪除異常）。

正規化到 3NF

sql

-- 客戶：無遞移依賴
CREATE TABLE customers (
    customer_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          TEXT        NOT NULL,
    email         TEXT        NOT NULL UNIQUE
);

-- 產品：獨立實體
CREATE TABLE products (
    product_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          TEXT        NOT NULL,
    price         NUMERIC(10,2) NOT NULL
);

-- 訂單：僅依賴客戶
CREATE TABLE orders (
    order_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id   BIGINT      NOT NULL REFERENCES customers(customer_id),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 訂單項目：依賴訂單 + 產品（完整的複合鍵依賴）
CREATE TABLE order_items (
    order_id      BIGINT      NOT NULL REFERENCES orders(order_id),
    product_id    BIGINT      NOT NULL REFERENCES products(product_id),
    quantity      INT         NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

每個非鍵欄位現在都依賴於鍵、完整的鍵、且僅依賴於鍵：

customers.email 僅依賴 customer_id。
products.price 僅依賴 product_id。
order_items.quantity 依賴完整的複合鍵 (order_id, product_id)。

注意 unit_price 被刻意儲存在 order_items 上（而非從 products 查找），因為銷售時的價格是一個歷史事實，MUST NOT 在當前產品價格更新時改變。

常見錯誤

錯誤	為何有害	修正
跳過正規化，為了簡單而設計「扁平」資料表	隨著資料增長，更新、插入和刪除異常會迅速出現	從 3NF 開始；有證據後再扁平化
過度正規化（例如每個屬性都有獨立資料表）	過多的 join 拖慢讀取並使應用程式碼複雜化	在 3NF 停止，除非有重疊的候選鍵（那就到 BCNF）
過早反正規化「因為 join 很慢」	在量測之前就增加了寫入複雜度和一致性風險	先分析實際查詢；在有索引的鍵上做 join 是快的
在 schema 審查時忽略函數依賴	可能意外滿足 3NF 但遺漏了之後才浮現的遞移依賴	在設計審查時明確列出函數依賴
儲存衍生資料但沒有重新整理策略	反正規化的副本會漸漸不同步	使用物化視圖或觸發器，並記錄重新整理頻率

參考資料

E.F. Codd, "A Relational Model of Data for Large Shared Data Banks" (1970) -- 引入關聯模型和 1NF 的原始論文
Database normalization -- Wikipedia -- 包含歷史背景的正規化形式全面概述
PostgreSQL Documentation: Constraints -- PostgreSQL 如何強制正規化所依賴的約束
Database Normalization -- DigitalOcean -- 附帶範例的實務演練

[DEE-100] 正規化 ​

背景 ​

原則 ​

圖解 ​

範例 ​

未正規化資料表（UNF） ​

正規化到 3NF ​

常見錯誤 ​

相關 DEE ​

參考資料 ​

[DEE-100] 正規化

背景

原則

圖解

範例

未正規化資料表（UNF）

正規化到 3NF

常見錯誤

相關 DEE

參考資料