database-schema-design

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

指定されたドメインについて、完全なデータベーススキーマ設計ドキュメントを作成します。スキーマドキュメントは単なるテーブルの一覧ではありません。何をモデル化したか、エンティティをどのように関連付けたか、スキーマをどのクエリ向けに最適化したか、どのようなトレードオフを選んだかを記録するものです。

優れたスキーマ設計ドキュメントがあれば、エンジニアはデータモデルを理解し、正しくクエリし、安全に拡張して、問題を起こさずにマイグレーションを作成できます。

必要な入力

まだ提示されていない場合は、以下の情報を尋ねてください。

• ドメインの説明 — システムの機能、およびモデル化するビジネスオブジェクト
• エンティティとリレーションシップ — ドメインの主要な要素とその関係（例：「ユーザーは複数の注文を持つ。注文は複数の注文明細を持つ。注文明細は商品を参照する」）
• 想定されるクエリパターン — 最も重要な読み取りおよび書き込みクエリ（例：「ユーザーのすべての注文を日付順に取得する」「SKU から商品を検索する」）
• データベースエンジン — PostgreSQL、MySQL、SQLite、CockroachDB など。DDL 構文や利用可能な型に影響します
• 想定されるデータ量 — おおよその行数、増加率、パーティショニングの必要性
• 制約 — 遵守すべき既存の規約、命名規則、マイグレーション上の制約

出力形式

---

データベーススキーマ設計：[ドメイン／サービス名]

サービス： [名前] | チーム： [チーム名] 作成者： [名前] | レビュー担当者： [名前] 日付： [日付] | データベースエンジン： [PostgreSQL X.X／MySQL X.X／など] ステータス： [ドラフト／レビュー済み／承認済み]

---

1. 概要

[モデル化するドメイン、このスキーマの対象範囲、および主要な設計方針を説明する2～3文（例：「このスキーマは書き込みの単純さよりも、顧客向け API の読み取りパフォーマンスを優先します」または「将来的なマルチテナントへの移行を想定して設計されています」）]

対象範囲：

• [エンティティまたはサブシステム]
• [エンティティまたはサブシステム]

対象外：

• [例：分析／レポート用テーブル — 別のスキーマで管理]
• [例：監査ログテーブル — 別の設計ドキュメントで扱う]

---

2. エンティティ・リレーションシップ図

┌───────────────────┐         ┌───────────────────────┐
│      users        │         │       organisations    │
│─────────────────  │         │─────────────────────── │
│ id (PK)           │    ┌───▶│ id (PK)                │
│ org_id (FK)  ─────┼────┘    │ name                   │
│ email             │         │ plan                   │
│ display_name      │         │ created_at             │
│ created_at        │         └───────────────────────┘
│ updated_at        │
└─────────┬─────────┘
          │ 1
          │
          │ N
┌─────────▼─────────┐         ┌───────────────────────┐
│      [table_a]    │         │      [table_b]         │
│─────────────────  │         │─────────────────────── │
│ id (PK)           │    N    │ id (PK)                │
│ user_id (FK) ─────┼────────▶│ [table_a]_id (FK)      │
│ [field]           │    │    │ [field]                │
│ [field]           │    │    │ [field]                │
│ created_at        │         │ created_at             │
└───────────────────┘         └───────────────────────┘

リレーションシップの概要：

エンティティ A	リレーションシップ	エンティティ B	備考
organisations	複数を持つ	users	1つの組織は複数のユーザーを持てます
users	複数を持つ	[table_a]	ユーザーの削除時に論理削除
[table_a]	複数を持つ	[table_b]	カスケード削除
[table_b]	に属する	[table_a]	NULL 不可の FK
[table_c]	多対多（[join_table] 経由）	[table_d]	メタデータを持つ結合テーブル

---

3. テーブル定義

organisations

[このテーブルに格納する内容と、ドメインにおける役割を説明する1文。]

