Skip to content

Data Science › Databases › 資料庫設計原則

資料庫設計原則 (Database Design Principles) ​

設計資料庫的第一個問題不是用哪個 DBMS,而是「資料要拿來做什麼」:支援日常寫入(交易)還是支援分析,決定了 schema 的形狀、正規化程度、要不要 view、權限怎麼分。

OLTP vs OLAP ​

面向OLTP(交易處理)OLAP(分析處理)
目的支援日常作業:下單、登錄檢體、更新狀態報表與分析:趨勢、彙總、跨主題比較
查詢簡單、頻繁、讀寫少量列複雜、聚合大量列、以讀為主
資料最新狀態整合後的歷史資料
設計以應用程式為中心、正規化以主題為中心、dimensional model(星狀)
重視寫入一致性、交易、低延遲掃描與聚合效率
典型系統PostgreSQL、MySQL、SQL ServerSnowflake、BigQuery、Redshift、DuckDB

例:LIMS / EHR 是 OLTP(每次檢驗結果即時寫入);研究用的資料倉儲把多個來源整合成歷史資料,是 OLAP。直接在 OLTP 系統上跑大量分析查詢,會拖慢日常作業;常見做法是定期 ETL 到分析用的資料庫(見 Processing and Pipelines)。

設計的層次 ​

層次回答產出
概念模型 (conceptual)有哪些實體、彼此怎麼關聯?ER diagram:病人、檢體、定序批次、檢驗
邏輯模型 (logical)有哪些表、欄位、key、關係?正規化的關聯模型或星狀模型
實體模型 (physical)用什麼型別、index、分區、儲存?CREATE TABLE、CREATE INDEX

Schema 與欄位型別 ​

schema 是資料庫的藍圖:表、欄位、關係、index、view、constraint。關聯式資料庫是 schema-on-write:寫入前就要符合結構;資料湖與 NoSQL 偏 schema-on-read:讀取時才解析結構。

欄位型別定義了欄位的 domain(可以放什麼值、能做哪些運算):

sql
CREATE TABLE samples (
  sample_id     TEXT PRIMARY KEY,          -- "S001" 保留前導 0,不用 INTEGER
  patient_id    TEXT NOT NULL REFERENCES patients (patient_id),
  collected_on  DATE NOT NULL,             -- 不存成 '3/1/2024' 字串
  rin           NUMERIC(3, 1) CHECK (rin BETWEEN 0 AND 10),
  is_tumor      BOOLEAN NOT NULL
);
Constraint防止
PRIMARY KEY重複與缺 ID
NOT NULL必填欄位留空
UNIQUE重複值(如 barcode)
CHECK不合理的值(負的數量、RIN > 10)
FOREIGN KEY指向不存在的父資料(孤兒列)

在資料庫層設 constraint,錯誤資料進不來;只在分析時檢查,錯誤已經擴散到下游。

Key ​

名詞意義例子
superkey能唯一識別一列的欄位集合(可含多餘欄位)(sample_id, collected_on)
candidate key最小的 superkey,少一欄就不唯一sample_id、barcode
primary key選定的 candidate keysample_id
composite key多欄組成的 key(patient_id, visit_date)
natural key業務上本來就有的識別碼病歷號、條碼
surrogate key系統產生、沒有業務意義的 IDGENERATED AS IDENTITY、UUID

natural key 可能改變(重新編號、格式調整)或含個資(病歷號),主鍵常改用 surrogate key,natural key 另加 UNIQUE。外鍵與被參照的主鍵型別必須一致;型別不同是 schema 開始偏掉的訊號。

外鍵與參照完整性 ​

sql
ALTER TABLE order_items
ADD CONSTRAINT order_items_order_id_fkey
FOREIGN KEY (order_id) REFERENCES orders (id)
ON DELETE CASCADE;
刪除父資料時(ON DELETE)行為適合
NO ACTION / RESTRICT(預設)擋下刪除歷史紀錄不能消失(檢驗結果)
CASCADE子資料一起刪子資料沒有父資料就無意義(訂單明細)
SET NULL子資料外鍵改為 NULL保留子資料、解除關聯
SET DEFAULT改為預設值歸到「未分類」

這是資料生命週期的業務決定,不只是語法:明細該不該跟著主資料消失、歷史資料要不要保留。

正規化 vs 維度模型 ​

正規化設計維度設計 (dimensional)
適合OLTP、主資料管理OLAP、BI 報表
目標減少冗餘、避免更新異常查詢直觀、聚合快
形狀很多小表、多層 joinfact table + dimension tables
細節正規化Dimensional Modeling and Star Schema

星狀 (star) 與雪花 (snowflake) schema:

StarSnowflake
dimension一張寬表(反正規化)再拆成多層(正規化)
查詢join 少、直觀,BI 工具友善join 多
冗餘較多較少
選擇分析與報表的預設dimension 很大且需要獨立維護時

View ​

view 是存起來的查詢,查它就像查一張表,但不另存資料。

sql
CREATE VIEW tumor_samples AS
SELECT s.sample_id, s.collected_on, p.sex, p.age_at_diagnosis
FROM samples AS s
JOIN patients AS p USING (patient_id)
WHERE s.is_tumor;

SELECT * FROM tumor_samples WHERE collected_on >= '2024-01-01';
用途注意
封裝常用的 join 與過濾,避免各處重寫每次查詢都重新執行底層查詢,不是效能優化
提供穩定介面:底層表改名、拆表時 view 不變層層疊的 view 很難除錯與調校
權限邊界:只開放 view,不開放含個資的原表下游大量依賴很重的 view 時,改成 materialized view 或正式的分析表

materialized view(PostgreSQL、Oracle、Snowflake)把結果實際存下來,查詢快,但要定期 REFRESH MATERIALIZED VIEW 才會更新。

權限是設計的一部分 ​

sql
CREATE ROLE analyst_role;
GRANT SELECT ON tumor_samples TO analyst_role;     -- 透過 view 開放去識別化後的欄位
REVOKE ALL ON patients FROM analyst_role;          -- 原表不開放
GRANT analyst_role TO alice, bob;                  -- 權限給角色,使用者掛到角色
原則原因
權限給 role,不直接給個人人員異動時只改成員關係
最小權限分析人員只需要讀;寫入留給 ETL 帳號
敏感欄位用 view 或 column-level 權限隔開病歷號、姓名、出生日期等直接識別資訊不外流(見 Privacy and Regulation)

PostgreSQL 的實際語法見 PostgreSQL:psql 與 COPY。

選擇 DBMS ​

工作負載選擇
多人即時寫入、需要交易與 constraintPostgreSQL、MySQL、SQL Server
單機分析大型檔案、不想架伺服器DuckDB(見 DuckDB 與 Parquet)
嵌入應用程式或工具的小型資料庫SQLite
大規模歷史資料分析、多人共用雲端資料倉儲(Snowflake、BigQuery)
原始、異質、尚未定義結構的大量資料資料湖(物件儲存 + Parquet)
巢狀文件、key 查詢、關係網路NoSQL(見 NoSQL 基礎)

常見錯誤 ​

錯誤後果
還沒釐清用途就決定 schema寫入與分析兩頭都不好用
用 OLTP 結構直接承擔大量分析查詢拖慢日常作業,分析也慢
沒有 constraint,靠應用程式檢查繞過應用程式的匯入(腳本、手動修正)帶進錯誤資料
把 view 當成設計問題的補丁層層疊加後無法維護
權限直接發給個人人員異動後殘留權限