database-schema-design

PostgreSQLおよびMySQLの包括的なデータベーススキーマ設計パターン（正規化、リレーションシップ、制約、エラー防止）。

---

クイックスタート（10分）

ステップ1: テンプレートからスキーマパターンを選択します：

# ユーザー、商品、注文を含む基本スキーマ
cat templates/basic-schema.sql

# リレーションシップパターン (1:1, 1:M, M:M)
cat templates/relationships.sql

# 制約の例
cat templates/constraints.sql

# 監査パターン
cat templates/audit-columns.sql

ステップ2: 正規化ルールを適用します（最低でも3NF）：

• 1NF: 重複するグループがなく、値が原子的である
• 2NF: 複合キーへの部分的な依存がない
• 3NF: 推移的な依存がない
• 詳細な例は references/normalization-guide.md を参照してください

ステップ3: すべてのテーブルに必須要素を追加します：

CREATE TABLE your_table (
  -- 主キー（必須）
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- 適切な型を持つビジネス列
  name VARCHAR(200) NOT NULL,  -- 適切な長さを使用

  -- 監査列（常に含める）
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

---

重要なルール

✓ 必ず実施すること

ルール	理由
すべてのテーブルに主キーを設定	行の一意性を保証し、リレーションシップを有効化
外部キーを明示的に定義	参照整合性を強制し、孤立レコードを防止
すべての外部キーにインデックスを設定	スロージョインを防止、パフォーマンスが重要
必須フィールドに NOT NULL を設定	データ整合性、NULL汚染を防止
監査列（created_at, updated_at）を追加	変更を追跡、デバッグ、コンプライアンス
適切なデータ型を使用	ストレージ効率、検証、インデックス作成
列挙型に CHECK 制約を使用	有効な値をデータベースレベルで強制
ON DELETE/UPDATE ルールを指定	誤ったデータ削除や孤立を防止

✗ 決してしてはいけないこと

アンチパターン	なぜ悪いのか
すべてで VARCHAR(MAX) を使用	スペースを浪費、インデックスが遅い、検証がない
日付を VARCHAR で保存	日付計算ができない、検証がない、ソートが壊れる
外部キー制約がない	参照整合性がない、孤立レコードが発生
過度な非正規化	メンテナンスが困難、データ異常が発生
EAV（Entity-Attribute-Value）	クエリが複雑、型安全性がない、低速
ポリモーフィック関連付け	外部キー整合性がない、複雑なクエリ
循環依存関係	データを入力不可能、CASCADE が破綻
外部キーにインデックスがない	JOIN が極めて遅い、パフォーマンス殺し

---

トップ7の重大エラー

エラー1: 主キーがない

症状: 行を一意に識別できない、データの重複 修正:

-- ❌ 悪い例
CREATE TABLE users (
  email VARCHAR(255),
  name VARCHAR(100)
);

-- ✅ 良い例
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

エラー2: 外部キー制約がない

症状: 孤立レコード、データ不整合 修正:

-- ❌ 悪い例
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID  -- 制約がない！
);

-- ✅ 良い例
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

-- 外部キーにインデックスを作成
CREATE INDEX idx_orders_user_id ON orders(user_id);

エラー3: すべてで VARCHAR(MAX)

症状: スペース浪費、遅いインデックス、検証がない 修正:

-- ❌ 悪い例
CREATE TABLE products (
  name VARCHAR(MAX),
  sku VARCHAR(MAX),
  status VARCHAR(MAX)
);

-- ✅ 良い例
CREATE TABLE products (
  name VARCHAR(200) NOT NULL,
  sku VARCHAR(50) UNIQUE NOT NULL,
  status VARCHAR(20) NOT NULL
    CHECK (status IN ('draft', 'active', 'archived'))
);

エラー4: 不適切なデータ型（日付を文字列で保存）

症状: 日付検証がない、ソートが壊れる、日付計算ができない 修正:

-- ❌ 悪い例
CREATE TABLE events (
  event_date VARCHAR(50)  -- '2025-12-15' or 'Dec 15, 2025'?
);

-- ✅ 良い例
CREATE TABLE events (
  event_date DATE NOT NULL,  -- 検証済み、ソート可能
  event_time TIMESTAMPTZ     -- タイムゾーン付き
);

エラー5: 外部キーにインデックスがない

症状: JOIN が極めて遅い、クエリパフォーマンス低下 修正:

