リレーショナルデータベース設計のよくある間違い

リレーショナルスキーマにおける間違いのほとんどは、突き詰めると一つの間違いに行き着きます。それは、データベースが保証すべきことをアプリケーションに任せてしまうことです。「サービス層で処理する」外部キー、INSERT の前に SELECT でチェックする一意性ルール、ORMは検証するがカラム自体には制約のないステータスフィールド — これらはいずれも、本来それを強制するために設計されたエンジンから制約を取り出し、呼び出すことを忘れなければ実行されるコードに移してしまったものです。その結果、スキーマが決して許可しなかったはずのデータが生まれます。孤立した行、重複したアカウント、マイナスの価格、誰も解釈できないタイムスタンプ。このガイドでは、アプリケーション開発者が PostgreSQL や MySQL で繰り返し犯す設計上の間違いを取り上げ、なぜそれが問題になるのか、そして修正方法を、各ケースの前後のコードスニペットとともに解説します。例では、重要な箇所に最新の Postgres 18 の構文を使用していますが、原則はあらゆるリレーショナルエンジンに適用できます。

リレーショナルデータベース設計における間違いの根本原因：スキーマではなくアプリコードにルールを置くこと

最も影響の大きい間違いは、データのルールをアプリケーションコードのみで強制することです。サービス層の制約は一つのコードパスしか保護しませんが、スキーマの制約はすべてのパスを保護します。API、バックグラウンドジョブ、手動の psql セッション、失敗したデータマイグレーション、そして将来誰かが同じデータベースに対して書く次のサービスまで、すべてです。ルールがアプリにしか存在しない場合、それを迂回した最初の書き込みがテーブルを永続的に破壊します。

これが現代の ORM トラップです。Prisma や Drizzle のようなツールは、データベースレベルの外部キーを一切生成することなく、アプリケーションコード上でリレーションを喜んでモデル化します。そして @default や Zod スキーマはバリデーションのように感じられます。しかし、それはバリデーションではありません。データベースレベルの外部キーなしにリレーションを宣言できる ORM は、作業を省いているのではなく、データ整合性のバグを本番環境に先送りしているのです。そこでは、セッションリプレイ — テストスイートではなく — が最終的にそれを発見します。なぜなら、失敗はスタックトレースとして現れないからです。破損したデータは、存在しないユーザーに紐付いたレビューや、重複した注文として表面化します。つまり、ユーザーが実際に目にする、意味不明な画面として現れるのです。

修正策は、ルールをスキップできない場所まで押し下げることです。PostgreSQL のチェック制約は、たとえば、どのクライアントが書き込んだかに関わらず不正なデータを拒否します。商品価格を正の値に限定するには、テーブル定義に CHECK (price > 0) 制約を使用できます。

-- Before: nothing stops a negative payment or a NULL email
CREATE TABLE payments (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint,                 -- no FK: orphans waiting to happen
  amount  numeric,                -- no CHECK: negatives allowed
  email   text                    -- no NOT NULL, no UNIQUE
);

-- After: the engine guarantees the invariants
CREATE TABLE payments (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
  amount  numeric NOT NULL CHECK (amount > 0),
  email   text NOT NULL UNIQUE
);

外部キーはパフォーマンスへの課税ではありません。存在しないユーザーのレビューとして表示される孤立した行から守る、唯一の手段です。Postgres 18 以降では、書き込みの多いテーブルに対して制約を段階的に適用することもできます。ALTER TABLE で NOT NULL 制約に NOT VALID 属性を設定できるようになり、即時のフルテーブルスキャンなしに制約を追加し、より弱いロックのもとで後から検証することが可能になりました。

正規化を省略する、または過剰に適用する

正規化とは、各事実を一度だけ保存する実践です。第一正形式（1NF）は繰り返しグループや複数値フィールドを持たないことを意味し、第三正形式（3NF）はすべての非キーカラムがキーのみに依存することを意味します。アプリケーションスキーマでは、3NF が合理的なデフォルトです。詳細は Postgres のデータ定義ドキュメント を参照してください。典型的な違反は二種類あります。一つのカラムにカンマ区切りの値を入れることと、payment_1 … payment_12 のように繰り返しカラムを作ることです。

