database-schema-design

データベーススキーマ設計

概要

適切なデータモデリング、安全なマイグレーション、効果的なインデックス作成、適切なクエリパターンに基づいて、データベーススキーマの設計、実装、最適化を行います。このスキルでは、概念モデリングから物理設計の最適化まで、ライフサイクル全体を扱い、正規化され、パフォーマンスに優れ、安全に進化できるスキーマを実現します。

開始時に伝えます：「データベーススキーマを設計するため、database-schema-design スキルを使用します。」

フェーズ 1：要件の把握と概念モデル

データ要件を理解するため、次の質問をします。

#	質問	判断できること
1	システムが管理するエンティティは何ですか？	テーブル名
2	エンティティ間にはどのような関係がありますか？	外部キー、中間テーブル
3	各エンティティの主要な属性は何ですか？	列の定義
4	主なクエリパターンは何ですか？	インデックス戦略
5	想定されるデータ量はどのくらいですか？（行数、増加率）	パーティショニング、スケーリング
6	読み取りと書き込みの比率はどのくらいですか？	正規化と非正規化の選択
7	SQL と NoSQL のどちらを使いますか？（または両方ですか？）	ストレージエンジンの選択

ストレージエンジンの選択表

要素	SQL を選択（PostgreSQL、MySQL）	ドキュメント型を選択（MongoDB）	キー・バリュー型を選択（Redis）
データの形式	構造化データ、リレーショナルデータ	半構造化データ、ネストされたデータ	単純な検索、キャッシュ
クエリの複雑さ	複雑な結合、集計	ドキュメント単位のクエリ	キーによるアクセスのみ
一貫性の要件	ACID が必須	結果整合性で問題なし	一時的なデータ、またはキャッシュデータ
スキーマの進化	マイグレーションで管理可能	スキーマに縛られない柔軟性	スキーマなし
スケーリングのパターン	まず垂直スケーリング、その後に読み取りレプリカ	水平シャーディング	インメモリ、サイズに制限あり

要件の把握後は停止し、確認のため概念モデル（エンティティ、関係、カーディナリティ）を提示します。

フェーズ 2：論理モデルの設計

概念モデルをテーブル、列、型、制約に落とし込みます。

列設計のルール

判断項目	指針
主キー	分散システムでは UUID、単一ノードでは自動採番を使用します
列の型	最も具体的な型を使用します（日付には varchar ではなく timestamptz）
NULL 許容	デフォルトは NOT NULL とし、値がないことに意味がある場合に限り NULL を許可します
デフォルト値	適切なデフォルト値を設定します（created_at DEFAULT now()）
制約	CHECK、UNIQUE、FK 制約をスキーマレベルで追加します
命名	snake_case を使用し、テーブル名は単数形か複数形のどちらかに統一します

正規化ガイド

正規形	ルール	違反例	修正方法
1NF	値は原子性を持ち、繰り返しグループがない	tags VARCHAR "urgent,priority,vip"	order_tags テーブルを分けます
2NF	すべての非キー列が主キー全体に依存する	複合主キーを持つ order_items 内の product_name	products テーブルに移します
3NF	推移的な関数従属がない	city は user_id ではなく zip_code に依存する	zip_codes テーブルを分けます

ルール： まずは常に正規化します。測定結果に基づく根拠がある場合にのみ、非正規化します。

非正規化の判断表

シナリオ	パターン	適用するタイミング
読み取りが多いダッシュボード	マテリアライズドビューまたはサマリーテーブル	クエリの遅さが測定された場合
頻繁に結合するデータ	JSONB 列に埋め込みます	クエリ時間の 80% 超を結合が占める場合
レポート／分析	非正規化したレポート用テーブルを分けます	OLAP ワークロード
キャッシュ層	書き込み時に更新する計算済み列	高頻度の読み取り

リレーションシップのパターン

リレーションシップ	実装	必要なインデックス
1 対 1	子テーブルに UNIQUE 制約付きの FK	FK 列
1 対多	「多」側に FK	FK 列
多対多	複合 PK を持つ中間テーブル／結合テーブル	両方の FK 列
ポリモーフィック	CHECK 制約付きの個別の FK 列（推奨）、または type+id パターン	type+id または各 FK
自己参照（ツリー）	同じテーブルを参照する parent_id FK、または ltree／マテリアライズドパス	parent_id またはパス

論理モデルの段階で停止し、レビュー用にテーブル定義を提示してください。

フェーズ 3：物理モデルとインデックス作成

インデックスタイプの選択表

