DB設計
#08 データベースを設計する

設計レビューのポイント

シリーズ最終回です。これまで学んだエンティティ設計、正規化、ER図、外部キー、非正規化の知識を使って、設計をレビューする視点を整理します。

良い設計のチェックリスト

設計レビューのチェックリスト データ構造 すべてのテーブルに PRIMARY KEY があるか 1NF：1セルに1値か（配列・コンマ区切りがないか） 2NF：部分関数従属がないか 3NF：推移関数従属がないか NULL を安易に許可していないか 意味が重複するカラムがないか 適切なデータ型を使っているか（金額はINT等） リレーション FK に適切な ON DELETE/UPDATE が設定されているか 多対多は中間テーブルで表現されているか 孤立したテーブルがないか（参照される側の確認） CASCADE の範囲が広すぎないか FK カラムにインデックスがあるか 中間テーブルに UNIQUE 制約があるか ER図と実装に乖離がないか パフォーマンス 検索・結合に使うカラムにインデックスがあるか 不要なインデックスがないか スナップショットが必要なデータに対応しているか 集計クエリが重い場合のサマリテーブルは？ 将来のデータ量を見積もっているか セキュリティ・運用 パスワードを平文で保存していないか 個人情報カラムが明確に識別できるか 命名規則が統一されているか created_at / updated_at が全テーブルにあるか ソフトデリートが必要なテーブルを確認したか チェックリストはあくまで出発点。設計の「なぜ」を説明できることが最も重要
図1: 設計レビューのチェックリスト

データ構造の確認

  • すべてのテーブルに 主キー? テーブルの各行を一意に識別するためのカラム。通常は自動で採番される`id`が使われる。 があるか
  • 第1正規形（1NF）? 各セルに1つの値のみを持ち、繰り返しグループをなくした状態。「趣味1・趣味2・趣味3」のような列は1NFに違反している。 ：1セルに1値か（配列・コンマ区切りがないか）
  • 第2正規形（2NF）? 第1正規形を満たした上で、複合主キーの一部にだけ依存する「部分関数従属」をなくした状態。完全関数従属のみになるようテーブルを分割する。 ：部分関数従属がないか
  • 第3正規形（3NF）? 第2正規形を満たした上で、主キー以外の列が主キーに推移的に依存する「推移関数従属」をなくした状態。実用上、3NFを目指すことが多い。 ：推移関数従属がないか
  • NULL を安易に許可していないか
  • 意味が重複するカラムがないか

リレーションの確認

  • すべての 外部キー? 別のテーブルの主キーを参照するカラム。テーブル間の関係を表す。例：投稿テーブルの`user_id`でユーザーテーブルを参照。 に適切なON DELETE/ON UPDATEが設定されているか
  • 多対多の関係は中間テーブルで表現されているか
  • 孤立したテーブルがないか（どこからも参照されていない）

パフォーマンスの確認

  • 頻繁に検索・結合に使うカラムに インデックス? テーブルの検索を高速化するためのデータ構造。本の索引のように、特定の列の値から行の位置をすばやく特定できる。検索は速くなるが、書き込みはやや遅くなる。 があるか
  • 不要なインデックスがないか（書き込み速度に影響する）
  • 変更履歴が必要なデータを正しく管理しているか（スナップショット等）

セキュリティ・安全性の確認

  • パスワードなどの機密情報を平文で保存していないか
  • 個人情報カラムが明確に識別できるか
  • 削除が本当に安全か（CASCADE の範囲が広すぎないか）

よくあるアンチパターン

よくあるアンチパターン一覧 ① EAV パターン（Entity-Attribute-Value） attributes（汎用テーブル） entity_id=1, color, red entity_id=1, size, M entity_id=1, price, 2000 価格が TEXT 型で入っている！ 問題点 ・型安全性がない ・検索・ソートが遅い ・整合性が保ちにくい ・クエリが複雑になる 代替案 JSON カラムを使う、または属性ごとにテーブルを分ける 例: products.attributes JSON（MySQL 5.7+） ② スパゲッティリレーション tableA tableB tableC tableD tableE 問題点 ・変更影響が不明 ・CASCADE が怖い ・テストが困難 解決策 ドメイン（ユーザー系/商品系/注文系）ごとに境界を設ける ドメインをまたぐ参照はアプリ層で管理することも検討 ③ God テーブル users（30カラム超） id, name, email stripe_id, subscription referral_code, referred_by notif_email, notif_sms ... 以下20カラム続く 問題点 ・行が広くなりI/O増大 ・責任範囲が不明確 ・NULL だらけになる 解決策 関心ごとに分割: users / user_profiles / user_preferences / user_payment_info 目安: 1テーブル20カラム以上は分割を検討 ④ status カラムの無秩序な拡張 orders.status の肥大化 'pending' 'paid' 'refund_requested' 'partially_refunded' 'disputed' ... 増え続ける 問題点 ・状態遷移が不明 ・いつ変わったか不明 ・ENUM の変更は ALTER 解決策 状態遷移テーブル（履歴テーブル）を作成 order_status_history(order_id, status, at) いつ・誰が変更したかも記録できる
図2: よくあるアンチパターン一覧

EAV パターン（Entity-Attribute-Value）

動的な属性を管理したいがために「汎用属性テーブル」を作るパターンです。

