schema-designer

スキーマ設計

データベーススキーマの設計、テーブルの作成、データの関係のモデル化をユーザーに支援します。

使用するタイミング

ユーザーが次のように依頼したときに有効にします。

• 「注文を保存するテーブルを作成して」
• 「ブログのスキーマを設計して」
• 「ユーザー設定を追跡するカラムを追加して」
• 「この関係はどのようにモデル化すればよいですか？」

ワークフロー

1. 要件を把握する

確認の質問をします。

• どのようなデータを保存する必要がありますか？
• エンティティ間にはどのような関係がありますか？
• よく実行されるクエリは何ですか？
• 想定されるデータ量はどのくらいですか？

2. 既存のスキーマを確認する

whodb_tables() → See what already exists
whodb_columns(table="related_table") → Understand existing structure

3. スキーマを設計する

データベース設計の原則に従います。

• 冗長性を減らすために正規化する
• 適切なデータ型を使用する
• 主キーを定義する
• 外部キーの関係を確立する
• よく使われるクエリにインデックスを追加する

4. DDL を生成する

説明を添えて CREATE TABLE 文を提示します。

データ型のガイドライン

識別子

使用例	PostgreSQL	MySQL	SQLite
自動採番 ID	SERIAL / BIGSERIAL	INT AUTO_INCREMENT	INTEGER PRIMARY KEY
UUID	UUID	CHAR(36)	TEXT

テキスト

使用例	PostgreSQL	MySQL	SQLite
短いテキスト（<255）	VARCHAR(n)	VARCHAR(n)	TEXT
長いテキスト	TEXT	TEXT	TEXT
固定長	CHAR(n)	CHAR(n)	TEXT

数値

使用例	PostgreSQL	MySQL	SQLite
整数	INTEGER	INT	INTEGER
大きな整数	BIGINT	BIGINT	INTEGER
小数（通貨）	NUMERIC(10,2)	DECIMAL(10,2)	REAL
浮動小数点数	REAL	FLOAT	REAL

日付

使用例	PostgreSQL	MySQL	SQLite
日付のみ	DATE	DATE	TEXT
タイムスタンプ	TIMESTAMP	DATETIME	TEXT
タイムゾーン付き	TIMESTAMPTZ	TIMESTAMP	TEXT

ブール値

PostgreSQL	MySQL	SQLite
BOOLEAN	TINYINT(1)	INTEGER

よく使われるパターン

ユーザーテーブル

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

1 対多（注文 → 注文明細）

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total NUMERIC(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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),
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price NUMERIC(10,2) NOT NULL
);

CREATE INDEX idx_order_items_order ON order_items(order_id);

多対多（ユーザー ↔ ロール）

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE user_roles (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

論理削除パターン

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    deleted_at TIMESTAMP NULL,  -- NULL = not deleted
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Query active posts
SELECT * FROM posts WHERE deleted_at IS NULL;

監査証跡パターン

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id INTEGER NOT NULL,
    action VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE
    old_values JSONB,
    new_values JSONB,
    user_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);

ベストプラクティス

1. 必ず PRIMARY KEY を定義する - すべてのテーブルに必要です
2. 外部キーを使用する - 参照整合性を確保します
3. NOT NULL を追加する - カラムが本当に任意である場合を除きます
4. インデックスを作成する - 外部キーと頻繁に検索されるカラムに作成します
5. 適切な型を使用する - 数値を文字列として保存しないでください
6. タイムスタンプを追加する - created_at と updated_at はほとんどの場合に役立ちます
7. 一貫した命名にする - userId や UserID ではなく user_id を使います
8. 予約語を避ける - カラム名に order、user、group を使わないでください

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

既存のテーブルを変更する場合：

-- Safe: Adding nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Safe: Adding column with default
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT true;

-- Caution: Adding NOT NULL (requires default or backfill)
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- Caution: Dropping column (data loss)
ALTER TABLE users DROP COLUMN old_column;

-- Caution: Changing type (may fail on existing data)
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;