テーブル設計とリレーションの実務判断

テーブル設計はDB力の中心

テーブル設計は、アプリケーションの土台です。

良いテーブル設計は、データの壊れ方を防ぎ、SQLを簡単にし、チューニングもしやすくします。

逆に、設計が曖昧だと、アプリ側で大量の例外処理が必要になります。

まずエンティティを分ける

テーブル設計では、画面ではなくデータの種類で分けます。

例: 学習サービス

画面に「ユーザー名、プラン名、次回面談」が一緒に表示されるからといって、1テーブルに詰め込むわけではありません。

関係を決める

テーブル間の関係を言葉にします。

users は subscriptions を複数持つ
plans は subscriptions から参照される
assignments は submissions を複数持つ

関係の種類:

多対多を列で表すのは避けます。

避けたい例:

tag1 TEXT,
tag2 TEXT,
tag3 TEXT

望ましい例:

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

主キーは安定した識別子にする

主キーは、行を一意に識別するための値です。

よく使う選択肢:

メールアドレスや外部サービスIDを主キーにすると、変更や連携変更で困ることがあります。内部IDを主キーにし、メールアドレスには UNIQUE を付ける設計が扱いやすいことが多いです。

制約で壊れ方を防ぐ

DB制約は、アプリケーションの最後の防御線です。

例:

CREATE TABLE subscriptions (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  plan_id BIGINT NOT NULL REFERENCES plans(id),
  status TEXT NOT NULL CHECK (status IN ('active', 'paused', 'cancelled')),
  started_at TIMESTAMPTZ NOT NULL,
  cancelled_at TIMESTAMPTZ
);

制約をアプリ側だけに置くと、バッチ、管理画面、別API、手動SQLから壊れる可能性があります。

正規化と非正規化

正規化は、重複を減らして更新漏れを防ぐ考え方です。

正規化したい例:

orders に customer_name と customer_email を毎回保存
ユーザーのメール変更時に過去注文の値がバラバラになる

ただし、非正規化が必要な場面もあります。

例:

• 注文時点の商品名を残す
• 請求時点の金額を固定する
• 集計結果を高速表示したい
• 履歴として当時の値を保存したい

非正規化は悪ではありません。理由なく重複させるのが危険です。

履歴をどう持つか

履歴設計は実務で重要です。

例: プラン変更履歴

CREATE TABLE subscription_plan_changes (
  id BIGSERIAL PRIMARY KEY,
  subscription_id BIGINT NOT NULL REFERENCES subscriptions(id),
  old_plan_id BIGINT REFERENCES plans(id),
  new_plan_id BIGINT NOT NULL REFERENCES plans(id),
  changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

現在値だけでよいのか、過去の状態も必要なのかを決めます。

履歴が必要なもの:

• 料金
• 契約状態
• 権限変更
• 住所や請求先
• 面談履歴
• 学習進捗

「あとで必要になったら取る」は、過去データには通用しません。

インデックスはクエリから逆算する

インデックスは列に対して貼るものですが、設計はクエリから逆算します。

例:

SELECT *
FROM submissions
WHERE assignment_id = $1
ORDER BY submitted_at DESC
LIMIT 20;

候補:

CREATE INDEX idx_submissions_assignment_submitted
ON submissions(assignment_id, submitted_at DESC);

インデックス設計で見ること:

• WHERE でよく使う列
• JOIN に使う外部キー
• ORDER BY に使う列
• 複合インデックスの列順
• 更新頻度
• カーディナリティ

外部キー列には、参照だけでなくJOINや削除確認で使うため、インデックスを検討します。

設計レビューのチェックリスト

• 画面都合で1テーブルに詰め込んでいないか
• 主キーは安定しているか
• 外部キーで関係を守っているか
• NOT NULL が必要な列に付いているか
• 重複禁止に UNIQUE があるか
• 多対多を中間テーブルで表しているか
• 履歴が必要なデータを消していないか
• 削除は物理削除か論理削除か決めているか
• よく使うクエリにインデックスがあるか
• インデックスを貼りすぎていないか

まとめ

テーブル設計は、データを保存する箱を作るだけではありません。

• エンティティを分ける
• 関係を外部キーや中間テーブルで表す
• 制約で壊れ方を防ぐ
• 正規化と非正規化を理由で選ぶ
• 履歴の必要性を先に考える
• インデックスをクエリから逆算する

設計が良いと、SQLもアプリコードも自然に短くなります。

参考リソース

• PostgreSQL: Constraints
• PostgreSQL: Indexes

次に読む記事

• 正規化の基本
• ブログを例にテーブル設計を練習する
• データベースインデックスの仕組みと最適化

×

PR

×

PR