database-schema-design

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

このスキルでは、AI エージェントがアプリケーションの要件から、堅牢で正規化されたリレーショナルデータベーススキーマを設計できます。エージェントはエンティティを分析し、適切なデータ型と制約を持つテーブルを定義して、リレーションシップ（一対一、一対多、多対多）を確立します。第3正規形（3NF）まで正規化し、クエリのパフォーマンス向上のためのインデックスを作成して、実行可能な完全なSQL DDLスクリプトを生成します。

ワークフロー

1. 要件を収集して分析する： ユーザーへのヒアリング、または仕様書の解析により、すべてのエンティティ、その属性、相互のリレーションシップを特定します。カーディナリティ（1:1、1:N、M:N）、必須フィールドと任意フィールドの区別、一意のメールアドレス、正の価格、列挙型のステータスなど、ドメイン固有の制約を明確にします。作業を進める前に、前提を明記します。

2. エンティティとリレーションシップをモデル化する： 要件を論理データモデルに変換します。各エンティティをテーブルとして定義し、適切な主キー（安定性のため、サロゲート整数キーまたはUUIDキーを推奨）を選択して、リレーションシップを割り当てます。一対多の場合は「多」側に外部キーを追加します。多対多の場合は、両方の親テーブルを参照する複合主キーを持つ中間テーブルを作成します。一対一の場合は、共有主キーまたは一意制約付き外部キーを使用します。

3. 正規化を適用する： スキーマを正規形に照らして確認します。すべての非キー列が主キー全体に依存し（第2正規形、2NF）、主キーのみに依存していること（第3正規形、3NF）を確認します。推移的関数従属を含むテーブルは分割します。読み取りの多いクエリパターンを根拠とする場合に限り、戦略的に非正規化し、トレードオフを記録します。

4. 制約とインデックスを定義する： NOT NULL、UNIQUE、CHECK、DEFAULT制約を追加して、データベースレベルでデータ整合性を確保します。外部キー列、WHERE句で使用する列、並べ替えやグループ化に使用する列にインデックスを作成します。複数列のクエリパターンには、複合インデックスを検討します。

5. SQL DDLスクリプトを生成する： すべての列、型、制約、インデックスを含む完全なCREATE TABLE文を生成します。冪等性を確保するため、IF NOT EXISTSを使用します。参照されるテーブルが参照するテーブルより先に作成されるよう、文の順序を調整します。

6. 検証して反復する： 元の要件に照らしてスキーマを確認します。すべてのエンティティが含まれていること、すべてのリレーションシップが正しくモデル化されていること、データ整合性に抜けがないことを検証します。フィードバックに基づいて調整します。

対応テクノロジー

• リレーショナルデータベース： PostgreSQL、MySQL、MariaDB、SQLite、SQL Server、Oracle
• スキーマツール： dbdiagram.io、pgModeler、MySQL Workbench、DBeaver
• マイグレーションフレームワーク： Flyway、Liquibase、Alembic、Prisma Migrate、Knex

使い方

アプリケーションのドメインとデータ要件について説明してください。主なエンティティとその属性、相互のリレーションシップを含めます。エージェントが正規化されたスキーマと完全なDDLを生成します。特定のデータベース（例：PostgreSQLとMySQLの構文）を指定したり、監査用の列や論理削除の追加など、スキーマの変更を依頼したりできます。

例

例1：Eコマースアプリケーションのスキーマ

リクエスト： ユーザー、商品、注文、注文明細を含むEコマースアプリのスキーマを設計してください。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    sku VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
    shipping_address TEXT NOT NULL,
    ordered_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
    UNIQUE (order_id, product_id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

例2：スキーママイグレーションによるレビュー機能の追加

リクエスト： 既存のEコマーススキーマに商品レビュー用のテーブルを追加してください。ユーザーは商品ごとに1件のレビューを、評価と任意のコメントとともに投稿できます。

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, product_id)
);

CREATE INDEX idx_reviews_product_id ON reviews(product_id);
CREATE INDEX idx_reviews_user_id ON reviews(user_id);
CREATE INDEX idx_reviews_rating ON reviews(rating);

これにより、UNIQUE制約によってユーザーが商品ごとに投稿できるレビューを1件に制限し、評価を1～5に限定します。また、ユーザーまたは商品を削除すると、そのレビューも削除されるように連鎖削除を設定します。

ベストプラクティス

• 外部キーは必ず明示的に定義してください。アプリケーションコードだけに頼らず、データベースレベルで参照整合性を確保できます。
• ドメインルールには CHECK 制約を使用してください。正の価格、有効なステータスの列挙値、評価の範囲などを設定し、無効なデータがデータベースに登録されるのを防ぎます。
• すべての外部キー列にインデックスを設定してください。これらの列は JOIN や検索で使用されます。外部キーにインデックスがないと、カスケード操作の際にテーブル全体のスキャンが発生します。
• 主キーには自然キーではなくサロゲートキーを優先してください。メールアドレスや SKU などの自然キーの値が変更されたときに生じる問題を避けられます。
• すべてのテーブルに created_at と updated_at のタイムスタンプを追加してください。監査やデバッグに役立ちます。整合性を確保するため、データベースのデフォルト値を使用してください。
• パフォーマンスのために正規形から逸脱する場合は、非正規化の判断を明示的に文書化してください。将来の開発者がトレードオフを理解できるようにします。

エッジケース

• 循環する外部キー依存関係： 2 つのテーブルが互いを参照する場合、まず一方のテーブルを外部キーなしで作成し、次にもう一方のテーブルを作成してから、ALTER TABLE で不足している外部キーを追加します。PostgreSQL では、トランザクション内での循環する挿入を処理するために遅延制約を使用してください。
• 自己参照関係： サブカテゴリーを持つカテゴリーなどの階層データには、同じテーブルを参照する、NULL 許容の parent_id 列を使用してください。ある行が自身を親として指定するのを防ぐため、CHECK 制約またはトリガーを追加してください。
• ポリモーフィック関連付け： 複数のテーブルから共有エンティティを参照する必要がある場合（投稿と製品の両方へのコメントなど）は、汎用的な entity_type + entity_id パターンではなく、1 つだけが NULL 以外になることを CHECK 制約で保証した複数の外部キー列を優先してください。汎用的なパターンでは参照整合性を強制できません。
• 大きなテキストデータやバイナリデータ： BLOB と大きなテキストは、外部キーで関連付けた別のテーブルに保存してください。メインテーブルの行サイズを小さく保ち、大きなデータを必要としないクエリの速度低下を防げます。
• マルチテナントスキーマ： tenant_id 列を持つ共有テーブル（よりシンプル）と、テナントごとに分けたスキーマ（より強固な分離）のどちらを採用するか決めてください。すべてのインデックスに tenant_id を含め、行レベルセキュリティポリシーで適用してください。