你跟我現在算什麼關係…? 現實世界中曖昧總是讓人受盡委屈，而在資料庫的世界裡，則是不能找不到資料表關聯的證據，那麼何時該確認呢? 在資料庫系統設計一開始我們就必需了解資料表彼此之間的關係，因為資料表所會出現的三種關係將會影響到整個系統的資料表要如何被設計。

資料表的三種關係

我們用幾個例子來理解

1. 咒術高專VS咒術師，一個咒術高專畢業許多咒術師，一個咒術師畢業於一個咒術高專，這是一對多的關係。
2. 鬼殺隊VS鬼，一個鬼殺隊成員可以殺死多隻鬼，一個鬼可以殺死多個鬼殺隊成員，這是多對多的關係。
3. 火影VS木葉，一個火影屬於一個木葉忍者村，一個木葉忍者村有一個火影，這是一對一的關係。

需求分析

1. 我們想建立一個系統可以建立計畫並且能夠看到計畫有哪些人。
2. 使用者可以登入這個系統
3. 系統需要顯示使用者的單位名稱

進行這段描述的分析，想一下，在現實世界中，你會把哪些東西當成不同東西在講?

1. 計畫跟員工是會分成不同東西在講的，因此可以建立<計畫資料表>跟<員工資料表>
2. 員工資料當中的單位也可以當成不同東西在講，因此可以再建立一個<單位資料表>
3. 員工跟系統帳號也可以分成不同的東西來講，因此也可以再分出<帳號資料表>
4. 所以我們一共會有<計畫資料表><員工資料表><單位資料表><帳號資料表>

搞清楚表格關係該如何配對，這個過程中可能會經過反覆的確認，最終定義出關係。

1. 一個計畫有多個人，每個人可以參加多個計畫，計畫跟員工會是多對多的關係。
2. 一個員工屬於一個單位，一個單位有多個員工，員工跟計畫是多對一的關係。
3. 一個員工有一組帳號，一組帳號給一個員工使用，員工跟帳號是一對一的關係。

繪製ERD

資料表的關係，我們可以透過 ERD (Entity Relationship Diagram) 來更加方便看出整體的關係，市場上有許多可以繪製ERD的軟體，我個人覺得 Lucidchart 直覺好用，所以推薦給大家，註冊後就可以使用免費版本，雖然有數量的限制，但是可以透過匯出匯入的小技巧，達到使用到更多的數量，不過如果公司願意噴預算，那當然要省下這個麻煩啦:)

一對一跟一對多都可以很容易用看的就理解了，但是在資料庫世界中要直接多對多其實很困難，幸好有一個已經被當成多對多解答的方法，必需在兩個多對多關係中的資料表中間再放一張表，這張表的功能是組合兩個主鍵形成兩個一對多，道理是兩個一對多關係，串再一起便會是多對多。

透過下列表格來理解一下，安安計畫在對應表當中有王大通、陳小聰跟廖小明，這樣可以看出一對多的關係，接著來看王大通同時在安安計畫跟你好計畫當中，所以是多對一的關係，因此計畫跟成員就能形成多對多的對應。

計畫表

員工表

對應表

建立資料表

1. 單位資料表放代碼跟名稱。
2. 員工資料表放名字、生日、信箱跟單位代碼。
3. 計畫資料放代碼、名稱、主持人、起始日跟結束日。
4. 帳號資料表放信箱跟密碼。
5. 計畫與員工中間表放各自的主鍵。

CREATE TABLE units (
    id SERIAL PRIMARY KEY,
    code VARCHAR(300) NOT NULL,
    name VARCHAR(300) NOT NULL
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(300) NOT NULL,
    birthdate DATE NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL,
    unit_id INT REFERENCES units (id) ON DELETE SET NULL
);

CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    code VARCHAR(300) NOT NULL,
    name VARCHAR(300) NOT NULL,
    principal_investigator INT REFERENCES employees (id) ON DELETE SET NULL,
    date_beg  DATE NOT NULL,
    date_end  DATE NOT NULL
);

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    email VARCHAR(200) REFERENCES employees (email) ON DELETE CASCADE,
    password VARCHAR(200) NOT NULL
);

CREATE TABLE projects_employees (
    id SERIAL PRIMARY KEY,
    project_id INT REFERENCES projects ON DELETE CASCADE,
    employee_id INT REFERENCES employees ON DELETE CASCADE
);

今晚想要來點資料佐哇沙米

這一段沒什麼好介紹的，從一開始跟到現在，大家應該也是老熟了，給這個架構一點點基本的資料。

INSERT INTO units  (code,name)
VALUES 
  ('A001','研發部'), 
  ('B001','開發部'), 
  ('C001','分析部');

INSERT INTO employees 
  (name, birthdate, email, unit_id)
VALUES 
  ('呂光晴','1962-06-18','misawa1981@gmail.com', 1),
  ('橘建太', '1967-03-27', 'kobashi1988@gmail.com', 1),
  ('黃利明', '1963-12-08', 'kawada1982@gmail.com', 2),
  ('宏上明', '1961-05-08', 'akira1980@gmail.com', 2);

INSERT INTO projects 
	(code ,name, principal_investigator, date_beg, date_end)
VALUES 
  ('2022JP00001', '諾亞方舟計畫', 1, '2022-01-01','2024-12-31'),
  ('2022JP00002', '迴旋肘擊離心力研究', 1, '2022-04-01','2023-12-31'),
  ('2022JP00003', '綠寶石飛瀑怒濤', 1, '2022-07-01','2022-12-31');

INSERT INTO projects_employees 
	(project_id, employee_id)
VALUES 
	(1,2),
	(2,2),
	(2,4),
	(3,3),
	(3,4);

串起來

最後把資料串起來，employees表格JOIN兩次是因為第二次是要對應計畫主持人的編號，這邊也再帶到一個技巧，同一張表不是只能JOIN一次。

SELECT 
	p.code AS 計畫代碼,
	p.name AS 計畫名稱,
	e.name AS 計畫人員,
	e2.name AS 計畫主持人,
	date_beg AS 計畫起始日,
	date_end AS 計畫結束日
FROM projects AS p
JOIN projects_employees AS pe ON pe.project_id = p.id
JOIN employees AS e ON pe.employee_id = e.id
JOIN employees AS e2 ON e2.id = p.principal_investigator
ORDER BY p.code, date_beg, date_end;