關聯式資料建模

實體關係模型會在選擇資料表之前，識別實體類型、其屬性與關係。接著，關聯式綱要會以定義的粒度將每個事實表示一次。例如，order_line 表示一筆訂單中的一個產品，而 order 表示訂單標頭。

實體、鍵與關係 #

每個資料表都需要一個能唯一識別其資料列的候選鍵。選擇主鍵時，應確保它穩定、不可為 NULL，且適合用於聯結。使用代理鍵並不代表不需要針對自然的業務識別建立唯一性約束，例如帳戶的外部識別碼。

依照以下方式對應關係基數：

1. 對於一對一關係，將 UNIQUE 外鍵放在可選的一方；如果兩個資料表的生命週期與存取控制一致，也可以合併資料表。
2. 對於一對多關係，將外鍵放在多方資料表中。
3. 對於多對多關係，建立一個關聯資料表，其中包含指向兩個實體的外鍵，以及屬於該關係的屬性。

NOT NULL 表示必須參與該關係。可為 NULL 的外鍵表示對該資料列而言，該關係是可選的。外鍵約束可以防止孤立參照，而 ON DELETE 行為必須反映業務規則：限制刪除、串聯刪除、設為 NULL，或在明確的工作流程中處理刪除。

正規化 #

正規化從函數相依開始：如果 X 的一個值決定 Y 的一個值，則寫作 X -> Y。第一正規形要求每個欄位包含宣告網域中的一個值，且不存在重複群組。第二正規形移除只依賴複合候選鍵一部分的非鍵屬性。第三正規形移除傳遞相依，也就是某個非鍵屬性決定另一個非鍵屬性。這些規則讓每個事實都有唯一的權威位置，因此可以避免插入、更新與刪除異常。

只有在測量讀取成本並定義如何讓重複值保持正確之後，才應進行反正規化。記錄由交易、觸發程序、產生欄、變更資料擷取消費者，或重建工作維護複本；接著監控資料過時狀態並進行核對。索引、摘要資料表或查詢重寫可以解決讀取路徑問題，而不必建立無法控制的業務資料複本。

CREATE TABLE order_line (
  order_id BIGINT NOT NULL,
  line_number SMALLINT UNSIGNED NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (order_id, line_number),
  CONSTRAINT fk_order_line_order
    FOREIGN KEY (order_id) REFERENCES orders (id),
  CONSTRAINT fk_order_line_product
    FOREIGN KEY (product_id) REFERENCES product (id)
) ENGINE = InnoDB;

複合主鍵明確表示資料表的粒度：一筆訂單內的某個行號只會存在一次。InnoDB要求存在一個索引，其前導欄位涵蓋每個外鍵，並且可能會自動建立該索引；新增多餘索引之前，請檢查 SHOW CREATE TABLE 與查詢計畫。

參考資料 #

• InnoDB外鍵約束
• MySQL約束