Terry Yao's Blog

[database/09] 資料庫設計模式：正規化、反正規化、分表的取捨

閱讀時間約 16 分鐘

結論先講

正規化到 3NF 就停，除非你有非常明確的理由往上走。反正規化不是罪，但要有意識地做，不要「因為懶得 JOIN」就反正規化。 Soft delete 看起來安全但坑很多。UUID 不一定比 auto-increment 好。命名用 snake_case、表名用複數。

資料庫設計沒有標準答案，但有很多標準的「錯誤答案」。這篇的目標是讓你在做決定時知道每個選擇的代價。

正規化：到底要到第幾階？

正規化（Normalization）的目的是 消除資料冗餘和更新異常。但過度正規化會導致查詢需要大量 JOIN，效能反而變差。

1NF：每個欄位只存一個值

-- 違反 1NF：tags 欄位存了多個值
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT  -- "javascript,react,frontend" ← 不行
);
 
-- 符合 1NF：拆成獨立的表
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT
);
 
CREATE TABLE article_tags (
    article_id INT REFERENCES articles(id),
    tag TEXT,
    PRIMARY KEY (article_id, tag)
);

現實妥協：如果 tags 只是用來顯示、不需要查詢或篩選，PostgreSQL 的 TEXT[] 陣列或 JSONB 其實也行。

2NF：非鍵欄位完全依賴主鍵

-- 違反 2NF：student_name 只依賴 student_id，不依賴 course_id
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    student_name TEXT,  -- ← 只跟 student_id 有關
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id)
);
 
-- 符合 2NF：student_name 搬回 students 表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT
);
 
CREATE TABLE enrollments (
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    grade CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

3NF：消除傳遞依賴

-- 違反 3NF：city 依賴 zip_code，zip_code 依賴 id（傳遞依賴）
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    zip_code TEXT,
    city TEXT  -- ← city 由 zip_code 決定，不是由 id 直接決定
);
 
-- 符合 3NF
CREATE TABLE zip_codes (
    code TEXT PRIMARY KEY,
    city TEXT
);
 
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    zip_code TEXT REFERENCES zip_codes(code)
);

BCNF：連候選鍵都要處理

BCNF（Boyce-Codd Normal Form）處理的是候選鍵之間的依賴關係。99% 的商業應用不需要走到這裡。

結論：停在 3NF

正規化層級實務價值建議
1NF必須做基本衛生
2NF必須做複合主鍵時特別注意
3NF大部分情況做消除冗餘的甜蜜點
BCNF很少需要除非你在設計學術系統
4NF/5NF幾乎不需要除非你在寫教科書

反正規化：有意識地加回冗餘

正規化完之後，你可能發現某些查詢要 JOIN 五六張表。這時候就需要反正規化（denormalization）——但要有意識地做，並記錄為什麼。

模式一：冗餘欄位

-- 正規化版本：要查訂單金額需要 JOIN order_items 並 SUM
SELECT o.id, SUM(oi.price * oi.quantity) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;
 
-- 反正規化版本：在 orders 表加 total_amount 欄位
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2);
 
-- 建立訂單時計算並存入
-- 代價：每次修改 order_items 都要更新 total_amount

模式二：Materialized View

-- 建立 Materialized View（定期重新整理）
CREATE MATERIALIZED VIEW product_stats AS
SELECT
    p.id,
    p.name,
    COUNT(r.id) AS review_count,
    AVG(r.rating) AS avg_rating,
    SUM(oi.quantity) AS total_sold
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name;
 
-- 定期重新整理（例如每小時）
REFRESH MATERIALIZED VIEW CONCURRENTLY product_stats;
 
-- 查詢超快，因為已經算好了
SELECT * FROM product_stats WHERE avg_rating > 4.0 ORDER BY total_sold DESC;

優點：不改原始表結構、查詢快、CONCURRENTLY 不鎖表。 缺點：資料有延遲（取決於重新整理頻率）、佔額外儲存空間。

模式三：Summary Table

-- 每日銷售摘要表
CREATE TABLE daily_sales (
    date DATE,
    product_id INT REFERENCES products(id),
    total_quantity INT,
    total_revenue DECIMAL(10,2),
    PRIMARY KEY (date, product_id)
);
 
-- 每天凌晨由排程計算
INSERT INTO daily_sales (date, product_id, total_quantity, total_revenue)
SELECT
    DATE(created_at),
    product_id,
    SUM(quantity),
    SUM(quantity * price)
FROM order_items
WHERE DATE(created_at) = CURRENT_DATE - INTERVAL '1 day'
GROUP BY DATE(created_at), product_id
ON CONFLICT (date, product_id) DO UPDATE SET
    total_quantity = EXCLUDED.total_quantity,
    total_revenue = EXCLUDED.total_revenue;

Soft Delete vs Hard Delete

