relational-data-modeling

リレーショナルデータモデリング

リレーショナルスキーマが迫る選択肢と、データベースが可能な範囲を強制し、アプリケーションには必要なことだけを残すための決め方を説明します。各ルールでは、正すべき誤った既定の選択肢を示します。十分な知識を持つモデルがすでに正しく扱えていることについては、ルールを設けていません。

例では PostgreSQL 18 を使用しており、このスキルに含まれるすべての DDL 文は、制約が意図した不正なデータを拒否することを確認するため、失敗するケースも含めて 18.4 で実行済みです。ルールのおよそ 3 分の 1 は、MySQL と SQLite にはない仕組み（EXCLUDE、部分ユニークインデックス、範囲型、WITHOUT OVERLAPS、遅延可能な制約、NOT VALID）に依存しており、その場合は明記しています。判断のルールは、どのリレーショナルエンジンにも応用できます。

適用する場面

次のような場合に、このスキルを使用します。

• CREATE TABLE／ALTER TABLE、マイグレーション、ORM のスキーマ定義（Prisma、Drizzle、Django モデル、ActiveRecord、Ecto、SQLAlchemy）を作成またはレビューするとき
• エンティティ・リレーションシップモデルを設計するとき、または行が「何であるか」を定義するとき — 主キーの選択を後から変更するコストが高くなるタイミングです
• ユーザーが「これは 1 つのテーブルにするべきですか、それとも 2 つですか」「多対多はどうモデル化すればよいですか」「スキーマが不正なデータを許しています」「履歴が必要です」「マルチテナント化します」「データベースで強制できますか」と言うとき
• バグの原因が、不正な状態を許していたスキーマだと判明したとき — ユニーク制約で検出すべき重複、外部キーで防ぐべき孤立行、重複する予約、デフォルトとしてフラグが立った 2 つの行など
• すでにデータとトラフィックがあるテーブルに制約を追加するとき
• ORM やスキャフォールディングツールが生成したスキーマをレビューするとき — ポリモーフィックな関連、自己参照型の代理キー、包括的な論理削除フラグが持ち込まれるのは、このような場面です

このスキルは、次の用途には適しません。

• クエリのチューニング、実行計画、読み取り処理用のインデックス選定 — 対象とするのは、制約と外部キーに必要なインデックスだけです
• コネクションプーリング、レプリケーション、運用上のチューニング
• ここで述べるトレードオフが当てはまらない、非リレーショナルストア

ルールのカテゴリ

#	カテゴリ	プレフィックス	対象
1	識別とキー	key-	行が何であるか。すべての外部キーが依存する選択です
2	リレーションシップと多重度	rel-	参照を単なる意図ではなく、データベースに宣言できる形で保つ方法です
3	モデルとしての制約	cons-	どの仕組みで、どの不変条件を実際に保持できるかです
4	型とドメイン	type-	慣例ではなく意味に基づいて選ぶ、最も低コストな制約です
5	派生データとエンコードされたデータ	norm-	意図的なコピーに伴うコストと、データベースから内部を確認できないデータです
6	時間、履歴、ライフサイクル	time-	世界が変化したときに行に何が起きるかです

クイックリファレンス

1. 識別とキー

• key-primary-key-is-real-identity — 代理キーは自然キーを隠しますが、テーブルには依然として UNIQUE が必要です
• key-identity-not-serial — serial は旧式です。GENERATED ALWAYS AS IDENTITY を使うと、挿入を失敗させる同期ずれを防げます
• key-uuidv7-for-client-generated — v4 は B-tree への挿入を毎回ランダムな位置に行います。uuidv7() を使うのは、UUID を使う正当な理由がある場合だけにしてください
• key-propagate-scoping-key — テナントを子のキーに含めることで、テナントをまたぐ参照を書き込めないようにします
• key-partition-axis-binds-the-key — トラフィックに応じて増大するテーブルでは、パーティションの軸は後から行うチューニングではなく、キーに関わる選択です

2. リレーションシップとカーディナリティ