-- アンチパターン：EAVテーブル
CREATE TABLE attributes (
  entity_id  INT NOT NULL,
  attr_name  VARCHAR(100) NOT NULL,
  attr_value TEXT
);
-- 使用例
INSERT INTO attributes VALUES (1, 'color', 'red');
INSERT INTO attributes VALUES (1, 'size', 'M');
INSERT INTO attributes VALUES (1, 'price', '2000');

問題点:

  • 型安全性がない（price が TEXT で入る）
  • 特定の属性での検索・ソートが非常に遅い
  • データの整合性が保ちにくい
  • クエリが複雑になる

代替案: JSON カラムを使う（MySQL 5.7+, PostgreSQL 9.4+）、または属性ごとにテーブルを分ける。

スパゲッティリレーション

すべてのテーブルがすべてのテーブルと関係し合っている状態です。

問題点:

  • どのテーブルを変えるとどこに影響するか把握できない
  • CASCADE 削除の範囲が予測不能
  • テストが困難

解決策: 関係する範囲を「ドメイン」ごとに区切る（ユーザー系、商品系、注文系）。ドメインをまたぐ参照は外部キー制約なしで管理することも検討する。

status カラムの無秩序な拡張

-- 問題のある設計
orders
  status VARCHAR(50)  -- 'pending', 'paid', 'shipped', 'delivered', 'cancelled',
                      -- 'refund_requested', 'refunded', 'partially_refunded'...

状態が増えるたびにカラムの意味が変わり、状態遷移のロジックが散らかります。

改善案: ENUM で管理する、または状態遷移を専用テーブルで管理する。

-- 改善案：状態遷移テーブル
CREATE TABLE order_status_history (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NOT NULL,
  status     ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') NOT NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  changed_by BIGINT UNSIGNED,  -- 変更したユーザーのID
  FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);

god テーブル

1つのテーブルに何十ものカラムが詰め込まれた状態です。

-- 問題のあるテーブル（カラムが多すぎる）
users
  id, name, email, password,
  address, phone,
  stripe_customer_id, stripe_subscription_id,
  last_login_at, login_count,
  referral_code, referred_by,
  notification_email, notification_sms, notification_push,
  avatar_url, bio, website_url,
  ...（30カラム以上）

改善案: 関心の分離に従ってテーブルを分割する。

users              -- 認証に必要な最小限の情報
user_profiles      -- 表示名、アバター、自己紹介
user_addresses     -- 住所情報
user_preferences   -- 通知設定
user_payment_info  -- 決済情報

命名規則の統一

命名規則を統一することで、スキーマの可読性が大幅に向上します。

テーブル名

推奨: 複数形・スネークケース
  users, products, order_items, product_categories

非推奨: 単数形・キャメルケース混在
  User, Product, OrderItem, productcategory

カラム名

主キー:     id（全テーブル統一）
外部キー:   {参照テーブル単数形}_id（例: user_id, product_id）
日時:       {動詞}_at（例: created_at, updated_at, ordered_at）
フラグ:     is_{形容詞} または has_{名詞}（例: is_active, has_discount）

制約名

主キー:     pk_{テーブル名}
外部キー:   fk_{テーブル名}_{参照テーブル名}
ユニーク:   uq_{テーブル名}_{カラム名}
インデックス: idx_{テーブル名}_{カラム名}

将来の拡張性を考えた設計

設計時に「この機能を追加するとしたら？」を考えておくと、後の改修が楽になります。

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

-- 物理削除ではなく論理削除でデータを残す
ALTER TABLE products ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

-- 削除の代わりにdeleted_atを更新
UPDATE products SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?;

-- 通常の検索はdeleted_atがNULLのものだけ
SELECT * FROM products WHERE deleted_at IS NULL;

ユーザーの削除履歴を残したい、復元機能が必要、などのケースで有効です。

created_by / updated_by の追加

誰がレコードを作成・更新したかを記録するカラムを最初から入れておくと、監査ログとして機能します。

created_by BIGINT UNSIGNED,  -- 作成したユーザーID
updated_by BIGINT UNSIGNED   -- 最終更新したユーザーID

バージョニング

価格改定や商品情報の変更履歴を管理したい場合、履歴テーブルを用意します。

CREATE TABLE product_price_history (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id   BIGINT UNSIGNED NOT NULL,
  price        INT UNSIGNED NOT NULL,
  applied_from DATE NOT NULL,
  applied_to   DATE,
  FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE
);

まとめ：設計は会話である

データベース設計は「正解を1人で考え出す作業」ではありません。

  • 要件を書いた人と「このデータはどう変わりうるか」を話し合う
  • 開発チームと「このクエリは毎秒何回走るか」を議論する
  • 運用チームと「このデータは何年保持するか」を確認する

設計はチームの合意形成のプロセスです。ER図やDDLは、その合意を文書化したものに過ぎません。

良い設計者は「テクニック」を知っているだけでなく、「なぜその設計にしたのか」を説明できる人です。このシリーズで学んだ知識を武器に、チームで議論しながら設計を磨いていきましょう。

シリーズ全8回、お疲れ様でした。

  1. データベース設計とは何か
  2. エンティティを洗い出す
  3. 正規化でデータの重複をなくす
  4. ER図でテーブルの関係を描く
  5. リレーションを実装する — 外部キー
  6. ECサイトを設計する（実践）
  7. 非正規化とパフォーマンスのトレードオフ
  8. 設計レビューのポイント（本記事）