database-schema-design

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

正規化、リレーションシップ、制約、エラー防止を含む、PostgreSQL と MySQL 向けの包括的なデータベーススキーマ設計パターンです。

---

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

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

# Basic schema with users, products, orders
cat templates/basic-schema.sql

# Relationship patterns (1:1, 1:M, M:M)
cat templates/relationships.sql

# Constraint examples
cat templates/constraints.sql

# Audit patterns
cat templates/audit-columns.sql

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

• 1NF：繰り返しグループがなく、値がアトミックである
• 2NF：複合キーに対する部分関数従属がない
• 3NF：推移的関数従属がない
• 詳しい例については、references/normalization-guide.md を読み込みます

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

CREATE TABLE your_table (
  -- Primary key (required)
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Business columns with proper types
  name VARCHAR(200) NOT NULL,  -- Use appropriate lengths

  -- Audit columns (always include)
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

---

重要なルール

✓ 必ず行うこと

ルール	理由
すべてのテーブルに PRIMARY KEY を設定する	行の一意性を保証し、リレーションシップを可能にします
外部キーを明示的に定義する	参照整合性を強制し、孤立レコードを防ぎます
すべての外部キーにインデックスを設定する	遅い JOIN を防ぎ、パフォーマンスに不可欠です
必須フィールドに NOT NULL を設定する	データの整合性を保ち、NULL の混入を防ぎます
監査カラム（created_at、updated_at）を設定する	変更の追跡、デバッグ、コンプライアンスに役立ちます
適切なデータ型を選択する	ストレージ効率、検証、インデックス作成に役立ちます
列挙値にはチェック制約を設定する	データベースレベルで有効な値を強制します
ON DELETE/UPDATE ルールを指定する	意図しないデータ損失や孤立レコードを防ぎます

✗ 絶対に行わないこと

アンチパターン	問題点
あらゆる場所で VARCHAR(MAX) を使う	スペースを浪費し、インデックスを遅くし、検証もできません
日付を VARCHAR として保存する	日付計算ができず、検証もできず、並べ替えも正しく機能しません
外部キーがない	参照整合性がなく、孤立レコードが発生します
時期尚早な非正規化	保守が難しく、データ異常が発生します
EAV（エンティティ・属性・値）	クエリが複雑になり、型安全性がなく、処理も遅くなります
ポリモーフィックな関連付け	外部キーの整合性がなく、クエリが複雑になります
循環依存	データを投入できず、CASCADE が機能しなくなります
外部キーにインデックスがない	JOIN が非常に遅くなり、パフォーマンスが大きく低下します

---

重大なエラー上位 7 件

エラー 1：主キーがない

症状：行を一意に識別できず、データが重複します
修正：

-- ❌ Bad
CREATE TABLE users (
  email VARCHAR(255),
  name VARCHAR(100)
);

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

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

症状：孤立レコード、データの不整合が発生します
修正：

-- ❌ Bad
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID  -- No constraint!
);

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

-- Index the foreign key
CREATE INDEX idx_orders_user_id ON orders(user_id);

エラー 3：あらゆる場所で VARCHAR(MAX) を使う

症状：スペースを浪費し、インデックスが遅くなり、検証もできません
修正：

-- ❌ Bad
CREATE TABLE products (
  name VARCHAR(MAX),
  sku VARCHAR(MAX),
  status VARCHAR(MAX)
);

-- ✅ Good
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：データ型が不適切（日付を文字列として保存）

症状：日付を検証できず、並べ替えが正しく機能せず、日付計算もできません
修正：

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

-- ✅ Good
CREATE TABLE events (
  event_date DATE NOT NULL,  -- Validated, sortable
  event_time TIMESTAMPTZ     -- With timezone
);

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

症状：JOIN が非常に遅く、クエリのパフォーマンスが低下します
修正：

-- Always index foreign keys
CREATE TABLE order_items (
  order_id UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id)
);

-- ✅ Required indexes
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：監査カラムがない

症状：レコードの作成・変更時期を追跡できません
修正：

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

-- ✅ Good
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
);

-- Auto-update trigger (PostgreSQL)
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

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

症状：クエリが複雑になり、型安全性がなく、パフォーマンスも低下します
修正：

-- ❌ Bad (EAV)
CREATE TABLE product_attributes (
  product_id UUID,
  attribute_name VARCHAR(100),  -- 'color', 'size', 'price'
  attribute_value TEXT           -- Everything as text!
);

-- ✅ Good (Structured + JSONB)
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,  -- Required fields as columns
  color VARCHAR(50),              -- Common attributes as columns
  size VARCHAR(20),
  attributes JSONB                -- Optional/dynamic attributes
);

-- Index JSONB
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

詳しい修正方法を含む全 12 件のエラーについては、references/error-catalog.md を読み込みます。

---

よくあるスキーマパターン

パターン	用途	テンプレート
基本 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 + 結合従属性がない	非常にまれで、学術的

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

詳細な変更前・変更後の例については、references/normalization-guide.md を読み込んでください。

---

設定の概要

PostgreSQL 推奨型

-- Primary Keys
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- OR for performance-critical:
id BIGSERIAL PRIMARY KEY

-- Text
name VARCHAR(200) NOT NULL
description TEXT
code CHAR(10)  -- Fixed-length codes only

-- Numbers
price DECIMAL(10,2) NOT NULL  -- Money: NEVER use FLOAT
quantity INT NOT NULL
rating DECIMAL(3,2)  -- 0.00 to 9.99

-- Dates/Times
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL  -- With timezone
event_date DATE
duration INTERVAL

-- Boolean
is_active BOOLEAN DEFAULT true NOT NULL

-- JSON
attributes JSONB  -- Binary, faster, indexable

-- Enum Alternative (preferred over ENUM type)
status VARCHAR(20) NOT NULL
  CHECK (status IN ('draft', 'active', 'archived'))

MySQL との違い

-- MySQL doesn't have:
TIMESTAMPTZ  -- Use TIMESTAMP (stored as UTC)
gen_random_uuid()  -- Use UUID() function
JSONB  -- Use JSON (same performance in 8.0+)

-- MySQL equivalent:
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- OR:
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 と 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),           -- Issue: No primary key, VARCHAR(MAX)
  password VARCHAR(MAX),
  created VARCHAR(50)           -- Issue: Date as string
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_email VARCHAR(MAX),      -- Issue: No foreign key
  total VARCHAR(20),            -- Issue: Money as string
  status VARCHAR(MAX)           -- Issue: No validation
);

変更後（本番環境対応）：

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. ✅ 外部キーにインデックスがない → すべての FK にインデックスを設定
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 と MySQL のデータ型
• references/constraints-catalog.md - すべての制約
• references/schema-design-patterns.md - ベストプラクティス
• references/error-catalog.md - 12 件すべてのエラーの説明

公式ドキュメント：

• PostgreSQL データ型：https://www.postgresql.org/docs/current/datatype.html
• PostgreSQL 制約：https://www.postgresql.org/docs/current/ddl-constraints.html
• MySQL データ型：https://dev.mysql.com/doc/refman/8.0/en/data-types.html

---

本番環境でのテスト済み | 12 件のエラーを防止 | MIT ライセンス