DB設計の失敗パターンと改善事例 ― 現場でよく踏む地雷とその外し方

概要

アプリケーションの寿命は、DB設計の質にかなり左右されます。コードはリファクタリングで直せますが、テーブル設計の失敗は本番データを抱えたまま直すことになるため、修正コストが桁違いに高くなります。

この記事では、複数の業務システム開発で繰り返し見てきた「DB設計の失敗パターン」を7つに整理し、それぞれの原因と改善策をSQL付きでまとめます。特定の案件・顧客の情報ではなく、一般化した公開情報のみに基づく考察です。

前提環境

• RDBMS: MySQL 8.0 / PostgreSQL 14 を想定
• 対象: 業務システム・SaaSの中規模テーブル（数百万〜数千万行）

失敗パターン1: 何でもNULL許容にする

「とりあえずNULL可にしておけば後で困らない」という発想でカラムを作ると、アプリ側が null 判定だらけになります。さらに「未入力なのか」「0なのか」「該当なしなのか」がスキーマから読み取れなくなり、集計や結合の結果が狂います。

原因は、制約を「後で足せばいい」と考えていること。実際にはデータが溜まってからNOT NULL制約を足すのは、既存のNULL行を埋める作業とセットになり非常に重くなります。

-- Before: 意図が曖昧
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  shipped_at DATETIME NULL,   -- 未発送? 発送日不明?
  discount   INT NULL         -- 割引なし? 未設定?
);

-- After: 意図を制約とデフォルトで表現
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  status     VARCHAR(20) NOT NULL DEFAULT 'pending',
  shipped_at DATETIME NULL,                          -- shipped時のみ入る、と明確化
  discount   INT NOT NULL DEFAULT 0                  -- 割引なし=0
);

NULLに明確な意味（＝まだ発送していない）を持たせられる場合はNULLで良いですが、数量はデフォルト0のほうが集計事故が減ります。

失敗パターン2: 意味を詰め込んだ可変列

option1, option2, option3 のような連番カラムや、カンマ区切りで複数値を1カラムに突っ込む設計は、検索も更新も地獄になります。第一正規形の違反です。

-- アンチパターン: タグをカンマ区切りで格納
CREATE TABLE articles (
  id   BIGINT PRIMARY KEY,
  tags VARCHAR(255)   -- "php,aws,mysql"
);
-- LIKE '%aws%' はインデックスが効かず "awesome" も誤ヒットする

多対多は中間テーブルに分解します。

CREATE TABLE tags (
  id   BIGINT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE article_tags (
  article_id BIGINT NOT NULL,
  tag_id     BIGINT NOT NULL,
  PRIMARY KEY (article_id, tag_id),
  FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id)     REFERENCES tags(id)     ON DELETE CASCADE
);

失敗パターン3: 論理削除の乱用

全テーブルに deleted_at を付けると、WHERE deleted_at IS NULL の付け忘れ障害と、ユニーク制約の破綻（削除済みの同名レコードと衝突）が起きます。

復元要件が無いなら物理削除＋監査ログでよく、復元要件があるならアクティブ行だけを対象にした部分ユニークインデックスを使います。

-- PostgreSQL: 未削除行だけにユニーク制約
CREATE UNIQUE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

MySQLは部分インデックスが無いため、生成列やアーカイブテーブルへの退避で対応します。

失敗パターン4: 主キーに意味を持たせる

メールアドレスや社員番号を主キーにすると、それが変わったとき全外部キーを連鎖更新する羽目になります。主キーはサロゲートキー、業務上の一意性はUNIQUE制約で別途保証します。

CREATE TABLE employees (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 不変のサロゲートキー
  employee_no VARCHAR(20) NOT NULL UNIQUE,
  email       VARCHAR(255) NOT NULL UNIQUE
);

分散環境やマージが絡むなら、時系列ソート可能なUUIDv7も検討の価値があります。

失敗パターン5: 金額・日時の型を雑に選ぶ

金額を FLOAT/DOUBLE で持つと誤差で1円合わず、日時をローカルタイムの文字列で保存するとタイムゾーンで破綻します。

CREATE TABLE payments (
  id       BIGINT PRIMARY KEY,
  amount   DECIMAL(12, 0) NOT NULL,       -- 通貨は固定小数点
  currency CHAR(3) NOT NULL DEFAULT 'JPY',
  paid_at  TIMESTAMP NOT NULL             -- UTCで保存し、表示層で変換
);

金額は DECIMAL、日時はUTC保存・表示層で変換、が鉄則です。

失敗パターン6: インデックス設計が後回し

開発中はデータが少なく速いので気づかず、本番でデータが増えてから急にスロークエリで火を噴きます。WHERE / JOIN / ORDER BY に出る列の組み合わせから複合インデックスを設計し、等価比較の列を先、範囲・ソートの列を後にします。

CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);

EXPLAIN SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

書き込みが多いテーブルにインデックスを貼りすぎると更新が遅くなるため、使われていないインデックスの棚卸しも設計の一部です。

失敗パターン7: 状態遷移を上書きで潰す

status カラムを上書きし続けると、「いつ・誰が・どの状態に変えたか」が残らず、トラブル調査ができません。状態遷移そのものをイベントとして別テーブルに追記します。

CREATE TABLE order_status_events (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  order_id    BIGINT NOT NULL,
  from_status VARCHAR(20),
  to_status   VARCHAR(20) NOT NULL,
  changed_by  BIGINT,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id)
);

現在状態は orders.status にスナップショットとして保持しつつ、変化はイベントで残す。この二段構えが調査性と性能を両立させます。

確認方法

• EXPLAIN / EXPLAIN ANALYZE でインデックスが使われているか
• スロークエリログで改善前後を比較
• 制約違反テスト（NOT NULL / UNIQUE / FK にわざと違反させて弾かれるか）
• 本番相当データ量でのロードテスト

注意点

• 稼働システムのスキーマ変更は、データ移行とダウンタイムの計画が本体です。制約追加・型変更は重いロックを取ることがあります。
• 「正規化が正義」ではありません。参照性能のために意図的に非正規化する場面もあります。大事なのは「なぜその形にしたか」を説明できることです。

まとめ

DB設計の失敗の多くは「後で直せばいい」という先送りから来ますが、DBは本番データを人質に取るため後になるほど直せません。設計時に5分だけ半年後を想像することが、最大の投資になります。