Database Schema Designer

本番環境に対応したデータベーススキーマを、ベストプラクティスを組み込んで設計します。

---

クイックスタート

データモデルを説明するだけです:

design a schema for an e-commerce platform with users, products, orders

次のような完全な SQL スキーマが得られます:

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL,
  INDEX idx_orders_user (user_id)
);

リクエストに含めるべき内容:

• エンティティ (ユーザー、製品、注文など)
• 主要な関係性 (ユーザーは注文を持つ、注文はアイテムを持つなど)
• スケールのヒント (高トラフィック、数百万レコードなど)
• データベース選択 (SQL/NoSQL) - 指定されない場合はデフォルトで SQL

---

トリガー

トリガー	例
design schema	"design a schema for user authentication"
database design	"database design for multi-tenant SaaS"
create tables	"create tables for a blog system"
schema for	"schema for inventory management"
model data	"model data for real-time analytics"
I need a database	"I need a database for tracking orders"
design NoSQL	"design NoSQL schema for product catalog"

---

主要用語

用語	定義
Normalization (正規化)	冗長性を削減するためのデータ編成 (1NF → 2NF → 3NF)
3NF	第三正規形 - カラム間の推移的依存性がない
OLTP	オンライントランザクション処理 - 書き込み量が多く、正規化が必要
OLAP	オンライン分析処理 - 読み込み量が多く、非正規化から利益を得る
Foreign Key (FK)	別のテーブルの主キーを参照するカラム
Index (インデックス)	クエリを高速化するデータ構造 (その代わり書き込みが遅くなる)
Access Pattern (アクセスパターン)	アプリがデータを読み書きする方法 (クエリ、結合、フィルタリングなど)
Denormalization (非正規化)	読み込みを高速化するため、意図的にデータを複製

---

クイックリファレンス

タスク	アプローチ	主要な考慮事項
新規スキーマ	まず 3NF に正規化	UI より領域モデリング
SQL vs NoSQL	アクセスパターンが決定	読み書き比が重要
主キー	INT または UUID	UUID は分散システムに向く
外部キー	常に制約を設定	ON DELETE の戦略が重要
インデックス	FK + WHERE カラム	カラム順序が重要
マイグレーション	常に可逆的に	まず後方互換性

---

プロセス概要

データ要件
    |
    v
+-----------------------------------------------------+
| フェーズ 1: 分析                                    |
| * エンティティと関係性を特定                         |
| * アクセスパターンを決定 (読み込み vs 書き込み)      |
| * 要件に基づいて SQL または NoSQL を選択             |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| フェーズ 2: 設計                                    |
| * 3NF に正規化 (SQL) または埋め込み/参照 (NoSQL)    |
| * 主キーと外部キーを定義                             |
| * 適切なデータ型を選択                               |
| * 制約を追加 (UNIQUE, CHECK, NOT NULL)              |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| フェーズ 3: 最適化                                  |
| * インデックス戦略を計画                             |
| * 読み込み集約的なクエリでの非正規化を検討           |
| * タイムスタンプを追加 (created_at, updated_at)    |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| フェーズ 4: マイグレーション                        |
| * マイグレーションスクリプトを生成 (up + down)      |
| * 後方互換性を確保                                   |
| * ゼロダウンタイムデプロイメントを計画               |
+-----------------------------------------------------+
    |
    v
本番対応スキーマ

---

コマンド

コマンド	使用時期	アクション
design schema for {domain}	最初からスタート	完全なスキーマ生成
normalize {table}	既存テーブルの修正	正規化ルールを適用
add indexes for {table}	パフォーマンス問題	インデックス戦略を生成
migration for {change}	スキーマ進化	可逆的なマイグレーションを作成
review schema	コードレビュー	既存スキーマを監査

ワークフロー: design schema から開始 → normalize で反復 → add indexes で最適化 → migration で進化

---

コア原則