CREATE TABLE organisations (
    id          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(255)    NOT NULL,
    slug        VARCHAR(100)    NOT NULL UNIQUE,
    plan        VARCHAR(50)     NOT NULL DEFAULT 'free'
                                CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings    JSONB           NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT now()
);

カラム	型	NULL 可	デフォルト	備考
id	UUID	いいえ	gen_random_uuid()	サロゲート PK — 分散利用では serial より UUID を推奨
name	VARCHAR(255)	いいえ	—	表示名。ユニークではありません
slug	VARCHAR(100)	いいえ	—	URL で安全に使用できる識別子。すべての組織で一意
plan	VARCHAR(50)	いいえ	'free'	CHECK により既知の値に制限
settings	JSONB	いいえ	{}	柔軟な設定用。クエリ対象のフィールドには使用しないでください
created_at	TIMESTAMPTZ	いいえ	now()	TIMESTAMP ではなく、必ず TIMESTAMPTZ を使用
updated_at	TIMESTAMPTZ	いいえ	now()	トリガーで更新（下記参照）

---

users

[このテーブルに格納する内容を説明する1文。]

CREATE TABLE users (
    id              UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id          UUID            NOT NULL REFERENCES organisations(id)
                                    ON DELETE RESTRICT,
    email           VARCHAR(254)    NOT NULL,
    display_name    VARCHAR(255)    NOT NULL DEFAULT '',
    role            VARCHAR(50)     NOT NULL DEFAULT 'member'
                                    CHECK (role IN ('owner', 'admin', 'member', 'viewer')),
    email_verified  BOOLEAN         NOT NULL DEFAULT false,
    deleted_at      TIMESTAMPTZ     NULL,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT now(),

    CONSTRAINT users_email_org_unique UNIQUE (email, org_id)
);

カラム	型	NULL 可	デフォルト	備考
id	UUID	いいえ	gen_random_uuid()	—
org_id	UUID	いいえ	—	organisations への FK。RESTRICT により孤立を防止
email	VARCHAR(254)	いいえ	—	RFC 5321 の最大長。組織ごとに一意（全体では一意ではありません）
role	VARCHAR(50)	いいえ	'member'	アプリケーションレベルの RBAC
deleted_at	TIMESTAMPTZ	はい	NULL	論理削除。NULL = 有効

論理削除ポリシー： deleted_at IS NOT NULL の行は削除済みとみなします。削除済みのレコードを明示的に取得する場合を除き、すべてのアプリケーションクエリで WHERE deleted_at IS NULL による絞り込みを必ず行ってください。これを徹底するために、ビューまたは ORM のスコープを使用してください。

[table_a]

[このテーブルがモデル化する内容の説明。]

CREATE TABLE [table_a] (
    id          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID            NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    [field_1]   VARCHAR(255)    NOT NULL,
    [field_2]   TEXT            NULL,
    [field_3]   INTEGER         NOT NULL DEFAULT 0 CHECK ([field_3] >= 0),
    status      VARCHAR(50)     NOT NULL DEFAULT 'pending'
                                CHECK (status IN ('pending', 'active', 'archived')),
    metadata    JSONB           NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ     NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ     NOT NULL DEFAULT now()
);

列	型	NULL 許容	備考
user_id	UUID	いいえ	CASCADE 削除 — ユーザーが削除されると、そのユーザーの [table_a] の行も削除されます
[field_1]	VARCHAR(255)	いいえ	[長さ制約の理由]
status	VARCHAR(50)	いいえ	ステートマシン：pending → active → archived（ほかの遷移はありません）
metadata	JSONB	いいえ	[保存される内容と、型付きの列にしない理由]

---

[join_table] (多対多)

[このテーブルが表す関係の説明。]

CREATE TABLE [join_table] (
    [table_c]_id    UUID        NOT NULL REFERENCES [table_c](id) ON DELETE CASCADE,
    [table_d]_id    UUID        NOT NULL REFERENCES [table_d](id) ON DELETE CASCADE,
    granted_by      UUID        NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    granted_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

    PRIMARY KEY ([table_c]_id, [table_d]_id)
);

