資料庫設計入門：
ER Diagram、一對多、正規化

users                posts              comments
┌──────────┐         ┌──────────┐       ┌──────────────┐
│ user_id  │1       N│ post_id  │1     N│ comment_id   │
│ username │─────────│ user_id  │───────│ post_id      │
│ email    │         │ title    │       │ user_id      │
│ password │         │ content  │       │ text         │
│ created  │         │ created  │       │ created_at   │
└──────────┘         └──────────┘       └──────────────┘

一個 user 可以有多個 posts（一對多 1:N）
一個 post 可以有多個 comments（一對多 1:N）
一個 user 也可以發多個 comments（一對多 1:N）

          follows（追蹤關係）
┌──────────────────────────────┐
│ follower_id（user_id）       │  ← 追蹤者
│ following_id（user_id）      │  ← 被追蹤者
└──────────────────────────────┘

users 追蹤 users → 同一實體的多對多（M:N）
透過中間表 follows 解決（稍後說明）

-- ❌ 違反 1NF：一個欄位存多個值
CREATE TABLE users_bad (
  user_id  INT,
  phones   VARCHAR(100)  -- '0912-123456, 0955-654321'（一格存了兩支電話）
);

-- ❌ 也違反 1NF：重複欄位
CREATE TABLE users_bad2 (
  user_id  INT,
  phone1   VARCHAR(20),  -- 第一支電話
  phone2   VARCHAR(20),  -- 第二支電話（如果沒有就 NULL）
  phone3   VARCHAR(20)   -- 第三支電話
);

-- ✅ 符合 1NF：每個事實一列
CREATE TABLE user_phones (
  user_id  INT REFERENCES users(user_id),
  phone    VARCHAR(20) NOT NULL,
  PRIMARY KEY (user_id, phone)  -- 一個用戶同一支電話只存一次
);

-- ❌ 違反 2NF
-- 組合主鍵是 (order_id, product_id)
-- 但 product_name 只依賴 product_id，不依賴整個組合主鍵
CREATE TABLE order_items_bad (
  order_id     INT,
  product_id   INT,
  product_name VARCHAR(100),  -- ← 只依賴 product_id，部分依賴！
  quantity     INT,
  PRIMARY KEY (order_id, product_id)
);

-- ✅ 符合 2NF：把 product_name 移到 products 表
CREATE TABLE products (
  product_id   INT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price        DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_items (
  order_id     INT REFERENCES orders(order_id),
  product_id   INT REFERENCES products(product_id),
  quantity     INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
-- 現在 order_items 裡的每個欄位都完全依賴組合主鍵

-- ❌ 違反 3NF
-- user_id → zip_code → city（city 透過 zip_code 間接依賴 user_id）
CREATE TABLE users_bad (
  user_id   INT PRIMARY KEY,
  zip_code  CHAR(5),
  city      VARCHAR(50)  -- ← city 依賴 zip_code，不直接依賴 user_id
);

-- 問題：台北市的 100 郵遞區號存了幾萬次
-- 台北市改名時，要更新幾萬列

-- ✅ 符合 3NF：把 city 移到獨立的 zip_codes 表
CREATE TABLE zip_codes (
  zip_code  CHAR(5) PRIMARY KEY,
  city      VARCHAR(50) NOT NULL,
  district  VARCHAR(50)
);

CREATE TABLE users (
  user_id   INT PRIMARY KEY,
  zip_code  CHAR(5) REFERENCES zip_codes(zip_code)
);
-- 城市名稱只存一次，修改時只要改一列

users      1:N  orders        （一個用戶有多張訂單）
orders     1:N  order_items   （一張訂單有多個商品明細）
products   1:N  order_items   （一個商品可在多張訂單出現）
categories 1:N  products      （一個分類有多個商品）

orders ↔ products 是 M:N，透過 order_items 中間表解決

-- 用戶表
CREATE TABLE users (
  user_id    BIGSERIAL PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  username   VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 商品分類表
CREATE TABLE categories (
  category_id   BIGSERIAL PRIMARY KEY,
  name          VARCHAR(100) NOT NULL,
  parent_id     BIGINT REFERENCES categories(category_id)
  -- 自引用：支援多層分類（如 電子產品 > 手機 > 智慧型手機）
);

-- 商品表
CREATE TABLE products (
  product_id   BIGSERIAL PRIMARY KEY,
  category_id  BIGINT REFERENCES categories(category_id),
  name         VARCHAR(255) NOT NULL,
  description  TEXT,
  price        DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
  stock        INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
  created_at   TIMESTAMPTZ DEFAULT NOW()
);

-- 訂單表
CREATE TABLE orders (
  order_id    BIGSERIAL PRIMARY KEY,
  user_id     BIGINT NOT NULL REFERENCES users(user_id),
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
               -- pending | paid | shipped | delivered | cancelled
  total       DECIMAL(10, 2),  -- 可用觸發器或應用層計算
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 訂單明細（orders ↔ products 的中間表）
CREATE TABLE order_items (
  order_id    BIGINT NOT NULL REFERENCES orders(order_id),
  product_id  BIGINT NOT NULL REFERENCES products(product_id),
  quantity    INT NOT NULL CHECK (quantity > 0),
  unit_price  DECIMAL(10, 2) NOT NULL,
  -- ↑ 關鍵設計：記錄下訂時的售價
  -- 商品日後漲價或打折，訂單歷史不受影響
  PRIMARY KEY (order_id, product_id)  -- 組合主鍵，同一訂單同一商品只能一列
);