面向Soft DeleteHard Delete
做法UPDATE SET deleted_at = now()DELETE FROM
可還原是否（除非有備份）
符合法規某些法規要求保留紀錄GDPR 可能要求真刪
查詢效能每個查詢都要加 WHERE deleted_at IS NULL乾淨，不影響
外鍵複雜（被刪的資料仍在，外鍵不報錯）乾淨（CASCADE 或 RESTRICT）
儲存持續累積釋放空間
索引效能索引包含已刪資料，效率降低索引乾淨

Soft Delete 實作

-- 基本 soft delete
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;
 
-- 用 partial index 優化查詢效能
CREATE INDEX idx_users_active ON users (id) WHERE deleted_at IS NULL;
 
-- 建立 view 方便查詢
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;
 
-- UNIQUE 約束的坑：email 要允許已刪除用戶重新註冊
-- 錯誤：UNIQUE (email) → 已刪除的 email 也佔住
-- 正確：
CREATE UNIQUE INDEX idx_users_email_active
ON users (email) WHERE deleted_at IS NULL;

我的建議

  • 需要審計追蹤的表（訂單、付款、合約）：soft delete
  • 使用者可能要還原的（文章草稿、設定）：soft delete
  • 暫存資料（session、cache、temp）：hard delete
  • GDPR 要求可刪除的（個資）：hard delete（或 anonymize）

UUID vs Auto-increment

這是另一個永遠吵不完的問題。

面向UUID (v4)Auto-incrementULID / UUID v7
全域唯一是否（表內唯一）是
可猜測否是（/users/1, /users/2…）部分（時間可推）
索引效能差（隨機分布）好（連續寫入）好（時間排序）
儲存大小16 bytes4-8 bytes16 bytes
分散式不需協調需要集中分配不需協調
可讀性差好中

UUID v4 的效能問題

UUID v4 是完全隨機的，這對 B-tree 索引是災難：

Auto-increment: 1, 2, 3, 4, 5 → 總是插在索引尾端，cache 友好
UUID v4: a3f2..., 17b8..., e91c..., 3d4a... → 隨機位置插入，大量 page split

當表超過幾百萬筆時，UUID v4 的 INSERT 效能可以比 auto-increment 慢 2-5 倍。

2026 年的建議：UUID v7 或 ULID

-- PostgreSQL 用 uuid v7（需要 pg_uuidv7 extension 或應用層生成）
CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
 
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    -- ...
);

UUID v7 和 ULID 都是時間排序的 UUID——前面的位元是時間戳，後面是隨機數。這樣既有全域唯一性，又保留了 B-tree 索引友好的連續特性。

簡單規則：

  • 單體應用、不暴露 ID → auto-increment
  • 分散式系統、ID 在 URL 中 → UUID v7 / ULID
  • 避免用 UUID v4 當主鍵（效能問題）

多型關聯（Polymorphic Associations）

「一則留言可以屬於文章，也可以屬於商品」——這種需求怎麼設計？

方案一：STI（Single Table Inheritance）

-- 所有類型的留言放同一張表
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    body TEXT,
    commentable_type TEXT,  -- 'article' 或 'product'
    commentable_id INT,     -- 對應的文章或商品 ID
    created_at TIMESTAMPTZ
);
 
-- 查詢：找某篇文章的所有留言
SELECT * FROM comments
WHERE commentable_type = 'article' AND commentable_id = 42;

優點：簡單、Rails/Django 預設模式。 缺點：沒有外鍵約束（commentable_id 指向哪張表取決於 commentable_type，資料庫層沒辦法驗證）。

方案二：MTI（Multiple Table Inheritance）

-- 每個類型一張留言表
CREATE TABLE article_comments (
    id SERIAL PRIMARY KEY,
    article_id INT REFERENCES articles(id),
    body TEXT,
    created_at TIMESTAMPTZ
);
 
CREATE TABLE product_comments (
    id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(id),
    body TEXT,
    created_at TIMESTAMPTZ
);

優點：有外鍵約束、索引乾淨。 缺點：新增一個類型就要新增一張表；「查詢所有留言」需要 UNION。

方案三：中間表

-- 用中間表來建立多型關聯
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    body TEXT,
    created_at TIMESTAMPTZ
);
 
CREATE TABLE article_comments (
    article_id INT REFERENCES articles(id),
    comment_id INT REFERENCES comments(id) UNIQUE,
    PRIMARY KEY (article_id, comment_id)
);
 
CREATE TABLE product_comments (
    product_id INT REFERENCES products(id),
    comment_id INT REFERENCES comments(id) UNIQUE,
    PRIMARY KEY (product_id, comment_id)
);

優點：有外鍵約束、comment 表乾淨。 缺點：多一層 JOIN。

建議：類型少（< 5 個）用 MTI，類型多或經常變動用 STI。中間表方案在需要嚴格外鍵約束時才用。

審計追蹤模式

-- 基本款：created_at + updated_at
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status TEXT,
    total DECIMAL(10,2),
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now(),
    created_by INT REFERENCES users(id),
    updated_by INT REFERENCES users(id)
);
 
-- 自動更新 updated_at（PostgreSQL trigger）
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION update_timestamp();

完整審計表