インデックスタイプ	最適な用途	例
B-tree（デフォルト）	等価検索と範囲検索	CREATE INDEX idx_users_email ON users(email)
GIN	全文検索、JSONB、配列	CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', body))
部分インデックス	条件に一致する行のサブセット	CREATE INDEX idx_active_users ON users(email) WHERE active = true
カバリング（INCLUDE）	テーブル参照を避けるインデックスのみのスキャン	CREATE INDEX idx_users_email ON users(email) INCLUDE (name)
複合インデックス	複数列に対するクエリ	CREATE INDEX idx_orders ON orders(tenant_id, status)

複合インデックスの列順

位置	列のタイプ	理由
先頭	カーディナリティの高い等価条件の列	最も選択性の高いフィルターを先頭にする
中間	追加の等価条件の列	結果をさらに絞り込む
末尾	範囲条件の列（日付、数値）	残りの行に対して範囲スキャンを行う

ルール： (A, B, C) の複合インデックスは、A、A+B、A+B+C に対するクエリをサポートしますが、B 単独または C 単独はサポートしません。

クエリ最適化チェックリスト

EXPLAIN ANALYZE のシグナル	問題	修正方法
大規模テーブルでの Seq Scan	インデックスがない	適切なインデックスを追加する
外側のテーブルが大きい Nested Loop	結合が非効率	インデックスを追加するか、クエリを再構成する
実際の行数と推定行数の差が大きい	統計情報が古い	テーブルに対して ANALYZE を実行する
Hash Join のメモリ使用量が大きい	work_mem が小さすぎる	work_mem を調整するか、クエリを再構成する

N+1 の検出と防止

-- N+1 problem (bad):
SELECT * FROM users;
-- Then for EACH user: SELECT * FROM orders WHERE user_id = ?;

-- Fixed with join:
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- Fixed with batch load:
SELECT * FROM orders WHERE user_id = ANY($1);

物理モデルの段階で停止し、インデックスと最適化戦略をレビュー用に提示してください。

フェーズ 4：マイグレーション戦略

ゼロダウンタイムマイグレーション（拡張・縮小）

1 つのマイグレーションで破壊的変更を行わないでください。2 つのフェーズに分けてください。

拡張フェーズ（後方互換性あり）：

1. 新しい列／テーブルを追加する（NULL 許容、またはデフォルト値を設定）
2. 古い列と新しい列の両方に書き込むコードをデプロイする
3. 既存データをバッチ処理でバックフィルする
4. 新しい列から読み込むコードをデプロイする

縮小フェーズ（すべてのコードが新しいスキーマを使用した後）：

1. 古い列への書き込みを行うコードを削除する
2. 古い列／テーブルを削除する

マイグレーションの安全性ルール

ルール	理由
すべてのマイグレーションに対応するロールバックを用意する	安全に元に戻せる
本番環境に適用する前にステージング環境でロールバックをテストする	元に戻せることを確認する
データを破壊するロールバックには明示的な承認が必要	誤ったデータ損失を防ぐ
適用済みのマイグレーションファイルは変更しない	状態を再現可能にする
大規模テーブルはバッチ（1000 行）でバックフィルする	テーブルロックを回避する

バックフィルのパターン

-- Backfill in chunks of 1000
UPDATE users SET display_name = username
WHERE display_name IS NULL
AND id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 1000);

マイグレーションタイプの選択表

変更タイプ	安全な方法	危険な方法
列の追加	NULL 許容、またはデフォルト値を設定して追加する	デフォルト値なしで NOT NULL を追加する
列の削除	拡張・縮小（2 回のデプロイ）	列を直接削除する
列名の変更	新しい列を追加し、データをコピーしてから古い列を削除する	ALTER RENAME（クエリが動作しなくなる）
インデックスの追加	CREATE INDEX CONCURRENTLY	CREATE INDEX（テーブルをロックする）
列の型の変更	新しい列を追加してデータを移行する	ALTER COLUMN TYPE（テーブルをロックする）

マイグレーション計画の段階で停止し、最終決定の前にロールバック戦略を確認してください。

フェーズ 5：保存と移行

明示的な承認を得た後：

1. スキーマ設計を docs/database/ に保存するか、マイグレーションファイルを生成します
2. 次のメッセージでコミットします：docs(db): add schema design for <feature>

移行先の判断表

ユーザーの意図	次のスキル	理由
「マイグレーションを作成して」	planning	マイグレーションの実装を計画します
「これの仕様を書いて」	spec-writing	データ操作の振る舞いに関する仕様を作成します
「スキーマを実装して」	test-driven-development	マイグレーションテストを用いた TDD
「設計を保存するだけでいい」	なし	スキーマ設計が成果物です
「パフォーマンスをレビューして」	performance-optimization	クエリパターンを分析します

ORM のガイダンス

