02. リレーショナルモデルとスキーマ

アプリケーションのデータを表へ入れる前に、「何を同じものとして識別するか」「どの状態を正しいとみなすか」を決める必要があります。リレーショナルモデルは、この判断を行と列の見た目ではなく、集合、属性、キー、制約という形で表現するための土台です。

この章では、後続のストレージやインデックスへ進む前に、論理的なデータ設計を整理します。

この章で答える問い

• 関係、タプル、属性は、SQLの表、行、列と完全に同じなのか
• 主キー、外部キー、一意制約は何を保証するのか
• 自然キーと代理キーは、どのような基準で選ぶのか
• 正規化は何を防ぎ、非正規化は何を引き受けるのか
• スキーマ設計とインデックス設計を分ける必要があるのはなぜか

関係は値の集合である

リレーショナルモデルでは、関係を同じ属性を持つタプルの集合として扱います。各属性にはドメイン、つまり取り得る値の範囲があります。

リレーショナルモデル	SQLで近いもの	注意点
関係	表	SQLの表は重複行を許し得る
タプル	行	行には物理的な位置や暗黙の順序を期待しない
属性	列	列には型とNULL許可などがある
ドメイン	データ型と制約	SQLの型だけでは業務上の範囲を表し切れないことがある

数学的な関係は集合なので、同じタプルが重複せず、タプルの順序もありません。一方、SQLは実務上の都合からバッグ、つまり重複を許す多重集合として振る舞う場面があります。SELECTの結果に順序が必要ならORDER BYを明示し、重複を除きたいならDISTINCTなどを指定します。

暗黙の順序を使わない

現在たまたま主キー順に返っていても、実行計画、並列実行、ページ配置が変われば順序は変化します。ORDER BYのない結果に順序の保証はありません。

NULLと三値論理

NULLは空文字列や0ではなく、「値が存在しない、または未知である」ことを表します。NULLを含む比較の結果は、真／偽だけでなく不明になることがあります。

SELECT NULL = NULL;       -- unknown
SELECT NULL IS NULL;      -- true
SELECT 1 NOT IN (1, NULL); -- false
SELECT 2 NOT IN (1, NULL); -- unknown

WHERE句は真になった行だけを残します。偽と不明はどちらも除外されるため、NULLを含むNOT INや外部結合では直感と異なる結果になりがちです。

NULLを許可するかどうかは、単なる実装上の好みではありません。「未入力」「適用外」「不明」「削除済み」など複数の意味を一つのNULLへ詰め込むと、問い合わせと制約が難しくなります。

行を識別するキー

候補キーと主キー

候補キーは、行を一意に識別でき、不要な属性を含まない最小の属性集合です。候補キーが複数ある場合、そのうち一つを主キーとして選び、残りも必要ならUNIQUE制約で守ります。

たとえばユーザーを次の属性で表すとします。

CREATE TABLE users (
  id            BIGINT PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  display_name  TEXT NOT NULL
);

idとメールは、どちらも一意なら候補キーです。主キーにidを選んだからといって、メールの重複を許してよいわけではありません。業務上の一意性は別途UNIQUEで表現します。

自然キーと代理キー

自然キーは業務上すでに意味を持つ値です。国コード、商品コード、メールアドレスなどが候補になります。代理キーはDB上の識別のために導入する値で、連番やUUIDなどが使われます。

観点	自然キー	代理キー
業務上の意味	ある	原則としてない
値の変更	業務変更の影響を受ける	通常は変更しない
外部キーの幅	複合・長文字列になり得る	短く統一しやすい
一意性	そのまま表現できる	自然キー側にもUNIQUEが必要

代理キーを採用しても、業務上の重複防止はなくなりません。たとえばusers.idとは別にusers.emailへUNIQUE制約が必要です。逆に自然キーが短く安定しているなら、必ず代理キーを追加する必要もありません。

制約は正しい状態の境界である

DB制約は、どの書き込み経路から来た変更にも適用されます。Web APIのバリデーションは分かりやすいエラーを返すために重要ですが、バッチ、管理ツール、別サービスなどすべての経路を永久に網羅する保証にはなりません。

代表的な制約を注文スキーマへ適用します。

CREATE TABLE customers (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL
);

CREATE TABLE products (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  price       INTEGER NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(id),
  status       TEXT NOT NULL
               CHECK (status IN ('pending', 'confirmed', 'cancelled')),
  ordered_at   TIMESTAMPTZ NOT NULL,
  UNIQUE (customer_id, id)
);

• NOT NULLは値の欠落を防ぐ
• UNIQUEは候補キーや業務上の一意性を守る
• 検査は行単体で評価できる値域を守る
• 外部キーは参照先行の存在を守る
• 主キーはNOT NULLかつUNIQUEな代表キーになる

外部キーが表すもの

外部キーは、参照元の値が参照先に存在することを保証します。削除・更新時の動作は、RESTRICT、CASCADE、SET NULLなどから業務上のライフサイクルに合わせて選びます。

CASCADEは便利ですが、「顧客を削除したら過去の注文も消す」という判断が法務・監査要件に合うとは限りません。参照整合性と削除ポリシーは別々に検討します。

関数従属性と正規化

属性集合Xの値が決まると属性集合Yの値が一意に決まるとき、X → Yという関数従属性（関数従属性）があるといいます。

次の未整理なデータを考えます。

order_id	ordered_at	customer_id	customer_name	product_id	product_name	quantity
1001	2026-08-20	10	Alice	7	キーボード	1
1001	2026-08-20	10	Alice	9	マウス	2

