設計と正規化

ER 図、エンティティ抽出、正規化（1NF / 2NF / 3NF / BCNF）、 命名規則、論理削除、サロゲートキー、現実的な非正規化まで。

設計のステップ

1. 業務理解: 何を扱うアプリか
2. エンティティ抽出: 名詞を拾う（User / Post / Order...）
3. 属性決定: 各エンティティの列
4. 関係定義: 1-1 / 1-N / N-N
5. ER 図にする
6. 正規化を当てる
7. 必要に応じて非正規化（性能 / 簡潔さ）
8. 制約・インデックスを入れる

ER 図

Entity-Relationship 図。テーブル間の関係を視覚化。Mermaid / dbdiagram.io / DrawSQL で簡単に書ける。

erDiagram
  USER ||--o{ POST : writes
  USER ||--o{ COMMENT : writes
  POST ||--o{ COMMENT : has
  POST }o--o{ TAG : tagged

  USER {
    bigint id PK
    text email UK
    text name
    timestamptz created_at
  }
  POST {
    bigint id PK
    text title
    text content
    bigint user_id FK
  }

正規化（Normalization）

データ重複を排除し、更新異常を防ぐための系統的な手順。第 1〜5 正規形まである。 実務は3NFを目標にすることが多い。

第 1 正規形（1NF）

• 各セルが原子値（分割不可能）
• 「タグ列にカンマ区切り」は NG → 別テーブル
• 繰り返しグループの除去

正規化前（NG）

posts
+----+----------+--------------------+
| id | title    | tags               |
+----+----------+--------------------+
| 1  | Hello    | js,react,nextjs    |
+----+----------+--------------------+
      

1NF 化後

posts                    post_tags
+----+--------+          +---------+--------+
| id | title  |          | post_id | tag    |
+----+--------+          +---------+--------+
| 1  | Hello  |          | 1       | js     |
+----+--------+          | 1       | react  |
                         | 1       | nextjs |
                         +---------+--------+
      

第 2 正規形（2NF）

• 1NF を満たす
• 部分関数従属を排除（複合主キーの一部に依存する列を別テーブルへ）

第 3 正規形（3NF）

• 2NF を満たす
• 推移的従属を排除（主キー以外の列に依存する列を別テーブルへ）

例: 推移的従属

orders（NG: zip_code から city が決まる = 推移的従属）
+----+---------+------+----------+
| id | user_id | zip  | city     |
+----+---------+------+----------+
| 1  | 42      | 100  | Tokyo    |
+----+---------+------+----------+

→ 3NF 化: city は zip_codes テーブルに分離
      

BCNF（Boyce-Codd 正規形）

3NF より厳しい版。すべての非自明な関数従属で、左辺がスーパーキーであること。

4NF / 5NF

多値従属 / 結合従属の話。実務ではあまり気にしない。

非正規化（あえて重複させる）

正規化は整合性に強く、性能に弱い。実務では一部を意図的に非正規化することがある:

• カウンタキャッシュ: posts.comment_count を持つ
• display_name の埋め込み: 別テーブルから JOIN せず直接保持
• マテリアライズドビュー: 集計結果をテーブル化
• 履歴テーブル: イミュータブルにする

トレードオフは更新時の整合性維持コスト。トリガー / アプリ側で同期する。

主キーの選び方（再掲）

サロゲートキー（人工キー）

• BIGSERIAL / UUID
• ビジネス意味なし
• 変更されない安心感
• 現代の標準

ナチュラルキー（自然キー）

• email / sku / order_no
• 意味があり読みやすい
• 変更される可能性 → FK 連鎖更新が必要に

定石

サロゲートキーを主キーに、ナチュラルキーには UNIQUE 制約。

命名規則

• テーブル名は複数形 snake_case: users, blog_posts
• 列は snake_case: created_at
• FK は {ref_table}_id: user_id
• 真偽値はis_/has_ プレフィックス: is_published
• タイムスタンプは created_at / updated_at / deleted_at
• 命名はチームで揃える（途中で変えない）

マスタ / トランザクション / ログの分離

• マスタ系: ユーザ、商品、設定。読み多め、更新少なめ
• トランザクション系: 注文、メッセージ、いいね。書き込み中心
• ログ系: アクセスログ、監査ログ。追記専用、定期的にアーカイブ

ログ系はパーティションを切ると古いデータの削除が高速。

論理削除（ソフトデリート）

CREATE TABLE posts (
  ...
  deleted_at TIMESTAMPTZ
);

-- 取得は常に NULL でフィルタ
SELECT * FROM posts WHERE deleted_at IS NULL;

• 誤削除に強い、復元可能
• クエリで毎回フィルタを忘れないように → ビュー or アプリ層で隠蔽
• UNIQUE 制約に注意（削除済み + 同じ値が衝突する）
• 本当に消したい時は別バッチで物理削除

updated_at の自動更新

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION set_updated_at();

履歴 / 監査

• 変更履歴は別テーブルに追加（audit_log）
• またはテーブルバージョニング（version 列 + 履歴テーブル）
• 金融系ではイベントソーシング（追記専用）

JSONB の使い所

Postgres の JSONB は柔軟スキーマと正規化の中間:

• ユーザ設定 / メタデータ / 多言語の翻訳
• 外部 API のレスポンスをそのまま保存
• 頻繁にクエリしないログ的データ

逆にやってはいけないこと:

• 関係（FK）を JSONB に詰める
• 頻繁に部分更新する
• WHERE で深い path 検索を多用（インデックスが効きにくい）

Enum vs ルックアップテーブル

テーブル分割の判断

• 1 テーブル列が 30 を超えると読みにくい → サブテーブル化
• 同じエンティティでもライフサイクルが違う列は分ける（profile / settings）
• 巨大列（長文 / バイナリ）は別テーブル / 別ストレージ
• ホット / コールドで分けて Hot 側を軽く

多テナント設計

SaaS は「共有 + tenant_id + RLS」が現代的。

イミュータブルテーブル

履歴 / イベント / 取引のようにUPDATE しないテーブルを作る:

• 整合性追跡が容易
• 監査・調査が簡単
• レプリケーションも軽い
• マスタ系は別テーブル（profile_versions）

サンプル設計（ブログ）

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  slug TEXT NOT NULL UNIQUE,
  content TEXT,
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','published','archived')),
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ
);

CREATE INDEX posts_author_idx ON posts (author_id);
CREATE INDEX posts_published_idx ON posts (published_at DESC) WHERE status = 'published';

CREATE TABLE tags (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id BIGINT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id UUID NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  parent_id UUID REFERENCES comments(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX comments_post_idx ON comments (post_id, created_at);

ツール

• dbdiagram.io — DSL で ER 図
• DrawSQL — チームで共同編集
• Mermaid — Markdown に書ける
• pgAdmin / DBeaver / TablePlus — GUI
• Prisma — schema → ER 図 → migration

失敗しやすいパターン