• rel-no-polymorphic-fk — type + id では参照整合性を宣言できません。排他的アークまたはスーパータイプを使います
• rel-relationship-as-entity — 事実とライフサイクルがあるなら名前を付け、なければキー付きリンクテーブルにします
• rel-choose-referential-action — CASCADE は構成要素であることを意味します。台帳ではデータ損失につながります
• rel-index-referencing-side — PostgreSQL は子側の列にインデックスを作成しませんが、MySQL は作成します。この習慣はほかの環境にも活かせます
• rel-one-to-one-is-one-table — テーブルを分割すると結合が必要になり、ストレージやアクセス制御が異ならない限り、何も強制できません
• rel-subtypes-through-the-key — UNIQUE (id, kind) と固定された識別子の組み合わせにより、バリアントが互いに排他的であることを保証できます

3. モデルとしての制約

• cons-not-null-by-default — NULL と評価される CHECK は通過し、UNIQUE の制約も機能しなくなります
• cons-exclude-for-non-overlap — アプリケーション側の重複チェックは、読み取り後の書き込みとの間で競合します。EXCLUDE ならその問題はありません
• cons-partial-unique-index — 「有効な X は最大 1 つ」はトリガーではなく、UNIQUE ... WHERE で実現します
• cons-check-is-single-row — クエリを関数で包むと、CHECK がいつの間にか機能しなくなることがあります
• cons-deferrable-for-cycles — 列を永久に NULL 許容にするのではなく、COMMIT まで制約の検証を遅らせます
• cons-not-valid-then-validate — 稼働中のテーブルに制約を追加できる仕組みです

4. 型とドメイン

• type-timestamptz-for-instants — timestamp はタイムゾーンのない壁時計時刻を保存します。誕生日は特定の時点ではありません
• type-text-over-varchar — varchar(255) は MySQL 固有の名残です。長さ制限には CHECK またはドメインを使います
• type-numeric-with-currency — 浮動小数点数には誤差が生じ、money はロケールに依存します。また、通貨が暗黙に決まることはありません
• type-range-not-two-columns — ばらばらの 2 列では逆転した行を許してしまい、あらゆる時間的制約を妨げます
• type-enum-lookup-or-check — 既定では CHECK を使い、値に属性がある場合はルックアップテーブルを使います。enum は後戻りできません

5. 派生データとエンコードされたデータ

• norm-derived-needs-a-mechanism — 2 つのコピーを同じ値に保てる制約はありません。仕組みを明示するか、コピーしないようにします
• norm-encoded-composite-values — 構成要素から参照コードを生成します。解析済みの列には制約を設定できません
• norm-jsonb-for-open-shapes — キーを列挙でき、そのうち 1 つで絞り込めるなら、それらは列にします

6. 時間、履歴、ライフサイクル

• time-events-not-in-place-updates — 残高を直接更新すると監査証跡が失われ、アカウントへの処理が直列化されます
• time-soft-delete-breaks-constraints — deleted_at は一意制約と外部キーを気付かないうちに無効にします
• time-validity-as-a-range — WITHOUT OVERLAPS を使うと、同時に存在する 2 つのバージョンを表現できなくなります
• time-valid-vs-transaction-time — 過去の認識を再現する必要がある場合に限り、2 つの時間軸を使います。通常、その必要はありません

使用方法

判断が必要になったときに、該当するリファレンスファイルを読みます。上のクイックリファレンスで参照先を確認できます。各ルールでは、正すべき誤ったデフォルトとその理由を述べたうえで、標準的な例を示します。

カテゴリをまたいで適用できる、覚えておくと役立つ経験則が2つあります。

1. 列を追加する前に、2つの行が同じ行であると判断する基準を明確にします。 その基準は、テーブルに課すべき制約です。
2. アプリケーションコードにバリデーションを書く前に、代わりにどの制約で保証できるかを考えます。 「どれもない」という答えなら、それを明確に把握しておく価値があります。不変条件の形と対応する仕組みの一覧は、cons-check-is-single-row を参照してください。

• セクションの定義 — カテゴリの構成と順序
• ルールのテンプレート — 新しいルールの追加用
• AGENTS.md — すべてのルールを対象に自動生成される目次

リファレンスファイル

ファイル	説明
references/_sections.md	カテゴリの定義と順序
assets/templates/_template.md	新しいルール用のテンプレート
metadata.json	バージョンとソースの参照情報