[DEE-103] 關聯（1:1、1:N、M:N）
INFO
使用正確的基數模式明確建模每個實體關聯。1:1 和 1:N 使用外鍵；M:N 使用連接表。永遠不要儲存逗號分隔的 ID。
背景
關聯式資料庫將真實世界建模為由關聯連接的實體（資料表）。三種基本的基數類型——一對一（1:1）、一對多（1:N）和多對多（M:N）——決定了外鍵和資料表的結構方式。選擇錯誤的模式會導致資料異常、查詢複雜度和完整性缺口。
原則
- 你 MUST 在 1:N 關聯中將外鍵放在「多」的那一側。
- 你 MUST 為 M:N 關聯使用連接表（join table / bridge table）。
- 你 SHOULD 將 1:1 關聯的 FK 放在可選或次要的資料表上來實現。
- 你 MUST NOT 在單一欄位中儲存多個 ID（逗號分隔、JSON 陣列等）來替代正式的連接表。
- 你 MUST 為關聯中使用的所有外鍵欄位建立索引。
圖解
一對一（1:1）
1:1 關聯表示資料表 A 中的每一列最多對應資料表 B 中的一列。實作方式是在一側放置帶有 UNIQUE 約束的外鍵。
何時使用 1:1：
- 將很少存取的或大型欄位（例如個人簡介、頭像 blob）從頻繁查詢的核心資料表中分離
- 隔離並非每列都會有的可選資料
- 在資料表層級強制存取控制（不同資料表，不同權限）
sql
CREATE TABLE users (
user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL
);
-- Profile 是「可選」的那一側：並非每個使用者都會有
CREATE TABLE profiles (
profile_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE
REFERENCES users(user_id)
ON DELETE CASCADE,
bio TEXT,
avatar_url TEXT
);profiles 中 user_id 上的 UNIQUE 約束強制了 1:1 基數——每個使用者最多只能有一個個人資料。
一對多（1:N）
1:N 關聯表示父資料表中的一列關聯到子資料表中的零或多列。外鍵位於「多」（子）的那一側。
sql
CREATE TABLE posts (
post_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL
REFERENCES users(user_id)
ON DELETE CASCADE,
title TEXT NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 為 FK 建立索引以提升 join 和刪除效能
CREATE INDEX idx_posts_user_id ON posts(user_id);一個使用者撰寫多篇文章。刪除使用者會級聯到其文章（如果文章沒有獨立的存檔價值則適當；如果有則使用 RESTRICT）。
多對多（M:N）
M:N 關聯表示資料表 A 中的列與資料表 B 中的多列相關，反之亦然。關聯式資料庫無法直接表示這種關係——你 MUST 使用連接表（也稱為 join table、bridge table 或關聯實體）。
sql
CREATE TABLE tags (
tag_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
-- 連接表：每一列代表一個文章-標籤關聯
CREATE TABLE post_tags (
post_id BIGINT NOT NULL REFERENCES posts(post_id) ON DELETE CASCADE,
tag_id BIGINT NOT NULL REFERENCES tags(tag_id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
-- 複合 PK 已為 (post_id, tag_id) 建立索引。
-- 為從 tag_id 開始的查詢添加反向索引。
CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);帶有額外屬性的連接表
當關聯本身攜帶資料時，在連接表上添加欄位：
sql
CREATE TABLE course_enrollments (
student_id BIGINT NOT NULL REFERENCES students(student_id),
course_id BIGINT NOT NULL REFERENCES courses(course_id),
enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);這裡的 enrolled_at 和 grade 是註冊（關聯）的屬性，而非學生或課程的屬性。
關聯摘要
| 基數 | FK 放置位置 | 鍵約束 | 範例 |
|---|---|---|---|
| 1:1 | 在可選/次要的資料表上 | 帶 UNIQUE 的 FK | users -- profiles |
| 1:N | 在「多」（子）的資料表上 | FK（無 UNIQUE） | users -- posts |
| M:N | 帶有兩個 FK 的連接表 | 兩個 FK 上的複合 PK | posts -- post_tags -- tags |
常見錯誤
| 錯誤 | 為何有害 | 修正 |
|---|---|---|
欄位中的逗號分隔 ID（例如 tags = '1,3,7'） | 無法強制 FK 完整性、無法建索引、無法高效 join、違反 1NF | 使用連接表 |
ID 的 JSON 陣列（例如 tag_ids jsonb） | 與 CSV 相同的問題加上儲存開銷；jsonb GIN 索引有助讀取但不強制完整性 | 使用連接表；將 JSON 保留給真正無 schema 的資料 |
| 不必要的 1:1 拆分 | 「以防萬一」的拆分增加了 join 開銷卻沒有好處 | 僅在有可衡量的理由（欄位大小、存取模式、安全性）時才拆分 |
| 「多」側缺少 FK 索引 | JOIN 和 CASCADE DELETE 效能降級為順序掃描 | 始終為 FK 欄位建立索引 |
| M:N 沒有複合 PK | 允許重複關聯（例如同一標籤被標記到同一文章兩次） | 在連接表上使用 PRIMARY KEY (a_id, b_id) |
| 在簡單連接表上使用代理 PK | 增加一個無用的欄位和索引；複合 FK 對已經是唯一的了 | 僅在連接列被其他資料表引用時才加代理 PK |
| 1:1 的雙向 FK | 循環引用需要延遲約束並使插入複雜化 | 僅在一側放 FK；用 UNIQUE 強制基數 |