原則	なぜ	実装
領域モデリング	UI は変わるが領域は変わらない	エンティティ名が業務概念を反映
データ完全性を優先	破損の修復は高くつく	データベースレベルで制約を設定
アクセスパターンに最適化	両方に最適化できない	OLTP: 正規化、OLAP: 非正規化
スケール計画	後付けは大変	インデックス戦略 + パーティション計画

---

アンチパターン

避けるべき	なぜ	代わりに
どこにでも VARCHAR(255)	ストレージの浪費、意図の隠蔽	フィールドごとに適切に設定
金額に FLOAT を使用	丸め誤差	DECIMAL(10,2)
FK 制約の欠落	孤立したデータ	常に外部キーを定義
FK のインデックスがない	JOIN が遅い	すべての外部キーにインデックスを設定
日付を文字列で保存	比較/ソートができない	DATE、TIMESTAMP 型を使用
クエリで SELECT *	不要なデータを取得	明示的なカラムリスト
可逆的でないマイグレーション	ロールバックできない	常に DOWN マイグレーションを作成
デフォルト値のない NOT NULL を追加	既存行を破損	NULL 許可、バックフィル、制約化

---

検証チェックリスト

スキーマ設計後:

• すべてのテーブルに主キーがある
• すべての関係性に外部キー制約がある
• すべての FK に対して ON DELETE 戦略が定義されている
• すべての外部キーにインデックスがある
• 頻繁にクエリされるカラムにインデックスがある
• 適切なデータ型を使用している (金額に DECIMAL など)
• 必須フィールドに NOT NULL がある
• 必要に応じて UNIQUE 制約がある
• 検証用の CHECK 制約がある
• created_at と updated_at タイムスタンプがある
• マイグレーションスクリプトが可逆的である
• 本番データを使用してステージングでテスト済み

---

<details> <summary><strong>詳細解説: 正規化 (SQL)</strong></summary>

正規形

形式	ルール	違反の例
1NF	アトミック値、繰り返しグループなし	product_ids = '1,2,3'
2NF	1NF + 部分的依存性なし	order_items の customer_name
3NF	2NF + 推移的依存性なし	postal_code から派生した country

第一正規形 (1NF)

-- 悪い例: カラムに複数の値
CREATE TABLE orders (
  id INT PRIMARY KEY,
  product_ids VARCHAR(255)  -- '101,102,103'
);

-- 良い例: アイテムの個別テーブル
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT
);

第二正規形 (2NF)

-- 悪い例: customer_name は customer_id にのみ依存
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  customer_name VARCHAR(100),  -- 部分的依存性!
  PRIMARY KEY (order_id, product_id)
);

-- 良い例: 顧客データを個別テーブルに
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

第三正規形 (3NF)

-- 悪い例: country は postal_code に依存
CREATE TABLE customers (
  id INT PRIMARY KEY,
  postal_code VARCHAR(10),
  country VARCHAR(50)  -- 推移的依存性!
);

-- 良い例: postal_codes テーブルを分割
CREATE TABLE postal_codes (
  code VARCHAR(10) PRIMARY KEY,
  country VARCHAR(50)
);

非正規化する時期

シナリオ	非正規化戦略
読み込み集約的なレポート	事前計算済みの集約
コストの高い JOIN	キャッシュされた派生カラム
分析ダッシュボード	マテリアライズドビュー

-- パフォーマンスのための非正規化
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10,2),  -- 計算済み
  item_count INT               -- 計算済み
);

</details> <details> <summary><strong>詳細解説: データ型</strong></summary>

文字列型

型	ユースケース	例
CHAR(n)	固定長	州コード、ISO 日付
VARCHAR(n)	可変長	名前、メール
TEXT	長いコンテンツ	記事、説明文

-- 適切なサイジング
email VARCHAR(255)
phone VARCHAR(20)
country_code CHAR(2)

数値型

