# Lesson 3: 資料庫設計美學-Schema 設計、正規化 (3NF) 與欄位型態的陷阱 在上一篇文章中介紹了常見的RDBMS、NoSQL以及其相關特性。 現在我們要針對最常使用的RDBMS來探討 關於：Schema、正規化、ACID原則…等。 ## Schema 關聯式資料庫的其中一個概念就是：table與table之間存在著某些關聯。以下會是幾個我在審閱ERD設計或者DB規劃的時候會特別注意到的點： ### 關於table name的命名 在 Laravel (以及大部分的 RDB 設計習慣) 中，資料表命名應該用「名詞 (複數)」，而不應該出現動詞。 動詞代表「行為」，但資料表存放的不是行為，而是資料。資料表代表的是一個collection of entities。 主副關係，當表 A (副表) 必須依附於表 B (主表) 才能構成完整語義時，副表應包含主表名稱。
💡
副表簡易判斷規則：當這張表不關聯其他資料，僅包含這個主體時，是否為有意義資料，例如：orders以級order_details，orders紀錄訂單主資料 而 order_details紀錄訂單購買商品，一旦order_details不包含order_id，他本身就屬於無意義的內容，那他就應該命名為order_details而不是details
## 正規化的那些事 在講正規化之前，我想先表態：我不支持所有的DB設計都完全的正規化。聽起來很奇怪，但我確實是這樣想的。在特定情況下，適當的去做反正規，其實對效能來說會是一個幫助，例如：我們將高頻查詢的資料整合成放進某搜尋表中，避面了需要一兩個欄位，卻又要多join一張table情況的產生，這對heavy-read的系統來說會是一個幫助 例如： | **場景** | **實施方法** | **提升效果** | | --- | --- | --- | | **高頻查詢** | 將常用的屬性從關聯表中**冗餘複製**到主表。例如：將 `Product` 的 `name` 複製到 `Order_Items`。 | **消除 JOIN 操作：** 讀取訂單細節時，無需再 JOIN `Products` 表，極大加速讀取速度。 | | **報表/彙總** | 建立專門的**彙總表 (Summary/Report Table)**。例如：每日銷售總額、用戶活躍度計數。 | | | 這就是：Materialized View的應用 | 將複雜計算結果**預先儲存**，報表生成不再需要實時執行複雜的聚合查詢。 | | | **歷史數據** | 結合了時間戳或狀態的數據。例如：**訂單快照**。 | 鎖定交易時點的數據，即使原始表（如商品價格表）更新，訂單記錄也不受影響，保持歷史數據的準確性。 | Q：但正規化聽起來很差，為甚麼還要遵循或學習？ A：正規化之所以成為 RDBMS 設計的基石，是因為它解決了比單純查詢速度**更根本、更致命**的問題：**數據的完整性和一致性**。 例如： | **異常類型** | **描述** | **非正規化設計的結果** | | --- | --- | --- | | **更新異常** | 更改一個事實時，必須同時修改多個地方。 | 如果您在 100 筆訂單中都冗餘存儲了某個客戶的地址，但只更新了其中 99 筆，**數據就會不一致**。 | | **刪除異常** | 刪除某筆記錄時，不小心連帶刪除了重要的、不相關的資訊。 | 如果一個員工是公司某部門唯一的員工，當您刪除這位員工的記錄時，**部門的資訊可能也消失了**。 | | **插入異常** | 在缺乏主鍵資訊的情況下，無法新增其他相關資訊。 | 如果您想新增一個還沒有被任何產品引用的「供應商」資訊，在非正規化設計中可能無法做到。 | ### 1NF 1NF(First Normal Form)的定義是：原子性(Atomicity)、無重複欄位、唯一記錄、必須有主鍵。 原則：資料表中的**每個欄位**都必須是不可再分割的原子值。因此在這個最核心的原則下，每一個data column只能儲存一個單一的數據點，不能是一組值、一個列表、一個陣列，或任何可以被拆分成多個獨立資訊的複合值。 如果違反1NF原則，則代表該表需要有多筆資料，或者需要再開一張關聯表出來。 這時候就會有一個疑問： Q：我們有些時候會在DB中儲存JSON或者JSONB的資料，那這樣不就違反原子性嗎？ A：現代 RDBMS 為了實用性，已經巧妙地繞過了 1NF 在「原子性」上的嚴格定義，允許儲存 JSONB 這種複雜結構。 PostgreSQL、MySQL 等現代 RDBMS 之所以引入 `JSON` 或 `JSONB` 類型，是出於對**實用性**和**效能**的考慮： **A. 結構化和可索引性** 重點在於 `JSONB` **不是一個單純的字串**： • `JSONB` 是 **Binary JSON**，RDBMS 能夠理解其內部結構。 • PostgreSQL 允許為 `JSONB` 建立 **GIN 索引**，這意味著可以高效地執行查詢，例如 `WHERE json_column -> 'city' = 'Taipei'`。 **B. 效能與反正規化** 在實務上，使用 `JSONB` 是一種**受控的反正規化**： • 它允許將那些**非關鍵的、變化多端、不需要頻繁 JOIN 的**細節數據（例如用戶偏好設定、訂單的額外屬性）存放在主表中。 • 這**避免了為了一堆次要屬性而建立數十張小表**，減少了 JOIN 的開銷。 **結論：RDBMS 選擇了「實用性 1NF」**，**而不是 強制且完全的1NF** **是 RDBMS 在 NoSQL 趨勢下，為了提供靈活性和性能優化而進行的進化。** 因此，當使用 `JSONB` 時，您是在**有意識地犧牲1NF的嚴格理論定義，來換取應用程式開發的靈活性和查詢的效率**。只要能為 `JSONB` 建立索引，並在應用層面控制數據的完整性，它就是一種可接受且強大的設計模式。 是 RDBMS 在 NoSQL 趨勢下，為了提供靈活性和性能優化而進行的進化。 因此，當使用 JSONB 時，您是在有意識地犧牲1NF的嚴格理論定義，來換取應用程式開發的靈活性和查詢的效率。只要能為 JSONB 建立索引，並在應用層面控制數據的完整性，它就是一種可接受且強大的設計模式。 無重複欄位：不應該有address\_1、address\_2 這種設計出現。 唯一記錄：不應該有兩條一模一樣的資料。 ### 2NF 2NF(Second Normal Form)的定義是：完全功能相依(Full Functional Dependency))。 簡單來說一個table要符合2NF，必須有以下條件： 1. 已經符合1NF 2. 所有非主鍵欄位，必須完全相依 Composite Key
💡
為甚麼要強調 複合主鍵？因為 如果一張資料表只有單一欄位作為主鍵（例如 id），則這個單一欄位就是「整個」主鍵。因此，所有非主鍵欄位天然地相依於這個單一主鍵。
**總結** 2NF 的目的就是專門解決**複合主鍵**設計時，將與主鍵部分欄位相關的冗餘資訊錯誤地放在同一張表中的問題。 ### 3NF 3NF(Third Normal Form)的定義是：消除傳遞相依性 (Eliminating Transitive Dependency)。 簡單來說一個table要符合3NF，必須有以下條件： 1. 已經符合2NF 2. 資料表中不能存在任何「非主鍵欄位」對「主鍵」的「傳遞相依性」。
💡
什麼是「傳遞相依性」？當一個非主鍵欄位 C 不直接相依於主鍵 A，而是透過另一個非主鍵欄位 B 來間接相依於主鍵 A 時，就稱為傳遞相依性。白話文：非主鍵欄位之間不能有決定關係。 如果欄位 B 的值決定了欄位 C 的值，那麼 B 和 C 就不應該在同一張表中。假設：Table有A、B、C三個欄位，B欄位跟C欄位 應該相依於A主鍵
![](https://cdn.hashnode.com/res/hashnode/image/upload/v1765440848777/a3194b6d-62d6-4b2e-9a85-b3101684ca69.png align="center") ### 隨堂測驗 ![](https://cdn.hashnode.com/uploads/covers/693a6e8cf7ff0b7590781231/86019759-2b24-4bc6-b14e-9c580b2d1938.png align="center") 請問上面的資料表是否違反3NF？ A：沒有完反學術界針對3NF的定義，但看起來有點奇怪對吧？這個設計會有點**Bad Smell**，因為他失去了DB Column的參考完整性。無法使用FK、約束...等。 為甚麼會沒有違反3NF呢？因為：source\_type以及source\_ref\_id 這兩個欄位本身並沒有絕對的限制，不會因為source\_type 是 order，source\_ref\_id就一定是1，這兩個欄位 在數值的定義上是 各自依賴於PK。source\_ref\_id可以是1、2、3、987、555，這就證明了它們之間沒有 3NF 所禁止的那種函數相依性。 正規化理論 (A → B) 中，「決定」有非常嚴格的定義：**如果我知道了 A 的值，我就能「唯一確定」B 的值。** 這時候開發者可能會說：但是source\_ref\_id 是 由 source\_type 決定的阿！ 沒錯！但是這是語意上的決定 (Semantic Dependency)」，而不是「數值上的函數相依 (Functional Dependency)，而 正規化（1NF, 2NF, 3NF）只關心欄位內的數值 (Values) 之間的關係，而不關心這些數值背後的業務邏輯或外部參考。 以這個案例為例：這樣的設計沒有數值上的強制性 但卻有邏輯上的強制性。 這是典型的：Polymorphic Relations 設計。 > 如果是Laravel的開發者 > > 如果使用預設的 morphs 情況下，會存取完整的class name在column之中，這會有點空間浪費 + 重構地域，建議可以使用 enforceMorphMap來進行 relation的設定。 * * * > 其實還有一個教科書中極少提到的：BCNF((Boyce-Codd Normal Form)。 # 欄位型態常見陷阱與最佳實務 ## 一、類型使用錯誤帶來的性能問題 ### 1\. 數字型態的浪費（INT、TINYINT、BIGINT） **陷阱** 所有整數欄位都習慣性使用 `INT` (4 bytes)，即使只需存放 0 到 100。 **後果** * 儲存空間浪費。 * 更高的 I/O 成本：例如 BIGINT (8 bytes) 的讀取開銷是 TINYINT (1 byte) 的八倍。 **建議** 依業務需求選最小能容納範圍的型別： * 狀態碼：TINYINT * 計數器：SMALLINT * 高成長自增 ID：BIGINT * * * ### 2\. 字串型別選用錯誤（CHAR vs VARCHAR） **陷阱** * 固定長度需求不明確卻使用 `CHAR`。 * `VARCHAR` 長度設定過大（如 VARCHAR(1000) 但實際只需要 100）。 **後果** * `CHAR` 會填滿設定長度：`CHAR(255)` 即使只有 5 字元也會占用 255 bytes。 * `VARCHAR` 長度過大可能造成： * 記錄額外管理負擔 * 部分資料庫可能將資料移到行外儲存（out-of-row），增加 I/O **建議** * 常變動長度：使用 VARCHAR * 長度固定（如國碼、性別代碼）：使用 CHAR * 設定合理長度，避免過度外溢 * * * ### 3\. 日期時間型別混用（DATETIME vs TIMESTAMP） **陷阱** 忽略兩者儲存方式與時區行為。 **後果** * `TIMESTAMP`：4 bytes，會轉換時區（用於事件時間）。 * `DATETIME`：8 bytes，不轉換時區（用於固定日期，例如生日）。 混用會導致資料不一致或儲存空間浪費。 **建議** 根據需要是否要自動處理時區決定型別。 * * * ## 二、外來鍵與主鍵型別不一致 **陷阱** 主表主鍵是 BIGINT，子表外來鍵卻使用 INT。 **後果** * 無法建立或穩定維護外來鍵。 * JOIN 時造成隱式型別轉換，使索引失效，可能導致全表掃描。 **建議** 對應的 PK 與 FK 型別必須完全一致（含 UNSIGNED）。 * * * ## 三、主鍵選型迷思（Auto-Increment vs UUID） ### 使用 UUID v4 作為主鍵的問題 **陷阱** 以 UUID v4（完全隨機）作為主鍵，為了追求分佈式唯一性，盲目使用標準的 **UUID v4** 作為主鍵，卻忽略了它對 **Clustered Index (聚簇索引)** 的破壞力。 **後果** 1. **隨機寫入災難 (針對 v4)：** 由於 UUID v4 是無序的，新資料會隨機插入索引樹的中間，導致頻繁的 **Page Splitting (頁面分裂)**，造成磁碟 I/O 激增且索引碎片化 (Fragmentation)。 2. **儲存膨脹：** UUID (16 Bytes) 比 BIGINT (8 Bytes) 大一倍。這不僅影響主表，**所有引用此主鍵的外來鍵 (Foreign Keys)** 也會跟著膨脹，導致索引佔用更多記憶體。 **現代化解決方案 ：** 如果業務必須使用 UUID (例如：防止 ID 遍歷攻擊、分散式系統生成 ID)，**請務必選擇 UUID v7 或 ULID**。 * **原理：** 它們將 **時間戳** 整合進 ID 的高位元，確保 ID 具有**單調遞增 (Monotonic Increasing)** 的特性。 * **效果：** 讓 UUID 的寫入效能接近傳統的 Auto-Increment ID，同時保留了全域唯一性。 * * * ## 四、濫用通用型別（EAV、JSON / JSONB） **陷阱** 為了彈性而大量使用 EAV 或 JSONB。 **後果** * 失去關聯式資料庫的資料完整性與索引優勢 * 查詢 JSON 需用特殊運算子，語法複雜且較慢 * 業務邏輯上移至應用層，維護成本提高 **建議** 僅用於非關鍵、無固定結構、低頻查詢的欄位。 # 附件 [Database Design Principles](https://docs.google.com/document/d/1eezMAjwX2hYA374Zc4sbJ95WRfB3aGyR-2_GbQr_RoU/edit?usp=sharing)