単一カラムにカンマ区切りの値を入れることは、最初の WHERE … LIKE '%,42,%' を書いた時点で後悔する非正規化です。修正策は、より賢い文字列クエリではなく、子テーブルまたは中間テーブルです。

-- Before: tags as a delimited string, phone numbers as columns
CREATE TABLE users (
  id     bigint PRIMARY KEY,
  tags   text,           -- 'admin,beta,vip' — unqueryable, unconstrained
  phone1 text, phone2 text, phone3 text   -- repeating group, runs out
);

-- After: a junction table for tags, a child table for phones
CREATE TABLE user_tags (
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  tag     text   NOT NULL,
  PRIMARY KEY (user_id, tag)
);
CREATE TABLE user_phones (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  phone   text   NOT NULL
);

逆の誤りは過剰正規化です。単一の address を六つの JOIN テーブルに分割したり、特に理由もなく一対一の関係を二つのテーブルでモデル化したりすることです。余分な JOIN はクエリコストと認知コストを増大させます。各事実が一度だけ存在するようになったら、そこで止めましょう。

ビジネスフィールドを主キーとして使用する

email、username、company_name などのビジネスフィールドを主キーとして使用しないでください。サロゲートキーを使用し、自然な値には別途 UNIQUE 制約を追加することで、名称変更やアドレス変更が UPDATE で済み、マイグレーションが不要になります。ビジネス上の値は変化します。そして主キーが変わると、それを参照するすべての外部キーをカスケードで更新しなければなりません。本来 ID であるべきでなかった値が、スキーマ全体のマイグレーションを引き起こすことになります。

サロゲートキーを避けるべきという 2007 年頃のアドバイスは、アプリケーション開発者にとって時代遅れです。現代のデフォルトは、サロゲート主キーと自然キーへの本物の UNIQUE 制約の組み合わせです。安定した識別子と一意性の保証の両方が得られます。

CREATE TABLE companies (
  id   uuid PRIMARY KEY DEFAULT uuidv7(),   -- stable surrogate identity
  name text NOT NULL UNIQUE                 -- natural key still enforced
);

UUID か bigint か、結論

Postgres 18 以降、時系列順に並ぶ識別子のために拡張機能は不要になりました。Postgres 18 では uuidv7() UUID 生成関数が追加されており、その値は時系列でソート可能です。また、バージョン 4 UUID を明示的に生成するための uuidv4() エイリアスも追加されました。UUIDv7 は RFC 9562（2024年5月、RFC 4122 を廃止）で定義されており、タイムスタンプのプレフィックスが埋め込まれているため、新しい行はランダムな v4 UUID のようにインデックス内に散在するのではなく、右端に追記されます。なお、古い Postgres では gen_random_uuid()（v4）がバージョン 13 以降コアに含まれているため、pgcrypto 拡張機能 は不要です。

ID が一つのデータベース内に留まる場合は bigint を、クライアント側やサービス間で ID を生成する場合は uuidv7() をデフォルトとしてください。

意図的なインデックス戦略がない

実際にフィルタリングや JOIN に使用するカラムにインデックスを付けてください。特に、Postgres が自動的にインデックスを付けない外部キーには注意が必要です。ただし、すべてのカラムにインデックスを付けることは避けてください。各インデックスはすべての INSERT と UPDATE において書き込みの増幅を引き起こします。これはアプリケーションスキーマにおける最も一般的なパフォーマンス低下の原因であり、どちらの方向にも間違いを犯しやすいものです。

この落とし穴は Postgres 特有のものです。Postgres は外部キーの参照先（親）側は自動的にインデックス化しますが、参照元（子）カラムはインデックス化しません。参照元カラムのインデックス化が常に必要なわけではなく、インデックスの付け方にも多くの選択肢があるため、外部キー制約を宣言しても参照元カラムのインデックスは自動的に作成されません。（MySQL の InnoDB は自動的に作成します。つまり、この落とし穴は Postgres 固有のものです。）

