ER図とテーブル設計 — リレーションの考え方と正規化

-- 商品とタグの N:N → 中間テーブル product_tags で解決
CREATE TABLE product_tags (
  product_id INT NOT NULL,
  tag_id     INT NOT NULL,
  PRIMARY KEY (product_id, tag_id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (tag_id)     REFERENCES tags(id)
);

❌ 非正規形
| order_id | items            |
|----------|-----------------|
| 1        | りんご, みかん    |
 
✅ 第1正規形
| order_id | item    |
|----------|---------|
| 1        | りんご   |
| 1        | みかん   |

❌ 部分関数従属あり
| order_id | product_id | product_name | quantity |
|----------|-----------|--------------|----------|
| 1        | 101       | りんご        | 3        |
 
product_name は product_id だけで決まる（order_id は不要）
 
✅ 第2正規形
orders_items: order_id, product_id, quantity
products:     product_id, product_name

❌ 推移的関数従属あり
| user_id | department_id | department_name |
 
department_name は department_id で決まる（user_id からは間接的）
 
✅ 第3正規形
users:       user_id, department_id
departments: department_id, department_name

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;
 
-- 論理削除
UPDATE users SET deleted_at = NOW() WHERE id = 1;
 
-- 有効なユーザーだけ取得
SELECT * FROM users WHERE deleted_at IS NULL;

-- WHERE句で頻繁に使うカラムにインデックスを張る
CREATE INDEX idx_users_email ON users(email);
 
-- 複合インデックス（左端のカラムから使われる）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

テーブル名: orders
説明: 注文情報を管理する
 
| カラム名     | 型            | NULL | デフォルト | 説明               |
|-------------|---------------|------|-----------|--------------------|
| id          | BIGINT        | NO   | AUTO_INC  | 主キー              |
| user_id     | BIGINT        | NO   |           | FK: users.id       |
| total_price | DECIMAL(10,2) | NO   |           | 注文合計金額（税込） |
| status      | VARCHAR(20)   | NO   | 'pending' | pending/paid/shipped/cancelled |
| created_at  | TIMESTAMP     | NO   | NOW()     | 作成日時            |
| updated_at  | TIMESTAMP     | NO   | NOW()     | 更新日時            |
| deleted_at  | TIMESTAMP     | YES  | NULL      | 論理削除日時         |
 
インデックス:
- idx_orders_user_id (user_id)
- idx_orders_status (status)
 
非正規化の意図:
- total_price: 注文時点の合計金額を保持。商品価格変更の影響を受けない