設計テンプレート集 — データベース設計入門

本ガイドで学んだ知識を実務で使うためのチェックリストとテンプレートをまとめています。新規プロジェクトの設計時 / レビュー時に活用してください。

命名規則チェックリスト

テーブル名

• snake_case で記述 (user_profiles、NG: UserProfiles)
• 複数形 (users、NG: user)
• 英語の業務語 を使う (orders、NG: tbl_注文)
• 2-3 単語 以内で簡潔に (order_items OK、customer_order_line_item_details は冗長)
• 接頭辞 tbl_ m_ を使わない (RDB の世界で users がテーブルなのは自明)

カラム名

• snake_case で記述
• 主キーは id (テーブル内で完結)、外部キーは <参照先テーブル単数_id> (user_id 等)
• Boolean は is_ / has_ / can_ で始める (is_active, has_subscription, can_edit)
• 日時は _at で終わる (created_at, updated_at, deleted_at)
• 日付は _date で終わる (birth_date, hire_date)
• 個数 / 金額には型を意識した名前 (item_count, total_amount)
• 意味不明な略語を避ける (flg → is_published, cnt → view_count)
• 日本語を避ける (DBMS / ツール互換性問題)

インデックス名

• idx_<テーブル>_<カラム> の規約で統一 (idx_users_email, idx_orders_user_created)
• UNIQUE 制約は uq_<テーブル>_<カラム> (uq_users_email)
• 複合インデックスは含むカラムを順に (idx_orders_user_created = (user_id, created_at))

標準カラム命名

用途	推奨名	説明
主キー	id	テーブル内一意
外部キー	<table_singular>_id	user_id, category_id
作成日時	created_at	レコード作成時
更新日時	updated_at	レコード更新時
削除日時 (論理削除)	deleted_at	NULL = 未削除
有効フラグ	is_active	Boolean
表示順	display_order または sort_order	INTEGER
作成者	created_by	作成ユーザー ID

キー設計チェックリスト

• 全テーブルに主キーがある (PK なしテーブルは原則 NG)
• 主キーはハイブリッド (代理キー = PK + 自然キー = UNIQUE 制約)
• 代理キーは SERIAL / BIGSERIAL / UUID (推測されたくないなら UUID)
• 主キーの型は BIGINT (BIGSERIAL) を default に (INTEGER は約 21 億で枯渇するリスクを避ける)
• 外部キー制約 (REFERENCES) を設定
• ON DELETE は明示: 親に従属するなら CASCADE、それ以外は RESTRICT (safe default)
• 外部キーにもインデックスを検討 (PostgreSQL は自動付与しない。MySQL/InnoDB は自動付与する)
• 複合主キーは多対多の中間テーブルで使う (それ以外は代理キーで)

正規化チェックリスト

第 1 正規形

• 各セルが原子的な値 (CSV やJSON 配列でない)
• 繰り返しグループがない (child_name_1, child_name_2 のようなカラム命名)
• 各レコードを一意に識別する主キーがある

第 2 正規形

• 複合主キーの一部だけで決まる属性がない (部分関数従属の解消)

第 3 正規形

• 主キー以外の属性が、主キー以外の他の属性で決まらない (推移的関数従属の解消)

非正規化の判断

• 計算で導ける値を保存していない (subtotal = quantity * unit_price は保存不要)
• スナップショットは意図して保存 (注文時の単価など、時系列の正確性のため)
• 集計テーブルを使う場合は元データから再同期可能 (バッチ / マテリアライズドビュー)

制約チェックリスト

• NOT NULL を必須項目に
• CHECK で値の範囲・列挙を保証 (CHECK (age >= 0), CHECK (status IN ('active', 'inactive')))
• UNIQUE で業務上の一意性を保証 (email, code など)
• FOREIGN KEY で参照整合性を保証
• DEFAULT で初期値を設定 (is_active BOOLEAN NOT NULL DEFAULT TRUE)

インデックスチェックリスト

各項目の根拠と設計の考え方は第 19 章 インデックス設計入門を参照してください。

• 頻繁に検索 (WHERE) で使うカラムにインデックス
• JOIN 条件で使うカラム (= 外部キー) にインデックス
• ORDER BY によく使うカラムにインデックス
• 複合インデックスはカラム順序を意識 (左から順に絞り込めるか)
• 不要なインデックスは削除 (使われない / 書き込み性能を圧迫)
• 部分インデックス (WHERE 条件付き) を活用 (WHERE deleted_at IS NULL 等)

認証認可チェックリスト

• パスワードは bcrypt / Argon2 でハッシュ化 (MD5/SHA-256 NG)
• SQL Injection 対策にパラメータ化クエリ
• セッションは Cookie + DB / Redis (JWT 単独は無効化困難)
• MFA の secret は暗号化保存
• アカウントロックは OWASP/NIST 準拠 (漸進遅延 + CAPTCHA、5回30分は DoS リスク)
• 監査ログは追記専用 (REVOKE UPDATE, DELETE)
• OAuth は Authorization Code Flow + PKCE (Implicit Flow NG)
• ID Token は JWT ライブラリで検証 (自前実装 NG)

