知識冒險者

資料庫正規化(一~三)

Properties1
tagsdatabase, sql, schema-design

閱讀時間約 16 分鐘

資料庫正規化（一～三）

作者：阿寶｜iT 邦幫忙 2020 鐵人賽系列《後端基礎 PHP+Mysql & Laravel 30 日養成計畫》Day 32

統整

定義與目的

關聯式資料庫正規化的過程，其實就是將一些實體的描述資料，透過一定的程序，將表單簡化，直到一張表單只單純描述一個事實為止。

目的三點：提昇儲存與操作效率、減少資料異常、讓維護更容易。

正規化後的資料庫應具備四個特性：

  • 欄位唯一性：每個欄位只儲存一項資料
  • 主關鍵欄位：每筆資料都有主鍵可區別
  • 功能關聯性：欄位之間的關聯要明確
  • 欄位獨立性：欄位之間不應存在遞移相依

起點：完全沒正規化的表

一張 orders 表塞下所有東西（原文表格的表頭比資料多一欄，以下依語意對回來，date 原文無資料）：

orders

custmor_idcustmor_namegenderitemspricesstore_idstore_nameaddresssex
1阿寶女鉛筆, 橡皮擦20, 501久成久東區大學路女
2豆芽女牛奶, 三明治70, 103全家東區北門路女
3老皮男鉛筆, 牛奶20, 701久成久東區大學路男

三個問題：

  1. items、prices 一個欄位存兩項以上資料，違反原子性（atomicity）
  2. gender 和 sex 意義重複，浪費空間與時間
  3. 沒有 unique 的 primary key，這張表容許存在兩筆內容一模一樣的資料

第一正規化：原子性、去重複欄位、定主鍵

要完成三件事：一個欄位只能有單一值、消除意義上重複的欄位、決定主鍵（必須 unique）。

做法是把每一項商品的消費拆成獨立的列，刪掉多餘欄位（sex 沒了），並以 custmor_id + item_id 組成複合主鍵。

orders（PK = custmor_id + item_id）

custmor_idcustmor_namegenderitem_iditem_namepricecounttotal_amountstore_nameaddress
1阿寶女1鉛筆20120久成久東區大學路
1阿寶女2橡皮擦502100久成久東區大學路
2豆芽女3牛奶703210全家東區北門路
2豆芽女4三明治10440全家東區北門路
3老皮男1鉛筆20120久成久東區大學路
3老皮男3牛奶702140全家東區北門路

（原文這張表同樣多夾了一欄沒有表頭的數字，去掉之後 total_amount = price × count 六列全部成立，這裡照此呈現。）

結果每一列變成一筆「某人買某商品」的紀錄，但消費者性別、商品名稱、商店名稱與地址在每一列重複出現。

作者用兩個情境說明痛點：

  • 老皮在全家訂一百個三明治 → 姓名、性別、商店地址要重複輸入一百次
  • 全家搬到民族路 → 那一百筆要一筆一筆改

第二正規化：消除部分相依

部分相依的意思為跟主鍵只有一部份有關係、另一部份沒有關係的欄位。

在這個例子裡主鍵是 customer_id + item_id，那麼 customer_name 只和 customer_id 相依、item_name 只和 item_id 相依——這些都該被移到另一張表。

作者補了一段很實用的旁註：

部分相依通常發生在使用複合主鍵的情況下。許多框架建表時預設以 id 作為 PK，若該表已完成第一正規化，設定 id 為 PK 則直接滿足第二正規化，因為 PK 為單一鍵（已經是最小單位，自然不會有和 PK「部分相依」的情況）。

拆完變成四張表：

customers(id, name, gender)
stores(id, name, address)
items(id, name, price, store_id)
orders(customer_id, item_id, count, total_amount, date)   -- FK → customers / items

customers

idnamegender
1阿寶女
2豆芽女
3老皮男

stores

idnameaddress
1久成久東區大學路
2全家東區北門路

items

idnamepricestore_id
1鉛筆201
2橡皮擦501
3牛奶702
4三明治102

orders（PK = custmor_id + item_id）

