前言

前幾天開始接觸 SQL，已經可以透過 SELECT、INSERT、UPDATE、DELETE 操作資料。

不過當資料開始變多之後，慢慢會發現另一個問題：資料到底應該怎麼放？

例如現在的 Note API 已經可以建立筆記，如果接下來加入使用者系統，每一篇 Note 就需要知道是誰建立的，這時候問題就不只是多加一個 user_id 而已，而是會開始碰到資料表怎麼拆、資料表之間怎麼建立關係，以及哪些欄位需要受到限制，這也是開始接觸 Database Design 的地方。

先在 Playground 裡建立 users 和 notes

這次可以直接使用 QueryPlane PostgreSQL Playground，在瀏覽器裡執行 PostgreSQL，不需要先建立自己的資料庫環境。先建立 users 資料表，讓它負責儲存使用者的基本資訊，這裡先不用急著理解每一個設定，後面會再看到 PRIMARY KEY、NOT NULL 和 UNIQUE 分別代表什麼。

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

接著建立 notes，除了筆記本身的資料之外，再加入 user_id，讓每一篇 Note 都能對應到建立它的 User。

CREATE TABLE notes (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  user_id BIGINT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

接著加入幾筆資料，讓兩張表真正產生關係。這裡 Amy 有兩篇 Note，John 則有一篇 Note，因此 user_id 會分別對應到不同的 User。

INSERT INTO users (name, email)
VALUES
  ('Amy', 'amy@example.com'),
  ('John', 'john@example.com');

INSERT INTO notes (title, content, user_id)
VALUES
  ('閱讀筆記', '今天讀了一本很有意思的書', 1),
  ('API 學習', '開始學習 Express', 1),
  ('今天的想法', '整理了一些新的想法', 2);

現在 users 裡有兩個使用者，notes 裡有三篇筆記，而每篇 Note 都能透過 user_id 找到自己的 User。再使用 JOIN 把兩張表查在一起，就能看到原本分開儲存的資料如何重新建立關係。

SELECT
  notes.title,
  notes.content,
  users.name
FROM notes
JOIN users
  ON notes.user_id = users.id;

資料表之間有哪些關係？

資料表之間常見的關係主要有三種：One-to-One（一對一）、One-to-Many（一對多）以及 Many-to-Many（多對多）。真正開始設計資料表時，不是先決定「我要用哪一種關係」，而是先看實際需求，問一個 A 可以對應幾個 B，再反過來看一個 B 可以對應幾個 A，從兩邊的數量就能判斷彼此之間的關係。

例如一個 User 可以建立很多篇 Note，而一篇 Note 只屬於一個 User，因此 User 和 Note 是一對多關係：

User 1 ─── N Note

如果一個 User 只有一份 Profile，而一份 Profile 也只屬於一個 User，就是一對一：

User 1 ─── 1 Profile

如果一篇 Post 可以有很多個 Tag，而同一個 Tag 也可以出現在很多篇 Post，就會形成多對多：

Post N ─── N Tag

所以關係的判斷其實來自需求本身，而不是資料庫語法。先知道資料彼此之間可以對應多少筆，接下來才會知道資料表應該怎麼設計。

User 和 Note 為什麼是一對多？

回到現在的 Note API，users 負責使用者資料，notes 負責筆記資料，而兩者之間的關係是：一個 User 可以有很多篇 Note，一篇 Note 則只會屬於一個 User，因此形成：

User 1 ─── N Note

如果把所有資料直接放在同一張表裡，就必須讓 User 的資料跟著每一篇 Note 一起出現，例如：

users

id
name
email
note_title
note_content

假設 Amy 有三篇 Note，Amy 的姓名和 Email 就會跟著三篇 Note 重複出現；Note 越多，重複的資料就越多，因此資料會拆成兩張表，讓 users 專門處理 User，notes 專門處理 Note，再利用 user_id 把兩者連起來。

users

id
name
email

notes

id
title
content
user_id

這裡的 user_id 放在 notes 是因為 Note 是「多」的那一方，每一篇 Note 都需要知道自己屬於哪個 User，而同一個 User 可以被很多篇 Note 參考，因此在一對多關係中，Foreign Key 通常會放在「多」的那一方。

Primary Key 和 Foreign Key

知道了 User 和 Note 的關係之後，就可以回頭看兩個很重要的欄位。

先看 users：

id | name | email
---|------|----------------
1  | Amy  | amy@example.com
2  | John | john@example.com

這裡的 id 就是 Primary Key，主鍵，簡稱 PK。Primary Key 是用來唯一辨識資料表中的一筆資料，因此每筆資料的 Primary Key 都必須不同。前面建立資料表時使用：

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

其中 PRIMARY KEY 定義 id 是主鍵，而 GENERATED ALWAYS AS IDENTITY 則讓 PostgreSQL 自動產生 id，新增 User 時不需要自己指定。

再看 notes：

id | title        | user_id
---|--------------|--------
1  | 閱讀筆記     | 1
2  | API 學習     | 1
3  | 今天的想法   | 2

這裡的 user_id 就是在記錄每篇 Note 屬於哪個 User，因此它是 Foreign Key，外鍵，簡稱 FK。建立資料表時寫入：

FOREIGN KEY (user_id) REFERENCES users(id)

代表 notes.user_id 參考的是 users.id，所以 user_id 不能填入一個不存在的 User ID，資料庫會在寫入資料時檢查這個關係。

把兩者放在一起看，可以先這樣理解：Primary Key 負責辨識資料，Foreign Key 負責把不同資料表之間的資料連起來。

NOT NULL

除了資料表之間的關係之外，資料庫也可以限制欄位本身的狀態。前面的 Schema 裡出現了 NOT NULL，它代表這個欄位不能是 NULL，例如：

name VARCHAR(100) NOT NULL

這表示建立 User 時，name 必須有值，而：

user_id BIGINT NOT NULL

則表示每一篇 Note 都需要有對應的 User。這些限制會直接寫進資料表裡，讓資料進入資料庫時就受到約束。

UNIQUE

Email 的定義則多了一個 UNIQUE：

email VARCHAR(255) NOT NULL UNIQUE

UNIQUE 代表這個欄位的值不能重複，所以如果資料庫裡已經存在 amy@example.com，再新增另一筆相同的 Email 時，資料庫就會拒絕這筆資料。

PRIMARY KEY 和 UNIQUE 都能限制資料不能重複，但用途並不相同。Primary Key 是用來唯一辨識一筆資料，而 UNIQUE 是用來限制某個欄位不能出現重複值，因此一張表通常會有一個 Primary Key，但可以同時存在多個 UNIQUE 欄位，例如：

users

id        → Primary Key
email     → Unique
username  → Unique

多對多怎麼設計？

一對多之外，另一個常見的情況是多對多。假設現在有 posts 和 tags，一篇 Post 可以有很多個 Tag，而同一個 Tag 也可以出現在很多篇 Post，因此會形成：

Post N ─── N Tag

這時候單純建立 posts 和 tags 兩張表，沒有一個地方可以記錄哪一篇 Post 使用了哪一個 Tag，因此通常會增加一張中間表：

posts

id
title

tags

id
name

post_tags

post_id
tag_id

例如：

posts

id | title
---|----------------
1  | Node.js 入門
2  | PostgreSQL 入門

tags

id | name
---|----------
1  | Backend
2  | Node.js
3  | Database

post_tags

post_id | tag_id
--------|-------
1       | 1
1       | 2
2       | 1
2       | 3

這樣第一篇 Post 就可以同時有 Backend 和 Node.js 兩個 Tag，而第二篇 Post 則可以有 Backend 和 Database。原本的多對多關係，也會透過這張中間表變成兩個一對多的關係。

把整個關係放在一起看

目前 users 和 notes 的資料結構可以整理成：

┌──────────────────┐
│ users            │
├──────────────────┤
│ id PK            │
│ name             │
│ email UNIQUE     │
│ created_at       │
└────────┬─────────┘
         │
         │ 1
         │
         │ N
┌────────▼─────────┐
│ notes            │
├──────────────────┤
│ id PK            │
│ title            │
│ content          │
│ user_id FK       │
│ created_at       │
└──────────────────┘

從這張圖可以把今天的概念串起來：users.id 是 Primary Key，notes.user_id 是 Foreign Key，兩張表形成 One-to-Many 關係，而 NOT NULL 和 UNIQUE 則進一步限制資料的狀態。

之後遇到新的資料表時，也可以用同樣的方式去理解它們之間的關係，先確認一個 A 可以對應幾個 B，再確認一個 B 可以對應幾個 A，接著才會知道資料表怎麼拆，以及 Foreign Key 應該放在哪裡。

補充：線上即時練習網站 - SiteQL

除了 QueryPlane 之外，還有一個偏向題庫型的線上 SQL 練習工具 SiteQL。QueryPlane 比較像自己建立資料表、加入資料，再測試 JOIN、Foreign Key 和 Constraint；SiteQL 則直接提供 SQL 題目，可以在瀏覽器裡寫完 SQL 後立即執行，兩者剛好對應到不同的練習方式。

小結

到了這一天，資料庫開始不只是把資料存進去，而是開始有自己的結構。從 users 和 notes 可以看到，資料表會依照資料的性質拆開，再透過 Foreign Key 建立關係；Primary Key 負責辨識資料，NOT NULL 和 UNIQUE 負責限制資料，而 One-to-One、One-to-Many、Many-to-Many 則描述不同資料之間的對應方式。

前幾天比較像是在學怎麼操作資料，到了今天則開始碰到資料本身怎麼被組織。SQL 是操作資料的語言，Database Design 則是在決定資料之間要如何形成關係。