削除戦略チェックリスト

データの性質に応じて削除戦略を選びます。

データ種類	推奨戦略	例
個人データ	物理削除 or 完全匿名化	GDPR / 個人情報保護法
業務履歴	論理削除 (deleted_at)	注文・売上 (税務 7 年保管)
短命データ	物理削除	カート・セッション
監査ログ	削除しない (アーカイブ)	法令遵守

• 個人データの削除リクエストに対応する手順がある
• 論理削除を使うテーブルでは WHERE deleted_at IS NULL をクエリに含める仕組み (ORM のグローバルスコープなど)
• CASCADE 削除の波及範囲を確認 (顧客削除で注文消失などは NG)

設計レビュー観点 (チームレビュー用)

キー設計

• すべてのテーブルに主キーがあるか
• 代理キーと自然キーの使い分けは適切か
• 外部キー制約は設定されているか

正規化

• 第 3 正規形まで進めたか
• 意図的な非正規化はコメントで明示されているか
• スナップショットすべきカラム (注文時単価など) は保存されているか

制約

• NOT NULL / CHECK / UNIQUE / FOREIGN KEY が適切に設定されているか
• ON DELETE の方針 (CASCADE / RESTRICT / SET NULL) は明確か

インデックス

• 検索で使うカラムにインデックスがあるか
• 不要なインデックスはないか
• 複合インデックスのカラム順序は適切か

セキュリティ

• パスワードはハッシュ化されているか
• 機微情報 (個人 / トークン) は暗号化されているか
• 監査ログが必要な操作は記録されているか

運用

• 削除戦略は明確か (物理 / 論理 / アーカイブ)
• GDPR / 個人情報保護法への対応方針があるか
• バックアップ・障害対策が考慮されているか
• 命名規則がプロジェクト内で統一されているか

設計ドキュメントテンプレート

新規プロジェクトの DB 設計を文書化するときの最小限のテンプレートです。

# DB 設計書 — [システム名]

## 概要

- システムの目的: ...
- 主要ユースケース: ...
- 想定データ規模: ユーザー XXX 万 / 日次データ XXX 件

## エンティティ一覧

| エンティティ | 種別 | 役割 |
|:----|:----|:----|
| users | マスタ | システム利用者 |
| products | マスタ | 商品マスタ |
| orders | トランザクション | 注文履歴 |
| ... | ... | ... |

## E-R 図

```mermaid
erDiagram
    USERS ||--o{ ORDERS : places
    ...
```

## テーブル定義

### users

| カラム | 型 | 制約 | 説明 |
|:----|:----|:----|:----|
| id | SERIAL | PRIMARY KEY | 代理キー |
| email | VARCHAR(255) | UNIQUE NOT NULL | メール (業務キー) |
| ... | ... | ... | ... |

### orders

| カラム | 型 | 制約 | 説明 |
|:----|:----|:----|:----|
| id | BIGSERIAL | PRIMARY KEY | 代理キー |
| user_id | INTEGER | NOT NULL REFERENCES users(id) | 注文者 |
| total_amount | NUMERIC(12,2) | NOT NULL CHECK > 0 | 合計金額 |
| ... | ... | ... | ... |

## 主要クエリパターン

### ユーザーの注文履歴
```sql
SELECT o.* FROM orders o WHERE o.user_id = $1 ORDER BY o.order_date DESC;
```
- インデックス: `(user_id, order_date)` 複合
- 想定実行頻度: マイページ表示時 (秒間 XXX 件)

## 削除戦略

| テーブル | 戦略 | 理由 |
|:----|:----|:----|
| users | 論理削除 (`deleted_at`) | 注文履歴の参照整合性 |
| orders | 論理削除 | 税務 7 年保管 |
| sessions | 物理削除 (TTL) | 短命データ |

## セキュリティ

- パスワード: bcrypt (cost 12。OWASP 推奨の最低線は 10) でハッシュ化
- セッション: Redis、有効期限 24 時間
- 監査ログ: `audit_logs` テーブル、REVOKE UPDATE/DELETE 適用

## 性能・スケーラビリティ

- 想定ピーク: 秒間 XXX 件
- インデックス戦略: ... 
- パーティション戦略: ... (採用しないなら「不要」と明示)

## 将来の拡張ポイント

- 多言語対応: `products.name` を多言語化する場合は `product_translations` テーブルを追加 (現状未実装)
- ...

このテンプレートのカスタマイズ

プロジェクトの規模・要件に応じてセクションを増減してください。「最低限これだけは書く」観点として:

1. エンティティ一覧 (どんなテーブルがあるか俯瞰)
2. E-R 図 (関係性の視覚化)
3. 主要テーブルのカラム定義 (型・制約・説明)
4. 削除戦略 (実装時の落とし穴防止)
5. セキュリティ方針 (レビュー観点)

関連リファレンス

• 用語集 — 専門用語の即引き辞典
• ガイドのトップ — 本ガイドの全体マップ