資料庫模型要支援所有業務情境，並持續提供整個世界的一致視圖。為此，多年來累積出一套設計規則，其主要目標是讓綱要管理的所有資料整體一致。

資料庫正規化（database normalization）是組織關聯式資料庫的欄位（屬性）與資料表（關聯）以降低資料冗餘、提升資料完整性的過程，也是簡化設計以達到最佳結構的過程。由埃德加・科德（Edgar F. Codd）首先提出，是關聯模型不可分割的一部分。

資料結構與演算法#

寫過那麼多 SQL 之後，應該不意外：SQL 是宣告式的——我們不寫取回資料的演算法，而是表達想要什麼結果集。

• PostgreSQL 把宣告式查詢轉成執行計畫，運用經典演算法：巢狀迴圈（nested loop）、合併連接（merge join）、雜湊連接（hash join），記憶體內的 quicksort 或資料放不進記憶體時落盤的 tape sort；規劃器也能把單一查詢拆給多個平行工作者。
• 自己實作演算法時我們都知道：最要緊的是選對資料結構。如羅布・派克（Rob Pike）在 Notes on Programming in C 所說：

規則五：資料主導一切。選對資料結構並組織好，演算法幾乎總是不言自明。程式設計的核心是資料結構，不是演算法。

《Basics of the Unix Philosophy》列出的 Unix 設計原則，幾乎可以原封不動套用到資料庫建模。其中最直接相關的包括：模組化（簡單零件、乾淨介面）、清晰（清晰勝過小聰明）、分離（政策與機制分離）、簡單、表述（把知識摺進資料，讓程式邏輯又笨又穩健）、最少驚訝等。

• 正規形式（normal forms）提供了落實這些規則的實務手段；SQL 的 join 操作則是連接資料結構的乾淨介面。
• 稍後會看到：資料表較少的模型並不等於更好或更簡單的模型——「分離規則」也許是清單中最重要的一條。「表述規則」在建模上則體現為選用具備進階行為與處理函式的正確資料型別。

這些規則與正規形式層級可以總結成一句話：先表達你的意圖。任何人讀你的資料庫綱要，都應該立刻理解你的業務模型。

正規形式#

正規化有多個層級（dbnormalization.com 提供實用指南），定義如下：

• 第一正規形式（1NF）：資料表無重複列；每個儲存格皆單一值（無重複群組或陣列）；同一欄的項目同類。
• 第二正規形式（2NF）：符合 1NF，且所有非鍵屬性依賴於整個鍵——即沒有只依賴複合鍵一部分的「部分依賴」。
• 第三正規形式（3NF）：符合 2NF，且沒有遞移依賴（transitive dependency）。
• Boyce-Codd 正規形式（BCNF）：符合 3NF，且每個決定因子（determinant）都是候選鍵。
• 第四正規形式（4NF）：符合 BCNF，且沒有多值依賴。
• 第五正規形式（5NF / PJNF）：符合 4NF，且資料表的每個 join 依賴都是候選鍵的邏輯結果。
• 域鍵正規形式（DKNF）：資料表上每個約束都是鍵與域（domain）定義的邏輯結果。

這一切要說的是：若想用關聯模型與 SQL 處理資料，最好別把資訊弄成一團亂，保持邏輯結構。實務上模型通常做到 BCNF 或 4NF；追到 DKNF 只見於特定場合。

資料庫異常#

未充分正規化的模型可能產生資料庫異常（database anomalies）。在修改（update、insert、delete）關聯時可能出現：

• 更新異常（update anomaly）：同一資訊出現在多列，更新可能造成邏輯不一致。例如「員工技能」表每列都含員工 ID、地址與技能——某員工搬家要更新多列，若只更新到一部分，資料表對「這位員工住哪」會給出互相矛盾的答案。
• 插入異常（insertion anomaly）：某些事實根本無法記錄。例如「教師與課程」表每列含教師 ID、姓名、聘用日與課程代碼——新聘但尚未排課的教師就記不進去，除非把課程代碼設為 null。
• 刪除異常（deletion anomaly）：刪除某些事實的資料時被迫連帶刪除完全不同的事實。同上例，教師暫時沒有任何課時，刪掉最後一列等於把教師本人也刪了。

實作正規形式的模型能避免上述異常，這正是建議做到 BCNF 或 4NF 的原因。不過正規化過程有時允許取捨，如下面的例子。

地址欄位建模#

地址欄位是正規化的實用案例：規則全守會得到非常複雜的綱要。答案取決於應用領域——接電信網路、送貨、或只是開發票，需求完全不同。

• 開發票：一個 text 欄位存使用者輸入的任何東西就夠了，反正只用來印在 PDF 發票上、用電子郵件寄出。
• 送貨業務：必須確認地址實際存在、可到達，還可能要最佳化配送路線。此時地址欄位長得完全不同：
  • 需要（可能帶地理定位的）城市參照清單——同名城市可能出現在多個地區（Portland 顯然是個很常見的名字）。
  • 因此需要各國的地區／行政區參照表（美國是州、德國是 Länder），才能無歧義地指稱一個城市。
  • 街道名稱在同語言地區大量重複，需要街名參照表，再加上「城市 × 街名」的關聯表。
  • 門牌號碼依城市而異，屬於城市與街道關聯的資訊；每個門牌可能還要精確地理定位。
  • 若是到府服務（組裝家具、接電、接網路），還需要每個門牌的棟別／戶別資訊。
  • 使用者可能想用郵遞區號指稱地點——但郵遞區號可能涵蓋一個市內區域，也可能涵蓋多個小城鎮。