型	範囲	ユースケース
TINYINT	-128 から 127	年齢、ステータスコード
SMALLINT	-32K から 32K	数量
INT	-2.1B から 2.1B	ID、カウント
BIGINT	非常に大きい	大きな ID、タイムスタンプ
DECIMAL(p,s)	完全精度	金額
FLOAT/DOUBLE	近似値	科学データ

-- 金額には常に DECIMAL を使用
price DECIMAL(10, 2)  -- $99,999,999.99

-- 金額に FLOAT を使用しない
price FLOAT  -- 丸め誤差!

日付/時刻型

DATE        -- 2025-10-31
TIME        -- 14:30:00
DATETIME    -- 2025-10-31 14:30:00
TIMESTAMP   -- タイムゾーン自動変換

-- 常に UTC で保存
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

ブール値

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE

-- MySQL
is_active TINYINT(1) DEFAULT 1

</details> <details> <summary><strong>詳細解説: インデックス戦略</strong></summary>

インデックスを作成すべき時期

常にインデックス	理由
外部キー	JOIN を高速化
WHERE 句のカラム	フィルタリングを高速化
ORDER BY カラム	ソートを高速化
UNIQUE 制約	一意性を実装

-- 外部キーインデックス
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- クエリパターンインデックス
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

インデックスタイプ

タイプ	最適な用途	例
B-Tree	範囲、等値	price > 100
Hash	完全一致のみ	email = 'x@y.com'
Full-text	テキスト検索	MATCH AGAINST
Partial	行のサブセット	WHERE is_active = true

複合インデックスの順序

CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- インデックスを使用 (customer_id が最初)
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- インデックスを使用しない (status のみ)
SELECT * FROM orders WHERE status = 'pending';

ルール: 最も選択的なカラムを最初に、または単独で最もクエリされるカラムを最初に。

インデックスの落とし穴

落とし穴	問題	解決方法
過度なインデックス	書き込みが遅い	クエリされるものだけインデックス
間違ったカラム順序	インデックスが使われない	クエリパターンに合わせる
FK インデックスの欠落	JOIN が遅い	常に FK にインデックスを設定

</details> <details> <summary><strong>詳細解説: 制約</strong></summary>

主キー

-- オートインクリメント (シンプル)
id INT AUTO_INCREMENT PRIMARY KEY

-- UUID (分散システム)
id CHAR(36) PRIMARY KEY DEFAULT (UUID())

-- 複合 (結合テーブル)
PRIMARY KEY (student_id, course_id)

外部キー

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE     -- 親と一緒に子を削除
  ON DELETE RESTRICT    -- 参照されている場合は削除を防止
  ON DELETE SET NULL    -- 親が削除されたら NULL に設定
  ON UPDATE CASCADE     -- 親が更新されたら子も更新

戦略	使用時期
CASCADE	依存データ (order_items)
RESTRICT	重要な参照 (事故防止)
SET NULL	オプション関係

その他の制約

-- 一意性
email VARCHAR(255) UNIQUE NOT NULL

-- 複合一意性
UNIQUE (student_id, course_id)

-- チェック
price DECIMAL(10,2) CHECK (price >= 0)
discount INT CHECK (discount BETWEEN 0 AND 100)

-- NOT NULL
name VARCHAR(100) NOT NULL

</details> <details> <summary><strong>詳細解説: 関係性パターン</strong></summary>

一対多

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id)
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL,
  quantity INT NOT NULL
);

多対多

-- 結合テーブル
CREATE TABLE enrollments (
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  course_id INT REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id)
);

自己参照

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INT REFERENCES employees(id)
);

ポリモーフィック

-- アプローチ 1: 個別の FK (強い完全性)
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INT REFERENCES posts(id),
  photo_id INT REFERENCES photos(id),
  CHECK (
    (post_id IS NOT NULL AND photo_id IS NULL) OR
    (post_id IS NULL AND photo_id IS NOT NULL)
  )
);

-- アプローチ 2: 型 + ID (柔軟、弱い完全性)
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  commentable_type VARCHAR(50) NOT NULL,
  commentable_id INT NOT NULL
);