-- payments.user_id is a FK but unindexed: every join and every
-- DELETE on users does a sequential scan of payments
CREATE INDEX ON payments (user_id);

FK カラムとフィルタカラムにはインデックスを付け、選択性の低いカラムや、そのフィールドでほとんどクエリしないテーブルのインデックスは省略してください。

一つのテーブルが多くの役割を担う

複数のドメインを一つのテーブルで無理やりモデル化すること — 汎用的な Entity-Attribute-Value（EAV）テーブルや、アラート、監査イベント、システムログを一つの notifications テーブルに詰め込むこと — は、スキーマの明確さを犠牲にして見せかけの柔軟性を得ているに過ぎません。型安全性を失い、意味のある制約を適用できなくなり、すべての読み取りがフィルタリングと自己 JOIN の混乱になります。ドメインごとに別々のテーブルを使用し、それぞれが独自のカラム、型、外部キーを持てるようにしましょう。

-- Before: one bag of everything, typed as text, no real constraints
CREATE TABLE entity_attributes (
  entity_id bigint, attr_name text, attr_value text
);

-- After: real tables with real columns and constraints
CREATE TABLE user_alerts  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                           user_id bigint NOT NULL REFERENCES users(id),
                           level text NOT NULL, body text);
CREATE TABLE audit_events (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                           actor_id bigint REFERENCES users(id),
                           action text NOT NULL,
                           occurred_at timestamptz NOT NULL DEFAULT now());

柔軟な属性が本当に必要な場合は、エンジンが提供するすべての制約を無効化する文字列型の EAV ではなく、実際のテーブルに型付きの jsonb カラムを追加することを検討してください。

命名規則の不統一

一つの命名規則を選び、それをすべての箇所に適用してください。Postgres とほとんどの ORM では以下を意味します。

• snake_case（クォートなしの識別子は小文字に変換される）
• テーブル名は複数形か単数形かを一度決めて統一する
• タイムスタンプには created_at / updated_at を使用する
• 名前にメタデータを含めない（tbl_users、col_varchar_address など）
• スペースやクォートを使用しない
• user、order、group などの予約語を裸の識別子として使用しない

命名はテーブル作成時に正しくするのが最もコストが低く、データとクエリがそれに依存してしまった後に変更するのが最もコストが高いものです。

スキーマを最終的なものとして扱う

スキーマは生きたコードであり、一度きりの成果物ではありません。すべての変更をバージョン管理され、レビューされ、ロールバック可能なマイグレーションで管理してください。ORM のマイグレーションツール（Prisma Migrate、Drizzle Kit）またはスタンドアロンのランナーを使用することで、スキーマの履歴を環境をまたいで再現可能にします。

履歴はまた、削除の方法も規定します。他の行からレコードが参照されている場合、ハードな DELETE よりもソフトデリート（deleted_at timestamptz）を優先してください。ハードデリートは子レコードを孤立させるか、ON DELETE CASCADE によって後から提出を求められる履歴を静かに消去してしまうからです。

ALTER TABLE orders ADD COLUMN deleted_at timestamptz;
-- "Active orders" becomes a filter, not a destructive operation
CREATE VIEW active_orders AS SELECT * FROM orders WHERE deleted_at IS NULL;

タイムゾーンなしで日時を保存する

タイムスタンプは timestamp ではなく timestamptz で保存してください。タイムゾーンのないカラムは、サーバーがたまたま「今」と判断した時刻を黙って記録します。そのあいまいさは、サーバーが複数のリージョンにまたがった瞬間に回復不能になります。timestamptz は絶対的な時点を（UTC に正規化して）保存し、セッションのタイムゾーンで表示します。一方 timestamp は、アンカーのない壁時計の読み取り値を保存します。詳細は Postgres の日付/時刻ドキュメント を参照してください。スケジュールや予約については、Postgres 18 でテンポラル制約も追加されました。WITHOUT OVERLAPS と PERIOD を使って指定する、重複しない PRIMARY KEY、UNIQUE、外部キー制約がサポートされています。（テンポラル外部キーは ON DELETE/ON UPDATE カスケードアクションをサポートしていないため、そこでのカスケードには依存しないでください。）

