關聯表對應

🎯 什麼情境該想到我

當你「兩個物件是多對多關係，一邊對多筆、另一邊也對多筆，要存進資料庫」的時候。

⚙️ 怎麼用（步驟 / 公式）

意圖：用一張獨立的關聯表（link table）儲存多對多關聯，每一列記錄一組配對的兩個外鍵。

做法要點：

建立中間表，欄位包含兩端物件的主鍵（兩個外鍵）。
一列代表「A 與 B 相連」的一筆關係；查詢時 join 兩端還原集合。
更新時比對兩端集合，計算要新增 / 刪除哪些關聯列。
關聯本身若有屬性（如加入時間），也放在這張表上。

★★ 中間表的三個層次，決定了它有多痛

-- ★ ① 純關聯（只有兩個外鍵）
CREATE TABLE order_tags (
    order_id text NOT NULL REFERENCES orders(id),
    tag_id   text NOT NULL REFERENCES tags(id),
    PRIMARY KEY (order_id, tag_id)          -- ★★ 複合主鍵在這裡是對的
);
 
-- ★★ ② 關聯帶屬性 —— ★ 這一刻它就不再是「關聯表」，而是一個實體
CREATE TABLE enrollments (
    student_id text NOT NULL,
    course_id  text NOT NULL,
    enrolled_at timestamptz NOT NULL,        -- ★ 屬性
    grade      text,                          -- ★ 屬性
    PRIMARY KEY (student_id, course_id)
);
-- → ★★ 它有自己的生命週期與業務規則 ⇒ 它是一個領域概念（Enrollment），
--    不是「Student 和 Course 之間的一條線」
 
-- ★ ③ 關聯有歷史（同一對可以多次建立與取消）
--   → ★★ 複合主鍵不夠了，需要自己的代理主鍵 + 時間範圍

★★ 判準：這條關聯有自己的名字嗎？
有（Enrollment、Membership、Assignment）→ ★ 它是實體，給它自己的 repo 與領域型別。
沒有（只是「這張訂單有這些標籤」）→ 純關聯表。
★ 把 ② 當成「純關聯」處理，是這個模式最常見的設計錯誤——
於是 grade 這個欄位沒有任何領域物件負責它。

★★ 兩個索引細節（缺了就是全表掃描）

PRIMARY KEY (order_id, tag_id)      -- ★ 這自動建了 (order_id, tag_id) 的索引
-- ★★ 但「反向查詢」沒有索引！
SELECT order_id FROM order_tags WHERE tag_id = $1;   -- ★ 全表掃描
-- ✅ 必須補
CREATE INDEX ON order_tags (tag_id);

★★ 這是關聯表最高頻的效能問題：複合主鍵只覆蓋「左前綴」的查詢。
★ 規則：兩個方向都會查 → 兩個索引都要有。

★★ 更新關聯集合：三種寫法與各自的問題

// ★★ ① 全刪全插（最簡單，也最常用）
DELETE FROM order_tags WHERE order_id = $1;
INSERT INTO order_tags (order_id, tag_id) SELECT $1, unnest($2::text[]);
// ★ 問題：① 若中間表有 created_at，全部被重置
//         ② ★★ 高併發下兩個交易會死鎖（各自鎖了一部分列）
//         ③ ★ 會產生大量 WAL 與 dead tuple（VACUUM 壓力）
 
// ★ ② diff（只加減差異）
//   ★ 正確且省，但要多一次查詢比對現狀
 
// ★★ ③ INSERT … ON CONFLICT DO NOTHING ＋ 明確 DELETE 差集（★ 推薦）
INSERT INTO order_tags (order_id, tag_id)
SELECT $1, unnest($2::text[]) ON CONFLICT DO NOTHING;
DELETE FROM order_tags WHERE order_id = $1 AND tag_id <> ALL($2::text[]);
// ★ 兩句、冪等、不重置既有列、不需要先查

★★ ③ 是實務上的最佳解：冪等（重試安全）、保留既有列的屬性、不需要讀取現狀。

★★ 讀取：多對多的 N+1 特別容易發生

// ✅ ★ 兩次查詢 + 記憶體 group by（同 [[外鍵對應]]）
// ★★ 但多對多要注意：中間表 JOIN 目標表 = 兩次 JOIN
SELECT ot.order_id, t.*
  FROM order_tags ot JOIN tags t ON t.id = ot.tag_id
 WHERE ot.order_id = ANY($1);
// ★ 一次查詢取回「所有訂單的所有標籤」，然後在 Go 裡 group by order_id

⚠️ 如果 tags 是小而穩定的表（幾十筆），★ 更好的做法是整張載進記憶體快取，
中間表只查 tag_id——省一次 JOIN，而且標籤名稱變更時不影響查詢。

★ 什麼時候不要用關聯表

★ 一邊的集合很小且不會被反向查（一張訂單最多 5 個標籤，且從不「查有某標籤的訂單」）
→ ★ 可以考慮陣列欄位（Postgres 的 text[] + GIN 索引）。
⚠️ 代價：失去外鍵約束、JOIN 變複雜、ORM 支援差。
★ 關聯其實是「從屬」（子項不會被別的父共用）→ 從屬對應。
關係是一對多（被誤判成多對多）→ 外鍵對應。

★ 兄弟辨析

	差別
外鍵對應	★ 一對多，不需要中間表
從屬對應	★ 子物件只屬於一個父
識別欄位	★ 關聯表是「複合主鍵」唯一的正當用途
內嵌值	存在同一列裡

🧪 我實際套用的紀錄

（待填）

⚠️ 注意 / 什麼時候不適用

若其實是一對多，用外鍵對應即可，不必多一張表。
更新集合需要差異比對，實作比單一外鍵複雜。
大量關聯時要注意查詢與索引效能。
★★ 關聯帶屬性時它就不是「關聯表」而是一個實體。 判準：這條關聯有自己的名字嗎？有 → 給它領域型別與 repo。
★★ 複合主鍵只覆蓋左前綴，反向查詢必須另建索引。 這是關聯表最高頻的效能問題。
★★ 更新集合用 INSERT … ON CONFLICT DO NOTHING ＋ DELETE … <> ALL($2)：冪等、保留既有屬性、不需先查。
★ 全刪全插在高併發下會死鎖（各自鎖了一部分列），而且產生大量 WAL 與 dead tuple。
★ 小而穩定的目標表（幾十筆）整張載進記憶體更好，省一次 JOIN。
★ 陣列欄位是替代方案，但失去外鍵約束、JOIN 變複雜、ORM 支援差。
關聯表的兩個外鍵都要有索引（一個來自複合主鍵，另一個要自己建）。
一對多被誤判成多對多是常見錯誤，會多出一張不必要的表。

🔗 相關工具

外鍵對應 Foreign Key Mapping（一對一 / 一對多的對照作法）
識別欄位 Identity Field（兩端主鍵的來源）
企業應用架構模式
外鍵對應 —— 一對多
從屬對應 —— 子物件只屬於一個父
識別欄位 —— 複合主鍵的正當用途
內嵌值 —— 存在同一列
延遲載入 —— 多對多的 N+1

Quartz 5

Explorer