ORM	言語	強み	注意点
Prisma	TypeScript	型安全なスキーマ、マイグレーション	ネストされたクエリでの N+1、限られた生 SQL 対応
Drizzle	TypeScript	SQL に似た API、軽量	エコシステムが新しく、ガイドが少ない
SQLAlchemy	Python	成熟しており、柔軟で、生 SQL に対応	複雑なセッション管理
GORM	Go	規約ベース、自動マイグレーション	サイレントな失敗、暗黙の動作

ORM のベストプラクティス

• 生成された SQL は必ず確認します（開発環境でクエリログを有効にします）
• N+1 クエリを防ぐため、イーガーローディングを使用します
• ORM と格闘するのではなく、複雑なクエリには生 SQL を記述します
• 本番環境では自動同期ではなく、ORM のマイグレーションを使用します
• 現実的なデータ量でクエリのパフォーマンスをテストします

コネクションプーリング

• コネクションプーラー（PgBouncer、組み込みプール）を使用します
• プールサイズの計算式：connections = (CPU cores * 2) + disk spindles
• ほとんどのワークロードでは、トランザクションレベルのプーリングを使用します
• アプリケーションサーバーから生のコネクションを開かないようにします

アンチパターン／よくある間違い

間違い	問題点	代わりにすべきこと
外部キー制約がない	孤立データや壊れたリレーションシップが発生します	必ず FK 制約を定義します
すべてに VARCHAR を使う	型安全性が失われ、ストレージを無駄にします	適切な型（timestamptz、int、uuid）を使います
FK 列にインデックスがない	関連テーブルの結合が遅くなります	すべての FK 列にインデックスを設定します
早すぎる非正規化	効果が測定されていないのに複雑さが増します	正規化した状態から始め、根拠を確認して非正規化します
列を直接削除する	実行中のアプリケーションコードが壊れます	expand-contract パターンを使用します
CONCURRENTLY なしで CREATE INDEX を実行する	インデックス作成中にテーブルがロックされます	本番環境では必ず CONCURRENTLY を使用します
本番環境でスキーマを自動同期する	予測できない破壊的変更が発生します	明示的なマイグレーションファイルを使用します
マイグレーションのロールバック計画がない	デプロイ失敗から復旧できません	すべての up マイグレーションに対応する down マイグレーションを記述します
どの列も NULL 許容にする	データ整合性の保証が失われます	デフォルトは NOT NULL とし、意図的に NULL を許可します

非合理化を防ぐためのガード

• 概念モデルを省略してはいけません — まずエンティティとリレーションシップを把握します
• 推測でインデックスを追加してはいけません — まずクエリパターンを測定します
• パフォーマンス上の問題があるという測定結果なしに、非正規化してはいけません
• ロールバック計画なしにマイグレーションを作成してはいけません
• 調査フェーズを省略してはいけません — クエリパターンとデータ量を把握します
• 本番環境で expand-contract パターンを使わずに列やテーブルを削除してはいけません

ドキュメントの参照（Context7）

最新のドキュメントを参照するには、mcp__context7__resolve-library-id の後に mcp__context7__query-docs を使用します。返されたドキュメントは、記憶している知識より優先されます。

• prisma — スキーマ構文、リレーション、マイグレーション API を確認する場合
• typeorm — エンティティデコレーター、リポジトリパターン、クエリビルダーを確認する場合
• knex — クエリビルダー構文、マイグレーション、シードファイルを確認する場合

---

統合ポイント

スキル	関係
api-design	上流：API リソースはデータベースエンティティに対応します
spec-writing	上流：仕様書でデータ永続化の要件を定義します
planning	下流：スキーマ設計が実装計画に反映されます
test-driven-development	下流：マイグレーションコードを書く前に、マイグレーションテストを作成します
performance-optimization	下流：スキーマの稼働後にクエリを最適化します
reverse-engineering-specs	上流：既存のスキーマの動作をリバースエンジニアリングします
senior-backend	並行：ORM とクエリパターンを担当するバックエンドのスペシャリストです

検証ゲート

スキーマ設計が完了したと宣言する前に、以下を確認します。

1. すべてのエンティティとリレーションシップがモデル化されていることを確認します
2. 正規化が少なくとも第 3 正規形（3NF）であること（または非正規化が正当化されていること）を確認します
3. すべてのクエリパターンと FK 列に対するインデックスが定義されていることを確認します
4. マイグレーション戦略に、すべてのステップのロールバックが含まれていることを確認します
5. ユーザーがスキーマ設計を承認していることを確認します
6. 本番環境向けのコネクションプーリング戦略が定義されていることを確認します

スキルの種類

柔軟 — 概念モデルから物理モデルへの移行、マイグレーションの安全性に関するルール、非正規化の前に測定済みのエビデンスを求める原則を保ちながら、ストレージエンジン、正規化のレベル、インデックス戦略をプロジェクトのニーズに合わせて調整します。