custmor_iditem_idcounttotal_amountdate
1112012/17
12210012/17
23321012/18
2414012/18
3142012/19
33114012/19

這張 orders 是原文數值，有三列的 total_amount 對不上 price × count（三明治 10 元買 1 個卻是 40、鉛筆 20 元買 4 個卻是 20、牛奶 70 元買 1 個卻是 140）。見下方矛盾點。

遞移相依與第三正規化

所謂遞移關係，在這個範例裡就是指：total_amount 是依賴 price 及 count 的資訊，而 item_id 和 count 又和主鍵直接相關，那 total_amount 和主鍵之間的關係就是遞移關係。

兩個定義：

  • 遞移相依：欄位1 和主鍵相關，欄位2 和欄位1 相關，欄位2 和主鍵就是遞移相依
  • 候選鍵：欄位組合讓資料能是唯一的，並且是最小唯一

規則是「非主鍵屬性的欄位都只能和候選鍵相關，非主鍵屬性的欄位彼此間應該要是獨立無關的」。

作者給的白話理由：為了避免 count 改變而 total_amount 沒改到造成資料錯誤，應該把 total_amount 從這張表中移除。

customers、stores、items 三張表原封不動，只有 orders 少一欄：

orders（PK = customer_id + item_id）

customer_iditem_idcountdate
11112/17
12212/17
23312/18
24112/18
31412/19
33112/19

金額改成查詢時算：items.price × orders.count。

三個階段對照

schema這一步解決的問題留下的問題
原始orders(custmor_id, custmor_name, gender, items, prices, store_id, store_name, address, sex)—欄位塞多值、gender/sex 重複、沒有主鍵
1NForders(custmor_id, custmor_name, gender, item_id, item_name, price, count, total_amount, store_name, address)，PK = custmor_id + item_id原子性、去重複欄位、有主鍵姓名／性別／商品名／店名地址每列重複，改一次要改很多列
2NFcustomers(id, name, gender)／stores(id, name, address)／items(id, name, price, store_id)／orders(custmor_id, item_id, count, total_amount, date)消除部分相依，重複資料各自集中到一張表total_amount 與 count／price 之間會不同步
3NF同上，但 orders(customer_id, item_id, count, date)消除遞移相依（拿掉衍生欄位）查金額要 join items；商品改價後查不到當時的成交價

BUT：正規化不是教條

文章最後轉折，給了兩個「不該繼續正規化」的情境：

  1. 高頻查詢要跨表：業務需求就是很高頻率查「某消費者購買的商品名稱及商店地址」，得從 customers 關聯到 items 再關聯到 stores。「這樣第二正規化之後所帶來的好處其實並不比他所帶來的開銷還要大，其實只要做到第一正規化就好了。」
  2. 需要保留歷史值：無論商品價格之後改幾次，需求就是要查得到「購買當下的價格」——照二、三正規化拆完反而不適用這個情境。

結論：資料庫正規化並不是什麼完全不可違背的教條，要進行到什麼程度，應視對資料庫操作的需求和資料量而定，而不是每次建表都只要無腦的全部照著正規化的規範去設計。

留言區的補充

讀者「微中子」留言指出：正規化有五種，這篇只介紹前三種，並附上自己寫的英文文章《Good RDB Design with the Concept of Normal Forms》。（作者未回應內容）

反覆出現的關鍵字

作者一再回到的說法，是這篇真正想講的東西：

  • 「一張表單只單純描述一個事實」——開頭的定義，也是三個 NF 的共同判準；每一步都在回答「這張表混了幾個事實」。
  • 相依（dependency）：部分相依 → 遞移相依。三個 NF 的差別被收斂成同一個問句：這個欄位到底跟誰有關。
  • 重複輸入／一筆一筆修改：說明 1NF 不夠時用的，說明 2NF 時又用一次。作者對「資料異常」的具體想像就是同一個事實被存在很多列，改的時候會漏。
  • 原子性（atomicity）：1NF 的唯一判準。
  • 複合主鍵：部分相依的成因，也是單一 id PK 直接跳過 2NF 的原因。
  • 「並不比他所帶來的開銷還要大」／「應視需求和資料量而定」：結尾反覆強調的節制，佔了整篇的最後五分之一。

