為了正規化而建模資料表之間的關聯

今天我們要深入聊一個超重要的主題：資料表之間的關聯建模。 其實正規化不只是把資料拆成原子、去除重複， 還有很大一部分就是要建立正確的資料表關聯。

如果說資料庫是有組織的資訊儲存系統，那資料表之間的關聯就是邏輯上的橋樑，讓你知道資料怎麼互相串連。 想像一下圖書館，書本的資訊和作者的資訊是分開存的，但每本書會透過一個特別的關聯「認識」自己的作者。或者像網路商店：商品資料和顧客資料是獨立的，但當有人下單時，系統會透過訂單表把特定顧客和特定商品連起來。

在醫療診所，病人和自己的病歷卡有關聯，醫生和看診時段有關聯，藥品和處方也有關聯。這些關聯讓系統知道哪些資訊是屬於誰的，不用重複存一堆資料。

這些關聯的基本型態就像現實生活中的關係：護照只屬於一個人（一對一），一個老師可以教很多課（一對多），學生可以選很多課，每門課也有很多學生（多對多）。

一對一（1:1）

這種關聯是「A」表的一筆資料只對應「B」表的一筆資料。 比如我們有「員工」和「護照資料」兩個表。 一個員工只能有一個護照，每本護照也只屬於一個員工。

範例：

員工

護照資料

這裡的關聯是透過 foreign key 員工_id，它指向「員工」表的 id。

一對多（1:N）

這是最常見的關聯型態。這裡「A」表的每一筆資料可以對應「B」表的多筆資料，但「B」表的每一筆資料只對應「A」表的一筆。例如我們有「老師」和「課程」兩個表。一個老師可以教很多課。

範例：

老師

課程

這個關聯是透過「課程」表裡的 foreign key 老師_id 建立的。

多對多（M:N）

當你什麼都很多的時候，真的很有趣但也很複雜。這種情況下，「A」表的每一筆資料可以對應「B」表的多筆資料，反過來也一樣。例如學生可以選很多課，每門課也有很多學生。

範例：

學生

課程

要建立這種關聯，我們需要一個中介表，專門存學生和課程的對應關係：

選課紀錄

用 foreign key 建模關聯

foreign key 就是一個欄位（或一組欄位），它指向另一個表的 primary key 欄位。這是建立資料表關聯的基礎。

foreign key 範例：

CREATE TABLE 課程 (
    id SERIAL PRIMARY KEY,
    課程名稱 VARCHAR(255)
);

CREATE TABLE 選課紀錄 (
    id SERIAL PRIMARY KEY,
    學生_id INT,
    課程_id INT,
    FOREIGN KEY (課程_id) REFERENCES 課程(id)
);

怎麼避免設計 foreign key 時出錯？最重要的是要確定 foreign key 和 primary key 欄位的資料型態一致，不然資料庫根本不讓你建關聯。另外也要先想好刪除資料時要怎麼處理。比如你從父表刪掉一筆資料，子表的資料要怎麼辦？最常見的做法是用 ON DELETE CASCADE，這樣相關資料會自動一起刪掉，不會留下「懸空」的關聯。

多對多關聯的實作

來看個例子：我們有學生和課程。一個學生可以選很多課，一門課也可以有很多學生。要實作 M:N 關聯，我們會建三個表：學生、課程 和 選課紀錄。

CREATE TABLE 學生 (
    id SERIAL PRIMARY KEY,
    名字 VARCHAR(255)
);

CREATE TABLE 課程 (
    id SERIAL PRIMARY KEY,
    課程名稱 VARCHAR(255)
);

CREATE TABLE 選課紀錄 (
    id SERIAL PRIMARY KEY,
    學生_id INT,
    課程_id INT,
    FOREIGN KEY (學生_id) REFERENCES 學生(id),
    FOREIGN KEY (課程_id) REFERENCES 課程(id)
);

現在我們可以往 選課紀錄 表新增資料，把學生和課程連起來。

實作練習

來設計一個課程管理系統的資料庫結構。你要有 學生、課程 和 選課紀錄 三個表。把所有關聯都建好。然後加一些學生、課程和選課的範例資料。 來看看怎麼做：

1. 建立資料表：

CREATE TABLE 學生 (
    id SERIAL PRIMARY KEY,
    名字 VARCHAR(255)
);

CREATE TABLE 課程 (
    id SERIAL PRIMARY KEY,
    課程名稱 VARCHAR(255)
);

CREATE TABLE 選課紀錄 (
    id SERIAL PRIMARY KEY,
    學生_id INT,
    課程_id INT,
    FOREIGN KEY (學生_id) REFERENCES 學生(id),
    FOREIGN KEY (課程_id) REFERENCES 課程(id)
);

2. 插入資料：

INSERT INTO 學生 (名字) VALUES ('Otto Lin'), ('Maria Chi');
INSERT INTO 課程 (課程名稱) VALUES ('SQL 基礎'), ('資料庫管理');
INSERT INTO 選課紀錄 (學生_id, 課程_id) VALUES (1, 1), (1, 2), (2, 1);

3. 查詢資料：

SELECT
    學生.名字 AS 學生, 
    課程.課程名稱 AS 課程
FROM 選課紀錄
JOIN 學生 ON 選課紀錄.學生_id = 學生.id
JOIN 課程 ON 選課紀錄.課程_id = 課程.id;

查詢結果：

建模關聯時的困難與注意事項

你在建模資料表關聯時，可能會遇到這些問題：

• 刪除資料時出錯（比如子表有資料依賴父表的資料）。
• 資料量很大時查詢效能會變差。M:N 關聯特別「吃資源」，因為要多做幾次 join。

這些問題可以這樣解決：

• 在 foreign key 欄位加上 index
• 設計資料庫結構時多想一點。
• 在正規化和效能之間找到平衡。

我們剛剛用很基礎的方式講了資料表關聯建模，還實際做了一個課程管理系統的資料庫結構。當然啦，其實可以舉更大的例子，不過我一時想不到怎麼舉。大例子通常又複雜又無聊，學不到什麼。我會試著在課程快結束時再回來聊這個問題。