Database design

データベース設計

目的

スキーマは後から変えるコストが最も高い部分のひとつ。MVPであっても丁寧に設計し、正規化・制約・インデックスの根拠を記録する。設計の意思決定をテーブル定義書とER図に残すことで、チームの認識統一とレビューを促進する。

使うタイミング

• 新規テーブル・カラムの追加設計
• 既存スキーマの見直し・リファクタリング
• マイグレーション計画の策定
• パフォーマンス問題のインデックス調査
• 技術的負債の棚卸し（EAV・JSON乱用の整理）

進め方

1. 要件からエンティティ抽出

   • [[requirements-definition]] の成果物を参照し、名詞（人・物・出来事・概念）を洗い出す
   • 「何を登録し、何を検索し、何を集計するか」のアクセスパターンをリストアップ

2. 関連の定義

   • 各エンティティ間を 1:1 / 1:N / N:M に分類
   • N:M は中間テーブル（関連テーブル）で表現。配列カラムで代替しない

3. Mermaid ER図の作成

erDiagram
    users {
        bigint id PK
        varchar email UK
        varchar name
        timestamp created_at
        timestamp updated_at
    }
    orders {
        bigint id PK
        bigint user_id FK
        decimal total_amount
        varchar status
        timestamp ordered_at
        timestamp created_at
        timestamp updated_at
    }
    order_items {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int quantity
        decimal unit_price
    }
    products {
        bigint id PK
        varchar name
        decimal price
        timestamp created_at
        timestamp updated_at
    }

    users ||--o{ orders : "places"
    orders ||--|{ order_items : "contains"
    products ||--o{ order_items : "included in"

4. 正規化（第3正規形を基本）

   • 繰り返しグループを別テーブルへ（第1正規形）
   • 部分関数従属を除去（第2正規形）
   • 推移的関数従属を除去（第3正規形）
   • 意図的に非正規化する場合は理由をコメントで明記

5. アクセスパターン確認とインデックス設計

   • WHERE / ORDER BY / JOIN の列をリストアップ
   • 複合インデックスは「選択性の高い列・等価条件の列」を先頭に
   • 書き込みコストとのトレードオフを評価（過剰インデックスは書き込みを遅くする）

6. テーブル定義書の作成（成果物テンプレートを使用）

7. マイグレーション計画

   • 後方互換を保つ段階方式：列追加 → コード切替 → 旧列削除
   • ロールバック手順を事前に用意
   • 本番適用前にバックアップ取得を必須とする

設計の要点

制約はDBで守る

NOT NULL / UNIQUE / 外部キー / CHECK 制約をスキーマに明示する。「アプリ側でバリデーションしているから不要」は複数経路（バッチ・管理ツール・直接SQL）からの書き込みで破綻する。

型と命名規約

項目	方針
テーブル名	複数形・snake_case（orders, order_items）
カラム名	snake_case
外部キー	参照先テーブル単数形_id（user_id）
金額	decimal(12, 2) ― floatは誤差が出る
真偽値	boolean（0/1 の integer は避ける）
日時	timestamp、UTC保存・表示時に変換
NULL	意味のあるNULLのみ許容。デフォルト値で回避できる場合は NOT NULL

ID設計

• 内部ID: 自動連番（bigint）— 結合・インデックスが効率的
• 公開ID: UUID v4 または ULID — 推測不可能で分散生成可。外部に見せるIDは必ず推測不可能に（[[security]] のIDOR対策と連動）
• 両方持つ設計も有効（内部はserial PK、APIレスポンスはuuid）

論理削除の判断基準

deleted_at による論理削除は慎重に検討する。

• 採用すべき場面: 復元要件がある、監査ログとして削除履歴を保持する必要がある
• 採用すべきでない場面: 復元不要で全クエリに WHERE deleted_at IS NULL が増えるだけ、UNIQUE制約と衝突する（同メールで再登録不可になる等）
• 論理削除を採用する場合、ソフトデリートに対応したORMフィルタか共通スコープを必ず設ける

タイムスタンプ定番カラム

全テーブルに created_at / updated_at を付与。DBのデフォルト値とトリガー（またはORMのフック）で自動セット。

成果物テンプレート

## テーブル定義書: orders（注文）

### 概要
ユーザーが行った注文を管理するテーブル。

### カラム定義

| カラム名 | 型 | 制約 | デフォルト | 説明 |
|---|---|---|---|---|
| id | bigint | PK, NOT NULL | auto | 内部ID（連番） |
| public_id | uuid | UK, NOT NULL | gen_random_uuid() | 外部公開用ID |
| user_id | bigint | FK(users.id), NOT NULL | — | 注文者 |
| status | varchar(20) | NOT NULL | 'pending' | 注文ステータス |
| total_amount | decimal(12,2) | NOT NULL | — | 合計金額（税込） |
| ordered_at | timestamp | NOT NULL | — | 注文確定日時（UTC） |
| created_at | timestamp | NOT NULL | now() | レコード作成日時（UTC） |
| updated_at | timestamp | NOT NULL | now() | レコード更新日時（UTC） |

### インデックス

| インデックス名 | 種別 | 対象カラム | 理由 |
|---|---|---|---|
| orders_pkey | PK | id | — |
| orders_public_id_key | UK | public_id | API参照用 |
| orders_user_id_idx | 通常 | user_id | ユーザー別注文一覧 |
| orders_status_ordered_at_idx | 複合 | status, ordered_at DESC | ステータス絞り込み＋時系列ソート |

### 備考
- status の取りうる値: `pending`, `paid`, `shipped`, `cancelled`
- total_amount は order_items から集計した値を非正規化して保持（集計コスト削減のため。更新は注文確定時のみ）

チェックリスト

• 全テーブルに created_at / updated_at がある
• 外部キー制約がスキーマに定義されている
• 金額カラムが decimal 型を使っている
• 外部公開するIDが推測不可能（UUID/ULID）
• 非正規化箇所に理由コメントがある
• 各テーブルのアクセスパターンに対してインデックスが設計されている
• 過剰インデックスがない（書き込み頻度と照合済み）
• 論理削除の採否が要件に基づいて判断されている
• マイグレーションのロールバック手順が存在する
• 本番適用前のバックアップ取得手順が計画に含まれている
• NULL許容の根拠が各カラムで説明できる

アンチパターン

アンチパターン	何が問題か	代替
EAV（汎用属性テーブル）	型安全性なし、クエリが複雑で遅い、インデックス設計困難	専用カラム・JSONBの限定活用・サブタイプテーブル
外部キー制約なし運用	孤立レコードが発生し整合性がアプリ任せになる	必ずスキーマで外部キーを定義
構造化データを全部JSON列に	検索・インデックス・型安全性の喪失。スキーマ進化も困難	構造が確定しているものは正規化。可変属性のみJSONB
本番DBへの手作業SQL	ロールバック不可・変更履歴なし・環境差異発生	必ずマイグレーションファイル経由で適用
N:M関係を配列カラムで管理	JOINできない、制約貼れない、クエリが煩雑	中間テーブルで表現
論理削除の安易な採用	全クエリへのフィルタ追加漏れ・UNIQUE制約との衝突	復元・監査要件がある場合のみ採用し、ORMスコープで隠蔽

モデル委譲ガイド

共通原則は [[orchestration]] を参照。

役割	担当内容
司令塔（メインモデル）	エンティティ境界の確定・正規化レベルの判断・非正規化の承認・設計全体の整合性確認
Opus相当	複雑なデータモデル（多態性・階層構造・マルチテナント設計）のレビューと代替案提示
Sonnet相当	テーブル定義書のドラフト・マイグレーションファイル作成・インデックス候補の調査・ER図の生成
Haiku相当	既存スキーマの棚卸し・カラム一覧の抽出・命名規約チェック・定型コメントの付与

関連スキル

• [[requirements-definition]] — エンティティ抽出の起点となる要件定義
• [[architecture-design]] — DB種別（RDBMS/NoSQL）の選定
• [[mvp-development]] — MVPでも丁寧に設計すべき部分の判断基準
• [[security]] — 公開IDのIDOR対策・権限設計
• [[performance-optimization]] — クエリ最適化・インデックスチューニング
• [[api-design]] — APIレスポンスとDBスキーマのマッピング設計
• [[implementation]] — マイグレーション実装・ORM設定
• [[refactoring]] — スキーマリファクタリングの段階的アプローチ
• [[orchestration]] — モデル委譲の共通原則