Lesson 3: 資料庫設計美學-Schema 設計、正規化 (3NF) 與欄位型態的陷阱
在上一篇文章中介紹了常見的RDBMS、NoSQL以及其相關特性。
現在我們要針對最常使用的RDBMS來探討 關於：Schema、正規化、ACID原則…等。
Schema
關聯式資料庫的其中一個概念就是：table與table之間存在著某些關聯。以下會是幾個我在審閱ERD設計或者DB規劃的時候會特別注意到的點：
關於table name的命名
在 Laravel (以及大部分的 RDB 設計習慣) 中，資料表命名應該用「名詞 (複數)」，而不應該出現動詞。 動詞代表「行為」，但資料表存放的不是行為，而是資料。資料表代表的是一個collection of entities。
主副關係，當表 A (副表) 必須依附於表 B (主表) 才能構成完整語義時，副表應包含主表名稱。
正規化的那些事
在講正規化之前，我想先表態：我不支持所有的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，必須有以下條件：
已經符合1NF
所有非主鍵欄位，必須完全相依 Composite Key
總結 2NF 的目的就是專門解決複合主鍵設計時，將與主鍵部分欄位相關的冗餘資訊錯誤地放在同一張表中的問題。
3NF
3NF(Third Normal Form)的定義是：消除傳遞相依性 (Eliminating Transitive Dependency)。
簡單來說一個table要符合3NF，必須有以下條件：
已經符合2NF
資料表中不能存在任何「非主鍵欄位」對「主鍵」的「傳遞相依性」。
隨堂測驗
請問上面的資料表是否違反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 (聚簇索引) 的破壞力。
後果
隨機寫入災難 (針對 v4)： 由於 UUID v4 是無序的，新資料會隨機插入索引樹的中間，導致頻繁的 Page Splitting (頁面分裂)，造成磁碟 I/O 激增且索引碎片化 (Fragmentation)。
儲存膨脹： 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 需用特殊運算子，語法複雜且較慢
業務邏輯上移至應用層，維護成本提高
建議
僅用於非關鍵、無固定結構、低頻查詢的欄位。