ドキュメントやスキーマテストがない

ドキュメント化もテストもされていないスキーマは、静かに積み重なる間違いです。典型的な症状はマジックナンバーです。status_code が 1〜3 の値を取り、誰かが 4 を追加した日に壊れる。しかも各数値が何を意味するかの記録もない。具体的な対策は以下の通りです。

• COMMENT ON COLUMN でデータベース自体に意図を文書化する
• ER 図をリポジトリに保管する
• マイグレーションをコードと同様にテストする — 適用、ロールバック、そして境界値や不正な入力に対してアサートする

COMMENT ON COLUMN orders.status IS
  'enum: pending|paid|shipped|cancelled — see app/orders/status.ts';

CHECK (status IN (...)) 制約とコメントを組み合わせることで、脆弱な整数値を自己記述的かつ自己強制的なカラムに変えることができます。

まとめ

ここで取り上げたすべての間違いは、同じ修正策に集約されます。データが有効かどうかの権限を、アプリケーションでも ORM でもなく、データベースに持たせることです。それらを前提とするクエリを書く前に、外部キー、NOT NULL、CHECK、UNIQUE 制約を追加してください。まず最も書き込みの多いテーブルから始めましょう。現在コードで強制している不変条件をリストアップし、スキップできない場所であるスキーマへ一つずつ移してください。

よくある質問

UUID 主キーと bigint identity カラムはどちらを使うべきですか？

ID が単一のデータベース内で生成され、クライアントが生成する必要がない場合は bigint identity を使用してください。8バイトで、連続してインデックス化され、最も高速な選択肢です。クライアント側や複数のサービスをまたいで ID を生成し、グローバルな一意性が必要な場合は UUID、具体的には Postgres 18 の uuidv7() を使用してください。UUIDv7 はタイムスタンプのプレフィックスを埋め込んでいるため、ランダムな UUIDv4 とは異なり、ほぼ連続したインデックスの局所性を保ちます。

ORM で外部キーを宣言すると、データベースレベルの制約も作成されますか？

必ずしもそうではありません。Prisma や Drizzle などのツールは、データベースレベルの FOREIGN KEY 制約を生成することなく、アプリケーションコード上でリレーションをモデル化できます。そのため、リレーションは ORM の理解の中にのみ存在し、エンジン内には存在しません。データベース制約がなければ、バックグラウンドジョブ、手動クエリ、または別のサービスが ORM の想定しない孤立した行を作成する可能性があります。リレーション宣言を信頼するだけでなく、マイグレーションが実際に REFERENCES 句を生成していることを確認してください。

PostgreSQL における timestamp と timestamptz の違いは何ですか？

timestamptz は UTC に正規化された絶対的な時点を保存し、セッションのタイムゾーンで表示します。一方 timestamp は、タイムゾーンのアンカーなしに壁時計の読み取り値を保存します。プレーンな timestamp は、サーバーがローカル時刻と判断した時刻を黙って記録し、サーバーが複数のリージョンにまたがると、そのあいまいさは回復不能になります。アプリケーションの日時には timestamptz を使用することで、どこで読み取られても常に一つの明確な瞬間を指す値になります。

Postgres で UUID を生成するために pgcrypto や uuid-ossp 拡張機能は必要ですか？

いいえ。バージョン 4 UUID を生成する gen_random_uuid() 関数は PostgreSQL バージョン 13 以降コアに含まれているため、拡張機能は不要です。PG18 の pgcrypto ドキュメントでは、コア関数を呼び出すだけになったため、独自の gen_random_uuid() を廃止予定としています。Postgres 18 以降では、拡張機能をインストールすることなく、時系列順の識別子のための組み込み uuidv7() と uuidv4() エイリアスも利用できます。