複合主キーを使用する理由： [table_c]_id + [table_d]_id の組み合わせは自然キーです。各関連付けは一意であり、別個のインデックスを作成しなくても、主キーが一意性制約を兼ねます。

---

4. インデックス戦略

各テーブルについて、作成するインデックスとその理由を定義します。対象とするクエリも記載してください。

テーブル	インデックス名	列	種類	対象クエリ	備考
users	users_org_id_idx	(org_id)	B-tree	SELECT * FROM users WHERE org_id = $1	外部キーの検索用。JOIN のパフォーマンスに必要です
users	users_email_lower_idx	(lower(email))	B-tree（関数）	WHERE lower(email) = lower($1)	大文字と小文字を区別しないメールアドレス検索
users	users_active_by_org_idx	(org_id, created_at DESC)	B-tree	WHERE org_id = $1 AND deleted_at IS NULL ORDER BY created_at DESC	部分インデックスの候補です（下記参照）
[table_a]	[table_a]_user_id_status_idx	(user_id, status)	B-tree	WHERE user_id = $1 AND status = 'active'	複合インデックス — 順序が重要です
[table_a]	[table_a]_metadata_gin_idx	metadata	GIN	WHERE metadata @> '{"key": "value"}'	JSONB の検索頻度が高い場合にのみ追加します

部分インデックス（PostgreSQL）：

-- Index only active (non-deleted) users — dramatically smaller for soft-delete tables
CREATE INDEX users_active_email_idx
    ON users (email, org_id)
    WHERE deleted_at IS NULL;

-- Index only pending items — avoids indexing the majority of rows
CREATE INDEX [table_a]_pending_idx
    ON [table_a] (user_id, created_at)
    WHERE status = 'pending';

適用するインデックス設計の原則：

• JOIN 条件に使用する外部キーには、必ずインデックスを設定します
• 複合インデックスは選択性の高い列を先頭にします
• 大文字と小文字を区別しない検索には関数インデックスを使用します
• GIN インデックスは、JSONB の包含クエリの頻度が高い場合にのみ使用します
• 大規模なテーブルで、ステータスによる絞り込みを行うクエリには部分インデックスを使用します

---

5. アクセスパターン分析

このスキーマが主に対象とするクエリを記載します。それぞれについて、クエリ、使用するインデックス、注意事項を示してください。

AP-1: 組織内のすべてのアクティブユーザーを取得する（ページネーションあり）

頻度： 非常に高い — ダッシュボードの読み込みごとに呼び出されます クエリ：

SELECT id, email, display_name, role, created_at
FROM users
WHERE org_id = $1
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 50 OFFSET $2;

使用するインデックス： users_active_by_org_idx (org_id, created_at DESC) 備考： 大規模なデータではキーベースのページネーション（WHERE created_at < $cursor）を使用します。OFFSET は約 10k 行を超えるとパフォーマンスが低下します。

---

AP-2: メールアドレスでユーザーを検索する（大文字と小文字を区別しない）

頻度： 高い — 認証の試行ごとに実行されます クエリ：

SELECT id, org_id, role, email_verified
FROM users
WHERE lower(email) = lower($1)
  AND deleted_at IS NULL;

使用するインデックス： users_email_lower_idx 備考： 同じメールアドレスが複数の組織に存在する場合、複数の行が返されます。アプリケーション側で組織のコンテキストに基づいて解決します。

---

AP-3: ステータス別にユーザーの [table_a] アイテムを取得する

頻度： 高い クエリ：

SELECT *
FROM [table_a]
WHERE user_id = $1
  AND status = $2
ORDER BY created_at DESC
LIMIT 25;

使用するインデックス： [table_a]_user_id_status_idx 備考： 複合インデックスが両方の絞り込み列をカバーします。user_id の方が選択性が高いため、インデックスではステータスの絞り込み列を2番目にする必要があります。

