データベースモデリングの実践アドバイス——正規化・エンティティ設計・インデックスの判断基準

モデリングの「ツケ」は必ず後から来る

データベース設計のミスは、実装フェーズではほとんど表面に出ません。問題が露わになるのは、サービスが成長し始めたとき、機能追加のたびにテーブル構造を変更しなければならないとき、あるいはクエリが遅すぎて本番障害が起きたときです。

エンジニア歴3年を超えると、一度は「あのときもっとちゃんと設計しておけばよかった」という後悔を経験するはずです。逆に言えば、この後悔こそがデータベースモデリングを本気で学ぶ最大の動機になります。この記事では、実務で頻繁に遭遇するモデリングの判断ポイントを、具体的な事例を交えて解説します。

正規化の目的と「やりすぎ」のバランス

データベース設計を学ぶとき、まず登場するのが正規化（Normalization）の概念です。第1正規形から第3正規形（あるいはボイス・コッド正規形）まで段階があり、「データの重複を排除し、更新異常を防ぐ」ことが目的です。

しかし現場では「正規化を突き詰めることがゴールではない」という現実に直面します。過度な正規化は、シンプルなデータ取得のためにも複数テーブルのJOINが必要になり、クエリの複雑さと実行コストを増大させます。

正規化の各段階と実務での目安

実務的な指針として、まず3NF（第3正規形）を基準に設計し、パフォーマンス上の理由がある場合にのみ意図的に非正規化（Denormalization）を検討するというアプローチが有効です。非正規化は「計算済みの値を持つ」「集計列を持つ」といった形で意図的に行い、その理由を必ずドキュメントに残しておくことが重要です。

エンティティ設計でよくある3つのミス

データモデルの品質は、テーブルをどう定義するかに大きく依存します。経験3〜7年のエンジニアが設計するテーブルに共通して見られる問題を3つ挙げます。

ミス1：「状態」をカラムで持ちすぎる

たとえば注文管理システムで is_paid、is_shipped、is_cancelled、is_returned という4つのフラグカラムを持つ設計があります。この設計の問題は、状態の組み合わせ爆発とビジネスロジックの複雑化です。「支払い済みかつキャンセル済み」という論理的に矛盾した状態を防ぐ手段がデータ層にありません。

このケースでは status という単一のカラムに ENUM またはコード値を使い、取り得る状態を列挙する設計のほうがシンプルです。さらに状態遷移の履歴が必要なら order_status_histories という履歴テーブルを別途持つことで、現在の状態と過去の遷移の両方を表現できます。

ミス2：汎用すぎる「メタデータテーブル」の乱用

attributes や metadata という名前のテーブルに key と value カラムだけを持たせ、何でも格納しようとする設計をEAV（Entity-Attribute-Value）パターンと呼びます。柔軟に見えますが、型安全性がなく、特定の属性を条件にしたクエリのパフォーマンスが著しく低下し、アプリケーション側での整合性維持が困難になります。

可変長の属性を持ちたい場合は、PostgreSQLの jsonb 型やMySQLの JSON 型を使ったJSONカラムの活用を検討してください。インデックスも部分的に張れるため、EAVよりはるかに実用的です。

ミス3：主キーの選択を軽視する

サロゲートキー（id として自動採番する整数型）とナチュラルキー（電話番号や社員番号など、業務上の意味を持つ値）の使い分けは、設計の根幹に関わります。多くの場合はサロゲートキー（特にUUID or 連番の整数）が安全ですが、UUIDを無計画に使うとインデックスの断片化によりパフォーマンスが落ちる問題があります。

MySQLでは UUID_TO_BIN(UUID(), 1) を使ったソート可能なUUID（UUIDv7相当）の採用や、代わりに BIGINT AUTO_INCREMENT を使う判断が求められます。PostgreSQLでは gen_random_uuid() によるUUIDv4か、最近利用可能になった uuid_generate_v7() を状況に応じて選びます。「とりあえずUUID」ではなく、書き込み頻度・分散環境の有無・インデックスの構造を踏まえた選択が必要です。

リレーション設計で意識すべきこと

テーブル間の関係を正しく表現することは、データの整合性を保ちながら柔軟な拡張を可能にする上で重要です。

多対多の中間テーブルを「薄く」保つ

users と roles の多対多関係を表現する user_roles テーブルには、user_id と role_id だけでなく、assigned_at（付与日時）や assigned_by（付与者）などのメタデータを持たせることもできます。しかしこのテーブルに業務ロジックが集中し始めると、メンテナンスコストが跳ね上がります。中間テーブルは「リレーションの事実」を記録するにとどめ、業務ルールは別のテーブルや上位レイヤーで管理するのが原則です。

論理削除の設計は慎重に

deleted_at カラムを使った論理削除は、データの保持要件がある場合によく使われます。しかしすべてのクエリに WHERE deleted_at IS NULL を加える必要が生じ、インデックスの効きが悪くなり、誤ってソフトデリートを忘れたクエリでゴミデータが混入するリスクが常に残ります。

論理削除が本当に必要かを精査した上で、必要であれば削除済みデータをアーカイブテーブルに移す「パーティショニング + アーカイブ」という設計も検討に値します。

インデックス設計——「張ればいい」は危険

インデックスはクエリを速くするためのものですが、貼りすぎると INSERT / UPDATE / DELETE のたびにインデックスの更新コストが発生し、書き込みパフォーマンスが低下します。インデックスは「必要な場所に必要な分だけ」が原則です。

実務での判断ポイントは以下の通りです。まず WHERE 句や JOIN の結合条件に使われるカラムが最優先です。次に、カーディナリティ（列の値の種類の多さ）が低いカラム——たとえば gender や is_active のようなフラグ——は単独インデックスの効果が薄く、複合インデックスの一部としてのみ有効になるケースが多いです。

複合インデックスを作成する場合は「左端一致の原則」を忘れないでください。(user_id, created_at) の複合インデックスは WHERE user_id = ? のクエリには使えますが、WHERE created_at = ? 単独では使えません。クエリパターンを洗い出してからインデックスを設計することが、パフォーマンスと保守性を両立させる近道です。

設計は「今」だけでなく「3年後」を見て行う

データベース設計で最も難しいのは、現在の要件を満たしながら将来の変更に耐えられる構造を作ることです。完璧な予測は不可能ですが、「このエンティティは将来どう変化しそうか」「この機能が拡張されたらどのテーブルに影響が出るか」をレビュー時に問い続ける習慣が、モデルの品質を長期的に保つ文化を生みます。

テーブル設計の変更は、コードの変更と違ってマイグレーションという本番リスクを伴います。設計段階で1時間の議論を惜しまないことが、リリース後の数日間の障害対応を防ぎます。データベースモデリングは「動けばいい」ではなく「壊れにくく、変えやすく」を目指す技術です。その感覚を養うことが、3年目以降のエンジニアに求められる一段上の設計力です。