ここには次のような依存があります。

• order_id → ordered_at, customer_id
• customer_id → customer_name
• product_id → product_name
• order_id, product_id → quantity

一つの表へすべて入れると、同じ顧客名や商品名が注文行ごとに重複します。

更新・挿入・削除の異常

• 更新異常：顧客名を変えるとき、過去の全注文行を更新する必要がある
• 挿入異常：まだ注文されていない商品を登録できない
• 削除異常：最後の注文行を消すと、商品情報まで失われる

正規化は、これらの異常が起きる依存関係を別の関係へ分解する考え方です。

erDiagram
    CUSTOMERS ||--o{ ORDERS : 注文する
    ORDERS ||--|{ ORDER_ITEMS : 含む
    PRODUCTS ||--o{ ORDER_ITEMS : 明細に現れる

    CUSTOMERS {
        bigint id PK
        text name
    }
    ORDERS {
        bigint id PK
        bigint customer_id FK
        timestamp ordered_at
    }
    PRODUCTS {
        bigint id PK
        text name
    }
    ORDER_ITEMS {
        bigint order_id PK,FK
        bigint product_id PK,FK
        integer quantity
    }

第1〜第3正規形の直感

正規形の厳密な定義は依存関係を使いますが、最初は次の直感で捉えます。

1. 第1正規形（1NF）：一つのセルへ繰り返し項目を詰め込まず、関係として扱える値にする
2. 第2正規形（2NF）：複合キーの一部にだけ依存する属性を分離する
3. 第3正規形（3NF）：キーではない属性を経由して決まる属性を分離する

ORDER_ITEMSのキーが(order_id, product_id)なのにproduct_nameが入っていると、product_nameはproduct_idだけに依存します。PRODUCTSへ分けることで、商品の名称を一か所で管理できます。

「一つのセル」の意味

1NFを「文字列を絶対に分割できないこと」と理解すると混乱します。住所や日時も内部的には分割できます。重要なのは、現在のデータモデルで値を一つのドメインとして扱い、集合をカンマ区切り文字列へ隠さないことです。

非正規化は意図的な重複である

正規化されたスキーマでも、読み取り要件のために値を重複して保持する場合があります。

例として、注文確定時の商品名と単価をORDER_ITEMSへスナップショットとして保存する設計があります。これは単なる性能対策ではなく、「商品マスタが変更されても、購入時点の明細を残す」という業務上の意味を持ちます。

非正規化するときは、最低限次を決めます。

1. どの値を正とするか
2. 複製された値をいつ更新するか
3. 更新途中の不一致を許容するか
4. 再構築・修復できるか
5. 読み取りの改善が書き込み複雑性に見合うか

「結合を避けたい」だけで重複を導入すると、書き込み経路が増え、トランザクションや非同期更新の設計まで複雑になります。

論理設計と物理設計を分ける

論理設計は、データの意味と正しい状態を決めます。物理設計は、その論理モデルを特定の処理負荷で効率よく扱う方法を決めます。

論理設計	物理設計
関係の分割	表の物理配置
主系/外部キー	B+木、ハッシュ、LSM-木
NOT NULL、検査、UNIQUE	クラスタ化/セカンダリインデックス
トランザクションで守る不変条件	分割、圧縮

「検索が遅いから正規化をやめる」と決める前に、実行計画、インデックス、集約表、キャッシュなど別の手段を確認します。逆に、理論上きれいな正規化を守るために、必要な履歴やスナップショットを失ってもいけません。

よくある誤解

「主キーがあれば重複データは存在しない」

代理キーが異なれば、業務上同じメールや注文番号を持つ行を複数登録できます。業務上の候補キーにもUNIQUE制約が必要です。

「外部キーは結合を速くする」

外部キーの目的は参照整合性です。参照元列にインデックスが自動作成されるかどうかは製品によって異なり、結合性能は別途確認する必要があります。

「正規化されたスキーマは常に最速である」

正規化は更新異常を減らす論理設計であり、特定クエリのI/Oを最小にする規則ではありません。性能は処理負荷と物理設計を含めて評価します。

まとめ

• 関係は順序を持たないタプルの集合であり、SQLの表とはバッグ意味論やNULLの点で差がある
• 候補キーは行を一意に識別する最小の属性集合である
• 代理キーを使っても、業務上の一意性にはUNIQUE制約が必要である
• 制約はすべての書き込み経路に対して正しい状態の境界を作る
• 正規化は関数従属性を整理し、更新・挿入・削除の異常を減らす
• 非正規化では、重複値の正本と同期方法を明示する
• 論理設計とインデックスなどの物理設計は、分けて考えてから接続する

確認問題

1. ORDER BYのないSELECT結果へ順序を期待できない理由を説明してください。
2. 代理キーを採用しても、自然キーへUNIQUE制約が必要になる例を挙げてください。
3. 顧客名を注文明細へ保存した場合に起きる3種類の更新異常を考えてください。
4. 注文時点の商品単価をORDER_ITEMSへ保存することは、単なる重複でしょうか。業務上の意味も含めて説明してください。
5. アプリケーションのバリデーションだけでなく、DB制約も置く利点を説明してください。

参考資料

• E. F. Codd, “A Relational Model of Data for Large Shared Data Banks”
• PostgreSQL Documentation: Constraints
• PostgreSQL Documentation: Table Expressions

次章では、ここで設計した表がファイル内でどのようにページとレコードへ変換され、バッファプールへ読み込まれるかを追跡します。