矛盾點

文章內部

  1. 表格自己對不齊，而且 2NF 的 orders 算錯三列。原始表與 1NF 表的表頭欄數和資料欄數不一致（各多／少一欄，date 整欄沒有值）。更實質的是 2NF 之後的 orders：(2, 4, 1, 40) 三明治 10 元買 1 個卻是 40、(3, 1, 4, 20) 鉛筆 20 元買 4 個卻是 20、(3, 3, 1, 140) 牛奶 70 元買 1 個卻是 140——六列裡三列的 total_amount ≠ price × count（1NF 那張表把夾帶的雜欄去掉後反而六列全對）。用來論證「count 改了 total 沒跟著改會出錯」的表格，自己就是那個錯誤的實例（推論：這反而意外地證成了作者的主張）。
  2. 2NF 段落的敘述與表格不符。文中說拆完後「orders 就單純的只儲存消費者和購買的商品，以及消費日期三種資料而已」，但同段的 orders 表明明還有 count 和 total_amount。
  3. total_amount 的處置被放在 2NF 段落講，卻是 3NF 的工作。遞移相依的定義、要移除 total_amount 的結論都寫在第二正規化那一節的末尾，第三正規化那節只剩貼表格。
  4. FK 指向的是 id 不是 name。「消費者對應到 customers 的姓名，商品對應到 items 的名稱」與下面 orders(customer_id, item_id) 的實作對不上。
  5. total_amount 算不算「遞移相依」是可疑的（推論）。教科書的 3NF 講的是「非主鍵欄位相依於另一個非主鍵欄位」；total_amount 的來源 price 在 2NF 之後根本不在這張表裡，它其實是衍生欄位（derived column）。兩者的處置剛好一樣（都該拿掉），但理由不同——把衍生欄位掛到 3NF 名下，會讓人以為「只要不是衍生的就沒事」。
  6. 「單一 id PK 直接滿足 2NF」是半個真話（推論）。定義上成立（沒有複合主鍵就沒有部分相依），但它並沒有消除底層的重複：真正的候選鍵可能仍是複合的，加一個代理鍵只是讓 2NF 的檢查通過，該拆的表還是沒拆。照字面讀很容易得到「用 Laravel 預設 id 就不用管 2NF」的錯誤結論。
  7. 「保留購買當下價格」不是正規化的反例（推論）。成交價和商品目前售價是兩個不同的事實，把成交價存進 orders 完全符合「一張表只描述一個事實」。作者把它寫成「遵循二、三正規化反而不適用」，其實是誤把 snapshot 需求當成反正規化。

與 vault 既有筆記

  1. JOIN 貴在每一列都要去另一張表配對：文章說「多層關聯的開銷不比好處大，只做到 1NF 就好」，但沒有給任何成本模型。那篇筆記的判準更精確——join 貴不貴取決於 join 欄位有沒有索引與參與配對的列數，而不是取決於關聯了幾層。（推論）用「幾張表」當反正規化的判準是最常見的誤判來源。
  2. 同一個寫法在互動式列表沒事，在全量掃描必爆：文章結論「視需求和資料量而定」正是同一個主張的資料庫版本——慣例的適用範圍由負載形狀決定。這兩篇可以互鏈。
  3. SQL Basic 已收錄 JOIN、GROUP BY 等關鍵字，但沒有任何 schema 設計層的內容；正規化是這份速查缺的上游。

待確認

  • 文章只講到 3NF，留言指出還有 BCNF / 4NF / 5NF。（推論：實務上 3NF 之後最常遇到的其實是 BCNF，值不值得再補一輪？）
  • 「高頻查詢就退回 1NF」這個處方，跟現在更常見的做法（保持正規化 ＋ 加索引 / 物化視圖 / 讀模型）差在哪？這篇 2020 年的文章沒有提。

抽出的原子筆記

關係圖譜

反向連結