Effective SQL 資料模型設計
8 min readMay 25, 2020
Tip 01. 所有資料表都應該有主鍵
- 主鍵是 unique
- 不為 null 或是空
- 不可以被修改
- 盡可能簡單 (單一欄位優於多欄位, 整數優於浮點數或字元, 複合主鍵相對需要較多的運算, 在做 JOIN 操作也較為麻煩…)
Tip 02. 消除重複資料
- 重複儲存的資料會帶來資料不一致的問題, 浪費磁碟資源, 處理時間…
Tip 03. 去除重複群組
- 這邊說的重複群組, 指的是屬性相同的欄位, 像 category 這種欄位…
+----+------------+------------+------------+------------+
| id | product_id | category_1 | category_2 | category_3 |
+----+------------+------------+------------+------------+
| 1 | 7 | 3c | laptop | brand_a |
| 2 | 8 | 3c | monitor | brand_b |
| 3 | 9 | 3c | storage | brand_c |
+----+------------+------------+------------+------------+- 這樣設計, 很容易遇到要增加欄位的情境
- 欄位修改代價極高 (異動 Schema), 列位修改代價低, 可以考慮 1 對多的設計
- 若無法透過正歸化來避免, 也可以考慮透過 UNION 的操作
select id, product_id, category_1 as category
from product
where category_1 not nullunionselect id, product_id, category_2 as category
from product
where category_2 not nullunionselect id, product_id, category_3 as category
from product
where category_3 not null
Tip 04. 每個欄位只存一個屬性
- 這個比較常見的案例大概就是使用者的住址, 國家, 鄉鎮區市, 路街號, etc.
- 考慮的是資料怎麼被 Select, 使用者的住址可以用一個 address 欄位作為一個不可切割的單位, 但考慮要篩選使用者的話, address 的欄位就需要被細切 國家, 州, 城, 鄉, 鎮, 區, 市, 路, 街, 號, etc.
- 雖然還是可以用 LIKE 的操作, 但可能導低效率的資料查詢
Tip 05. 謹慎儲存計算出來的資料
- 若儲存計算出來的資料, 新, 刪, 修, 都需要重新計算.
- 計算欄位應該審慎考慮對效能的影響, 考慮資料量, 跟使用情境.
- 計算欄位也應該慎用 trigger (我自己是滿討厭使用 trigger 的, 在 AP 層級開發時, trigger 就像一個黑箱, 資料被改不容易察覺)
- 若儲存的資料不做 index, 可以考慮採用 View 的方式 （這邊我也不太明白, 以 Postgres 來說 View 本身就是一個 Query 的結果, 或者說 View 本身就是一種適合 ETL 的物件？）
Tip 06. 定義外鍵保護資料完整性
- 外鍵約束的好處, 就是確保資料的完整性, 因為約束的關係, 在 新, 刪, 修, 都會提示你有欄位被 Reference.
- 容易建構 JOIN 操作
- 某些資料庫系統 (Microsoft Access) 外鍵的定義, 可以提高效能 (因資料庫而異, 不一定 XD)
Tip 07. 確保資料表關係的合理
- 案例: employee, customer, vendor 的資料表設計, 若 vendor (廠商) 同時也是 customer (顧客) 的情況下出現, 就會違反資料重複的規則, 若考慮新增 contact 的資料表看似可以解決資料重複的問題, 但這樣的設計卻會帶來新的問題, 在 contact 裡面反而不容易辨識同時是 customer 與 vendor 的狀況。
contact (1) -> (1) customer [1 對 1 關係]contact (1) -> (1) vendor [1 對 1 關係]
- contact 的設計同時有另一種缺點, 因為 contact 對應了 customer 與 vendor, 故在做資料新增時, 變成需要額外驗證 customer 與 vendor 的相關欄位。
- 因此並非重複的資料是不好的, 資料庫雖然要付出一些代價, 但在維護上反而比較容易。
- Entity-attribute-value (EAV)模型的概念, 是一種資料攤平 (Flattening) 的做法, 這種模型的好處是能夠應對不確定屬性數量的 entity, 簡單來說就是一種 1 對多關係的設計應用, 參考 這裡
// product entity
class Product { int id; String name; List<ProductAttribute> attributes;
}
// product attributes entity
class ProductAttribute {
int productId; String attributeName; String value;}
- 資料結構化：資料結構, 已經可以完整展現 entity 與 entity 的關係
- 半結構化：JSON 或 CSV 或其他形式的資料, 具有一定的結構, 但資料欄位會因資料本身要描述的情境, 有增加或減少欄位的狀況
Tip 08. 3NF 不夠時, 更多的正規化
- 正規化的目的用我的方式來理解就是讓資料更容易被操作, 更少出錯, 更容易被擴充與維護, 先來複習一下 1NF, 2NF, 3NF, 詳細的 參考筆記與範例
-- 1NF
1. 每個 table cell 都應該只有 1 個 value
2. 每一筆 record 都應該是 unique
3. 所有欄位皆依賴於 PK
4. E.g. (學號) -> {姓名, 性別, 級別, 課程, 教師編號, 教師姓名, 分數}-- 2NF
1. 滿足 1NF 的規則
2. 非 Key 欄位, 必須完全相依 (Full Dependency) 於 PK
3. E.g.
(學號) -> {姓名, 性別, 級別}
(學號) -> {課程編號, 成績}
(課程編號) -> {課程, 教師編號, 教師姓名}-- 3NF
1. 滿足 2NF
2. 非 Key 欄位, 與 PK 位皆無 遞移相依 (Transitive Dependency)
3. 延續上面的例子, 課程編號會對應一個教師編號, 但教師姓名對應教師編號, 所以間接依賴課程編號
4. E.g.
(學號) -> {姓名, 性別, 級別}
(學號) -> {課程編號, 成績}
(課程編號) -> {課程, 教師編號}
(教師編號) -> {教師姓名}-- BCNF
1. 滿足 3NF
2. 用來審查複合主鍵的表, 延續 3NF 的規則, Key 欄位不可依賴於非 Key 欄位
3. 學號跟課程編號, 是查詢成績的 複合主鍵, 成績改變, 不會影響到課程編號與學號
4. 因此符合 BCNF
- 基本上有好好把握，PK, Unique 的特徵，設計出來的資料結構理論上不會太差才是。
- 在 3NF 之後卻違反更高正規劃 (BCNF, 4NF) 的徵兆是資料表與一個以上的其他資料表關聯，特別是參與多對多關係，或是複合主鍵。透過無損分解 (Lossless Decomposition), 來分析違反高階正規劃。
- Lossless Decomposition：指的是假設一個大表，可以拆成多張小表，理論上小表應該可以透過 JOIN 的方式還原大表。
Tip 09. 資訊倉儲（Data Warehouse）使用反正規化
- Data warehouse：WIKI 裡面定義指的是資料有通過 ETL 的操作，便於與 BI 工具整合的資料儲存系統。比較可以想像的情境是，ERP 系統的資料，可能還會整合 Facebook, Google 跟一些第三方的資料，依據企業需求整合為不同面向的資料儲存系統。
- 由於 Data warehouse 的特性，要做 JOIN 反而是沒有效率的，所以採用反正規劃的方式做儲存，舉例來說：如果想看廣告投放跟訂單的關係，若是把廣告報表 JOIN 訂單，反而不利於快速查詢與整合 BI 工具。
- Fact table：是 Data warehouse 設計的基礎，設計上有 Star 跟 Snowflake 兩種形式，詳細的例子可以參考 開發一個 Cube～多維度資料模型的設計開發步驟
- Fact table 更明確的解釋，可以讀這篇https://www.kimballgroup.com/2008/11/fact-tables/，Fact table 可說是設計用來洞察 insight 企業價值的核心。
- Fact table 書中引用 Ralph Kimball 分為三類，Transaction fact tables ，Periodic snapshot fact tables，Accumulating snapshot fact tables。https://www.kimballgroup.com/2014/06/design-tip-167-complementary-fact-table-types/