-- 常に外部キーにインデックスを作成
CREATE TABLE order_items (
  order_id UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id)
);

-- ✅ 必須のインデックス
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

エラー6: 監査列がない

症状: レコードの作成/変更時期が追跡できない 修正:

-- ❌ 悪い例
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200)
);

-- ✅ 良い例
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- 自動更新トリガー (PostgreSQL)
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

エラー7: EAV アンチパターン

症状: クエリが複雑、型安全性がない、パフォーマンス低下 修正:

-- ❌ 悪い例 (EAV)
CREATE TABLE product_attributes (
  product_id UUID,
  attribute_name VARCHAR(100),  -- 'color', 'size', 'price'
  attribute_value TEXT           -- すべてがテキスト！
);

-- ✅ 良い例 (構造化 + JSONB)
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,  -- 必須フィールドを列で
  color VARCHAR(50),              -- 一般的な属性を列で
  size VARCHAR(20),
  attributes JSONB                -- オプション/動的属性
);

-- JSONB にインデックスを作成
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

詳しくは references/error-catalog.md で全12エラーと詳細な修正方法を参照してください。

---

一般的なスキーマパターン

パターン	ユースケース	テンプレート
基本CRUD	標準的なユーザー/商品/注文	templates/basic-schema.sql
一対一	ユーザー → プロフィール	templates/relationships.sql (行7-17)
一対多	ユーザー → 注文	templates/relationships.sql (行23-34)
多対多	学生 ↔ コース	templates/relationships.sql (行40-60)
階層的	カテゴリーツリー、組織図	templates/relationships.sql (行66-83)
論理削除	削除済みをマーク、履歴保持	templates/audit-columns.sql (行55-80)
バージョン管理	時系列で変更を追跡	templates/audit-columns.sql (行86-108)
マルチテナント	組織ごとにデータ分離	references/schema-design-patterns.md (行228-258)

---

正規化クイックリファレンス

形式	ルール	例
1NF	原子的な値、重複するグループがない	phone1, phone2 → phones テーブル
2NF	1NF + 複合キーへの部分的依存がない	複合キー依存 → 別テーブル
3NF	2NF + 推移的依存がない	user.city → city.id 参照
BCNF	3NF + すべての決定要因が候補キー	稀なエッジケース
4NF	BCNF + 多値依存がない	複雑な多対多
5NF	4NF + join依存がない	非常に稀、学術的

推奨: 3NFまで設計し、計測されたパフォーマンスデータがある場合のみ非正規化してください。

詳しくは references/normalization-guide.md で詳細な例（修正前後）を参照してください。

---

設定サマリー

PostgreSQL 推奨型

-- 主キー
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- またはパフォーマンス重視の場合:
id BIGSERIAL PRIMARY KEY

-- テキスト
name VARCHAR(200) NOT NULL
description TEXT
code CHAR(10)  -- 固定長コードのみ

-- 数値
price DECIMAL(10,2) NOT NULL  -- 金額: 決して FLOAT を使用しないこと
quantity INT NOT NULL
rating DECIMAL(3,2)  -- 0.00 から 9.99

-- 日付/時刻
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL  -- タイムゾーン付き
event_date DATE
duration INTERVAL

-- ブール値
is_active BOOLEAN DEFAULT true NOT NULL

-- JSON
attributes JSONB  -- バイナリ、高速、インデックス可能

-- 列挙型代替（ENUM型より推奨）
status VARCHAR(20) NOT NULL
  CHECK (status IN ('draft', 'active', 'archived'))

MySQL の相違点

-- MySQL にはない:
TIMESTAMPTZ  -- TIMESTAMP を使用（UTCで保存）
gen_random_uuid()  -- UUID() 関数を使用
JSONB  -- JSON を使用（8.0+で同じパフォーマンス）

-- MySQL 同等の記述:
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- または:
id BIGINT AUTO_INCREMENT PRIMARY KEY

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
attributes JSON

詳しくは references/data-types-guide.md で包括的な型選択ガイドを参照してください。

---

リファレンス読み込み時期

スキーマ設計プロセス

次の場合に references/schema-design-patterns.md を参照してください：

• 新しいデータベース設計を開始する
• パターン例が必要（監査列、論理削除、バージョン管理）
• マルチテナント機能を実装する
• UUID vs BIGSERIAL を選択する
• 命名規則を従う

正規化

次の場合に references/normalization-guide.md を参照してください：