-- 記錄每次變更的獨立審計表
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id INT NOT NULL,
    action TEXT NOT NULL,  -- INSERT / UPDATE / DELETE
    old_data JSONB,
    new_data JSONB,
    changed_by INT REFERENCES users(id),
    changed_at TIMESTAMPTZ DEFAULT now()
);
 
-- 用 trigger 自動寫入審計紀錄
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
    VALUES (
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END,
        current_setting('app.current_user_id', true)::INT
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;
 
-- 對需要審計的表啟用
CREATE TRIGGER orders_audit
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger();

命名規範

沒有絕對的對錯，但團隊一致最重要。

面向建議範例
表名snake_case + 複數users, order_items
欄位名snake_casefirst_name, created_at
主鍵id不要用 user_id 當主鍵
外鍵{表名單數}_iduser_id, order_id
布林is_ 或 has_ 前綴is_active, has_verified
時間_at 後綴created_at, deleted_at
索引idx_{表}_{欄位}idx_users_email
唯一約束uniq_{表}_{欄位}uniq_users_email

反模式：不要做這些事

1. EAV（Entity-Attribute-Value）濫用

-- EAV：把所有屬性存成 key-value
CREATE TABLE attributes (
    entity_id INT,
    attribute_name TEXT,
    attribute_value TEXT,  -- 什麼型別都塞成字串
    PRIMARY KEY (entity_id, attribute_name)
);
 
-- 查詢地獄
SELECT
    e.id,
    MAX(CASE WHEN a.attribute_name = 'name' THEN a.attribute_value END) AS name,
    MAX(CASE WHEN a.attribute_name = 'email' THEN a.attribute_value END) AS email
FROM entities e
JOIN attributes a ON a.entity_id = e.id
GROUP BY e.id;

用 JSONB 取代 EAV。 除非你在做需要使用者自定義欄位的 SaaS（像 Salesforce），否則 EAV 是地獄。

2. God Table（萬能表）

-- 什麼都塞在同一張表
CREATE TABLE everything (
    id SERIAL PRIMARY KEY,
    type TEXT,             -- user / product / order / comment
    name TEXT,
    email TEXT,            -- 只有 user 用
    price DECIMAL,         -- 只有 product 用
    status TEXT,           -- user 的和 order 的意思不同
    parent_id INT,         -- 有時是 user_id，有時是 order_id
    data JSONB             -- 放不下的都丟這
);

拜託不要。

3. 隱式關聯

-- 沒有外鍵約束，靠「大家知道」來維護關聯
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,  -- ← 沒有 REFERENCES，刪了 user 這裡不會報錯
    product_id INT -- ← 同上
);

永遠加外鍵約束，除非有非常明確的效能理由（大量批次寫入時可以暫時停用）。

常見問題

什麼時候該反正規化？

當你的查詢效能開始出問題，而且瓶頸確實在 JOIN 上的時候。不要「預防性反正規化」——先正規化，用 EXPLAIN ANALYZE 確認效能問題，再有針對性地反正規化。加冗餘欄位時，在程式碼和 schema 上都要留下註解說明為什麼。

Soft delete 的 deleted_at 要不要建索引？

要，但用 partial index。CREATE INDEX idx_active ON users (id) WHERE deleted_at IS NULL; 這樣索引只包含「活的」資料，又小又快。如果你大部分查詢都是查活資料（通常是），這個索引的 hit rate 會非常高。

UUID 真的那麼慢嗎？

UUID v4 在大表（> 500 萬筆）上確實比 auto-increment 慢，主要是 B-tree page split 造成的。但 UUID v7 / ULID 基本上沒有這個問題，因為它們是時間排序的。如果你的表不超過百萬筆，用 UUID v4 其實也感覺不到差異。

表名到底用單數還是複數？

都可以，但要一致。我個人偏好複數（users, orders），因為表代表的是一個集合。Rails/Django 也是用複數。但 Laravel 用複數、Java 生態偏好單數——不要在同一個專案裡混用就好。

什麼時候該用 JSONB 取代正規化？

當欄位結構不固定、且不需要經常查詢或 JOIN 的時候。例如：使用者偏好設定、商品規格（每種商品的規格項目不同）、API 回應快取。如果你發現自己經常需要 WHERE specs->>'cpu' = 'M4 Pro' 這種查詢，考慮把那個欄位獨立出來。

本系列文章

  1. 資料庫全景：SQL vs NoSQL vs NewSQL
  2. MySQL：為什麼越來越多人覺得它不夠好？
  3. PostgreSQL：為什麼越來越多人轉
  4. NoSQL 什麼時候該用
  5. 資料庫設計：正規化與索引策略
  6. 向量資料庫：RAG 時代的 Embedding 儲存方案
  7. 時序資料庫：監控資料用一般 DB 存，你會後悔
  8. MongoDB vs PostgreSQL：什麼時候該用 NoSQL？
  9. 資料庫設計模式：正規化、反正規化、分表的取捨（本篇）
  10. 擴展策略：讀寫分離與分片
  11. Docker 環境的資料庫管理
  12. 資料庫安全：從連線加密到欄位加密

關係圖譜

反向連結