</details> <details> <summary><strong>詳細解説: NoSQL 設計 (MongoDB)</strong></summary>

埋め込みと参照

要因	埋め込み	参照
アクセスパターン	まとめて読む	個別に読む
関係性	1:少数	1:多数
ドキュメントサイズ	小さい	16MB に近い
更新頻度	頻繁でない	頻繁

埋め込みドキュメント

{
  "_id": "order_123",
  "customer": {
    "id": "cust_456",
    "name": "Jane Smith",
    "email": "jane@example.com"
  },
  "items": [
    { "product_id": "prod_789", "quantity": 2, "price": 29.99 }
  ],
  "total": 109.97
}

参照ドキュメント

{
  "_id": "order_123",
  "customer_id": "cust_456",
  "item_ids": ["item_1", "item_2"],
  "total": 109.97
}

MongoDB インデックス

// 単一フィールド
db.users.createIndex({ email: 1 }, { unique: true });

// 複合
db.orders.createIndex({ customer_id: 1, created_at: -1 });

// テキスト検索
db.articles.createIndex({ title: "text", content: "text" });

// 地理空間
db.stores.createIndex({ location: "2dsphere" });

</details> <details> <summary><strong>詳細解説: マイグレーション</strong></summary>

マイグレーションのベストプラクティス

プラクティス	なぜ
常に可逆的に	ロールバックが必要
後方互換性を保つ	ゼロダウンタイムデプロイ
スキーマ前にデータ	関心の分離
ステージングでテスト	早期に問題を発見

カラムを追加 (ゼロダウンタイム)

-- ステップ 1: NULL 許可のカラムを追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- ステップ 2: 新規カラムに書き込みするコードをデプロイ

-- ステップ 3: 既存行をバックフィル
UPDATE users SET phone = '' WHERE phone IS NULL;

-- ステップ 4: 必要に応じて必須化
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;

カラムを名前変更 (ゼロダウンタイム)

-- ステップ 1: 新規カラムを追加
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- ステップ 2: データをコピー
UPDATE users SET email_address = email;

-- ステップ 3: 新規カラムから読む場合はコードをデプロイ
-- ステップ 4: 新規カラムに書き込む場合はコードをデプロイ

-- ステップ 5: 古いカラムを削除
ALTER TABLE users DROP COLUMN email;

マイグレーションテンプレート

-- マイグレーション: YYYYMMDDHHMMSS_description.sql

-- UP
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);
COMMIT;

-- DOWN
BEGIN;
DROP INDEX idx_users_phone ON users;
ALTER TABLE users DROP COLUMN phone;
COMMIT;

</details> <details> <summary><strong>詳細解説: パフォーマンス最適化</strong></summary>

クエリ分析

EXPLAIN SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';

確認項目	意味
type: ALL	フルテーブルスキャン (悪い)
type: ref	インデックスが使われている (良い)
key: NULL	インデックスが使われていない
rows: high	スキャンされた行が多い

N+1 クエリ問題

# 悪い例: N+1 クエリ
orders = db.query("SELECT * FROM orders")
for order in orders:
    customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")

# 良い例: 単一 JOIN
results = db.query("""
    SELECT orders.*, customers.name
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
""")

最適化テクニック

テクニック	使用時期
インデックス追加	遅い WHERE/ORDER BY
非正規化	コストの高い JOIN
ページング	大きな結果セット
キャッシング	繰り返されるクエリ
読み込みレプリカ	読み込み集約的な負荷
パーティショニング	非常に大きなテーブル

</details>

---

拡張ポイント

1. データベース固有のパターン: MySQL vs PostgreSQL vs SQLite の各バリエーションを追加
2. 高度なパターン: 時系列、イベントソーシング、CQRS、マルチテナント
3. ORM 統合: TypeORM、Prisma、SQLAlchemy のパターン
4. 監視: クエリパフォーマンス追跡、遅いクエリアラート