• スキーマにデータ重複がある
• 現在の正規化形式が不明確
• 既存スキーマを正規化する必要がある
• データベース構造を計画する

リレーションシップ

次の場合に references/relationship-patterns.md を参照してください：

• テーブルリレーションシップを定義する
• ジャンクションテーブルを実装する
• 階層的構造を作成する
• カスケードルールを設定する

データ型

次の場合に references/data-types-guide.md を参照してください：

• 列型を選択する
• PostgreSQL/MySQL 間でマイグレーションする
• ストレージを最適化する
• JSON フィールドを実装する

制約

次の場合に references/constraints-catalog.md を参照してください：

• 検証ルールを追加する
• CHECK 制約を実装する
• 外部キーカスケードを設定する
• 一意制約を作成する

エラー防止

次の場合に references/error-catalog.md を参照してください：

• スキーマレビューが必要
• スキーマの問題をトラブルシューティング
• 全12個の文書化されたエラーと修正

---

完全なセットアップチェックリスト

テーブル作成前:

• 最低でも3NFに正規化されている
• すべてのリレーションシップが識別されている
• データ型が適切に選択されている
• カスケードルールが定義されている

すべてのテーブルが必須:

• 主キーが定義されている
• 監査列（created_at, updated_at）がある
• 必須フィールドに NOT NULL が設定されている
• VARCHAR に適切な長さがある（MAX ではない）
• 列挙型/範囲に CHECK 制約がある

外部キー:

• すべての外部キーが REFERENCES で定義されている
• ON DELETE/UPDATE アクションが指定されている
• すべての外部キーがインデックスされている

インデックス:

• 外部キーがインデックスされている
• よくクエリされる列がインデックスされている
• 複数列クエリに複合インデックスがある

検証:

• 循環依存関係がない
• EAV パターンがない
• ポリモーフィック関連付けがない
• 正しいデータ型（日付を文字列で保存していない）

---

本番環境の例

修正前（複数の問題）:

CREATE TABLE users (
  email VARCHAR(MAX),           -- 問題: 主キーがない、VARCHAR(MAX)
  password VARCHAR(MAX),
  created VARCHAR(50)           -- 問題: 日付が文字列
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_email VARCHAR(MAX),      -- 問題: 外部キーがない
  total VARCHAR(20),            -- 問題: 金額が文字列
  status VARCHAR(MAX)           -- 問題: 検証がない
);

修正後（本番環境対応）:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'canceled')),
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

結果: ✅ すべての制約が強制され、型が適切で、インデックスがあり、監査可能

---

既知の問題防止

全12個の文書化されたエラーを防止:

1. ✅ 主キーがない → UUID/BIGSERIAL が必須
2. ✅ 外部キー制約がない → REFERENCES が必須
3. ✅ すべてで VARCHAR(MAX) → 適切な長さ
4. ✅ 正当化のない非正規化 → 最低3NF
5. ✅ NOT NULL 制約がない → 必須フィールドをマーク
6. ✅ 外部キーにインデックスがない → すべての外部キーをインデックス
7. ✅ 不適切なデータ型 → 正しい型選択
8. ✅ CHECK 制約がない → 検証ルール
9. ✅ 監査列がない → created_at/updated_at が必須
10. ✅ 循環依存関係 → 依存関係分析
11. ✅ ON DELETE/UPDATE カスケードがない → カスケードルール
12. ✅ EAV アンチパターン → 構造化スキーマ + JSONB

参照: references/error-catalog.md 詳細な修正のため

---

リソース

テンプレート:

• templates/basic-schema.sql - ユーザー、商品、注文スターター
• templates/relationships.sql - すべてのリレーションシップ型
• templates/constraints.sql - 制約の例
• templates/audit-columns.sql - 監査パターン + トリガー

リファレンス:

• references/normalization-guide.md - 1NF～5NF 詳細
• references/relationship-patterns.md - リレーションシップ型
• references/data-types-guide.md - PostgreSQL vs MySQL 型
• references/constraints-catalog.md - すべての制約
• references/schema-design-patterns.md - ベストプラクティス
• references/error-catalog.md - 全12エラー文書化

公式ドキュメント:

• PostgreSQL Data Types: https://www.postgresql.org/docs/current/datatype.html
• PostgreSQL Constraints: https://www.postgresql.org/docs/current/ddl-constraints.html
• MySQL Data Types: https://dev.mysql.com/doc/refman/8.0/en/data-types.html

---

本番環境での実績 | 12エラー防止 | MITライセンス