database-design

name	database-design
description	リレーショナルデータベースのスキーマ設計とマイグレーション作成。 正規化、インデックス設計、パフォーマンス考慮。 DB設計、テーブル作成、マイグレーション作成時に使用。
version	1.0.0

Database Design Skill

Overview

スケーラブルで保守しやすいデータベース設計を支援します。

Design Principles

1. 命名規則

• テーブル名: スネークケース、複数形 (users, order_items)
• カラム名: スネークケース (created_at, user_id)
• 外部キー: {参照テーブル単数形}_id (user_id, order_id)
• インデックス: idx_{テーブル}_{カラム} (idx_users_email)

2. 必須カラム

id            -- 主キー（UUID or BIGINT AUTO_INCREMENT）
created_at    -- 作成日時
updated_at    -- 更新日時

3. 正規化ガイド

正規形	ルール
1NF	繰り返しグループなし、原子値のみ
2NF	部分関数従属なし
3NF	推移的関数従属なし

※パフォーマンスのため意図的な非正規化はOK（理由を文書化）

4. インデックス戦略

• WHERE句で頻繁に使用するカラム
• JOIN条件のカラム
• ORDER BY句のカラム
• 外部キー
• 複合インデックス: カーディナリティ高い順

5. 制約

-- NOT NULL: 必須項目
-- UNIQUE: 一意制約
-- FOREIGN KEY: 参照整合性
-- CHECK: 値の範囲制限
-- DEFAULT: デフォルト値

Schema Template

-- ユーザーテーブル例
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user' 
        CHECK (role IN ('user', 'admin', 'moderator')),
    email_verified_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP  -- ソフトデリート用
);

-- インデックス
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role) WHERE deleted_at IS NULL;

-- 更新日時自動更新（PostgreSQL）
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

Migration Best Practices

1. マイグレーションルール

• 1マイグレーション = 1つの変更
• 必ずロールバック可能に
• 本番データを考慮

2. 安全な変更

-- ✅ 安全: カラム追加（NULL許容 or デフォルト値あり）
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- ✅ 安全: インデックス追加（CONCURRENTLY）
CREATE INDEX CONCURRENTLY idx_users_phone ON users(phone);

-- ⚠️ 注意: カラム削除（先にコードから参照を削除）
ALTER TABLE users DROP COLUMN old_column;

-- ⚠️ 注意: 型変更（データ変換が必要）
ALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::INTEGER;

3. 大量データ対応

-- バッチ処理でデータ移行
DO $$
DECLARE
    batch_size INT := 1000;
    affected INT;
BEGIN
    LOOP
        UPDATE users 
        SET new_column = old_column 
        WHERE new_column IS NULL 
        LIMIT batch_size;
        
        GET DIAGNOSTICS affected = ROW_COUNT;
        EXIT WHEN affected = 0;
        
        COMMIT;
        PERFORM pg_sleep(0.1);  -- 負荷軽減
    END LOOP;
END $$;

ER Diagram Format (Mermaid)

erDiagram
    users ||--o{ orders : places
    users {
        uuid id PK
        string email UK
        string name
        timestamp created_at
    }
    orders {
        uuid id PK
        uuid user_id FK
        decimal total
        string status
        timestamp created_at
    }