一個仍算簡單、可支援送貨的模型至少要五張表（虛擬 SQL，僅示意不可執行）：

create table country(code, name);
create table region(country, name);
create table city(country, region, name, zipcode);
create table street(name);
create table city_street_numbers
        (country, region, city, street, number, location);

選了精細模型，就得為營運範圍內的國家、地區、城市實作維護流程：世界上的邊界會變、郵遞區號隨人口調整、街道會改名、新建築會出現，甚至門牌會有「2 b」「4 ter」這種值——連門牌號碼都不是整數欄位。

好的資料模型是讓應用程式容易處理它需要的資訊，並確保資訊全域一致。地址練習到此已觸及它能說明的極限。

主鍵#

主鍵（primary key）是實現第一、第二正規形式的資料庫約束。1NF 第一條規則是「資料表無重複列」，而主鍵確保兩件事：

• 主鍵約束涵蓋的屬性不允許 null。
• 主鍵涵蓋的屬性在資料表內容中唯一。

兩個保證缺一不可：SQL 比較 null 是複雜問題（三值邏輯），與其爭論無重複規則對 null = null（結果是 null）或 null is not null（結果是 false）如何適用，主鍵約束乾脆完全禁止 null。

代理鍵#

主鍵存在的理由是避免資料集出現重複項。一旦主鍵定義在自動產生的欄位上——這欄位其實不算資料集的一部分——就等於敞開違反 1NF 的大門。

前一章的草稿模型：

create table sandbox.article
 (
    id        bigserial primary key,
    category  integer references sandbox.category(id),
    pubdate   timestamptz,
    title     text not null,
    content   text
 );

這個模型連 1NF 都不符合：

insert into sandbox.article (category, pubdate, title)
     values (2, now(), 'Hot from the Press'),
            (2, now(), 'Hot from the Press')
  returning *;

PostgreSQL 樂意插入兩筆分類、標題、發佈時間完全相同的「重複」文章——只有系統代生的 id 不同。對出版系統的業務規則來說，這恐怕不可接受。

這種人工產生的鍵稱為代理鍵（surrogate key），因為它替代了自然鍵（natural key）。只有自然鍵才能真正防止資料集中的重複項。

用自然鍵修正綱要：

create table sandbox.article
 (
    category   integer references sandbox.category(id),
    pubdate    timestamptz,
    title      text not null,
    content    text,

    primary key(category, title)
 );

如此同一標題可出現在不同分類，但整個系統史上同分類只能發佈一次。若考慮歷史總會重演，也可允許同標題在不同時間再發佈：把主鍵改為 primary key(category, pubdate, title)。

但這樣一來，參照 article 的 comment 表也得跟著扛起整組鍵：

create table sandboxpk.comment
 (
    a_category integer      not null,
    a_pubdate  timestamptz  not null,
    a_title    text         not null,
    pubdate    timestamptz,
    content    text,

    primary key(a_category, a_pubdate, a_title, pubdate, content),

    foreign key(a_category, a_pubdate, a_title)
     references sandboxpk.article(category, pubdate, title)
 );

每筆留言都得攜帶足以唯一指認一篇文章的完整資訊，表變得相當臃腫。

折衷方案：代理鍵與自然鍵並存——保留好引用的代理鍵，同時用 unique + not null 維持 1NF 保證：

create table sandboxpk.article
 (
    id         bigserial primary key,
    category   integer      not null references sandbox.category(id),
    pubdate    timestamptz  not null,
    title      text         not null,
    content    text,

    unique(category, pubdate, title)
 );

not null 加 unique 提供與主鍵同等級的保證：其他表引用起來容易，資料集也有堅實的 1NF 保障。

外鍵約束#

好的主鍵實現 1NF；更高的正規形式要求資訊只在單一處管理（單一事實來源），資料因此拆進多張表，這時就需要其他約束。

• 為確保拆散到不同表的資訊仍然合理，我們需要引用資訊並確保引用持續有效——這就是外鍵（foreign key）約束。
• 外鍵必須引用目標表中已知唯一的一組鍵，因此 PostgreSQL 強制目標表上要有 unique 或 primary key 約束（一律以唯一索引實作）。

PostgreSQL 不會在外鍵的來源端自動建索引；需要的話必須自己建。

Not Null 約束#

not null 約束禁止屬性留白；而屬性的資料型別本身強制值必須合理，所以資料型別也可視為一種約束。

Check 約束與 Domain#

當資料型別允許的值多於應用或業務模型允許的範圍，SQL 可用 check 約束或 domain 定義加以限制（domain 就是把 check 約束綁在資料型別定義上）。PostgreSQL 文件的例子：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

check 約束也可以一次引用同一張表的多個欄位：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

定義新的資料域（CREATE DOMAIN）並當成資料型別使用：

CREATE DOMAIN us_postal_code AS TEXT
 CHECK
 (
    VALUE ~ '^\d{5}$'
     OR
    VALUE ~ '^\d{5}-\d{4}$'
 );

CREATE TABLE us_snail_addy (
   address_id SERIAL PRIMARY KEY,
   street1 TEXT NOT NULL,
   street2 TEXT,
   street3 TEXT,
   city TEXT NOT NULL,
   postal us_postal_code NOT NULL
);

排除約束#

如同前面 Range 一節所見，PostgreSQL 還能定義排除約束（exclusion constraint）：它像廣義的唯一約束，可自選運算子。例如匯率在一段期間內有效，且同一貨幣不允許有效期間重疊：

create table rates
 (
   currency text,
   validity daterange,
   rate     numeric,

  exclude using gist (currency with =,
                      validity with &&)
 );