---

AP-4: 必要に応じてアクセスパターンを追加します

---

6. 正規化の判断

正規化または非正規化を行う意図的な判断と、その理由を記録します。

判断	方針	理由
[e.g. Organisation name on users table?]	非正規化しない — 常に organisations と結合します	古くなったコピーを避けます。組織名が変わることはまれで、結合のコストも低いためです
[e.g. Status history]	このテーブルには含めない — 必要であれば別の [table_a]_status_history を作成します	クエリの99％では現在のステータスだけで十分です。履歴は監査用であり、アプリケーションデータではありません
[e.g. JSONB settings column on organisations]	JSONB に非正規化します	設定はまとめて読み取られ、フィールド単位でクエリされることはありません。スキーマの変更にマイグレーションは不要です
[e.g. Computed aggregate counts]	保存しない — クエリ実行時に計算します	件数は少量です。カウンター列を維持するには慎重なロック制御が必要です。インデックスを使って SELECT COUNT(*) を実行します

---

7. トリガーと自動化

-- Automatically update updated_at on any row modification
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER [table_a]_updated_at
    BEFORE UPDATE ON [table_a]
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

---

8. マイグレーションの注意事項

このスキーマを既存のシステムに導入する場合は、マイグレーションの方針を記載します。

手順	説明	下位互換性	リスク
1	organisations テーブルを作成します	あり — 追加のみ	低
2	users テーブルを作成します	あり — 追加のみ	低
3	既存のユーザーに org_id をバックフィルします	二重書き込み期間が必要です	中
4	org_id に NOT NULL 制約を追加します	バックフィルが100％完了している必要があります	中
5	非推奨の列を削除します	先にアプリケーションコードを更新する必要があります	アプリケーションのデプロイ後は低

バックフィル戦略： [既存データの処理方法を記述します — バッチサイズ、レート制限、検証クエリ]

ロールバック： 各マイグレーション手順は個別に元に戻せるようにします。ロールバック手順のテンプレート全体については、[database-migration-plan skill] を参照してください。

---

品質チェック

• すべてのテーブルに主キーと created_at 列がある — 行の挿入順序による暗黙の並べ替えに依存しない
• すべての外部キーに対応するインデックスがある — 結合時に全表スキャンを引き起こす外部キーのインデックス漏れがない
• TIMESTAMP ではなく、すべて TIMESTAMPTZ 列を使う — タイムゾーンの扱いを明示する
• 論理削除するテーブルでは、その規約とフィルターを適用する場所（ORM のスコープ、ビュー、またはクエリの標準）を記載する
• 設計内のすべてのアクセスパターンに対応するインデックスがあるか、全表スキャンを許容できる旨を明記する
• JSONB 列を使う理由が明確である — クエリ対象のフィールドに対する適切なスキーマ設計の代わりに使わない
• 正規化の判断は、判断だけでなく理由も記録する
• グリーンフィールドのスキーマではなくスキーマ変更の場合は、マイグレーションの注意事項で既存データを扱う

アンチパターン

• クエリ対象となるフィールドについて、適切なリレーショナルスキーマ設計の代わりに JSONB 列を使わない
• 憶測でインデックスを追加しない — すべてのインデックスに具体的なアクセスパターンに基づく根拠が必要です
• タイムゾーンへの対応を省略しない — TIMESTAMPTZ を使い、通常の TIMESTAMP は使わない
• 正規化の判断を記録せずに設計しない — 将来の保守担当者に必要なのは構造だけでなく、その理由です
• アクセスパターンのセクションを省略しない — クエリパターンのないスキーマは、正しさを評価できません

トリガーフレーズの例

• 「データベースを設計して。」
• 「既存のスキーマを文書化して。」
• 「エンティティとリレーションシップをモデル化して。」
• 「テーブル構造を定義して。」
• 「インデックス戦略を計画して。」