E-R図とキー｜業務ルールからエンティティ・関連・多対多を設計する

顧客、注文、商品という名詞を三つの表に分けただけでは、業務を正しく保存できるとは限りません。一つの注文に複数の商品が入り、同じ商品を別の注文でも扱うなら、その関連にも数量や価格が必要です。何を一件として識別し、どの関係を残すかを業務ルールから決めます。

この記事で理解すること

• エンティティと属性を、保存する事実と識別条件から決める。

• 主キー・候補キー・外部キーを区別し、重複と参照の整合を説明する。

• 関連の最小・最大件数を読み、多対多を交差エンティティへ分ける。

架空の文具卸会社の受注業務を例にします。顧客は注文を何度でも行い、一つの注文は一人の顧客に属します。一つの注文は一つ以上の明細を持ち、各明細は一つの商品を扱います。同じ商品を同じ注文に複数行記載できる業務とします。

エンティティと属性：何を一件として保存するか

エンティティは業務で識別して管理する対象です。E-R図のエンティティタイプは「顧客」「注文」のような対象の種類を表し、一人の顧客や一件の注文がその実体です。属性は顧客名、注文日など、その対象に関する項目です。

同じ名称の対象でも別の実体である場合があります。顧客名だけでは同姓同名や社名変更を扱えません。識別に使う値と、表示・連絡に使う値を区別します。画面の一つの欄がそのまま一つの表になるとも限らず、業務の事実と繰返しを読み取ります。

数量や受注単価は「この商品を、この注文で、どれだけ・幾らで受けたか」という事実です。商品そのものの属性に置くと、注文ごとの違いを残せません。どの対象に属する属性かは、値を決定する条件を使って考えます。

候補キー・主キー・代替キー

候補キーは、実体を一意に識別でき、不要な属性を含まない属性の組です。複数の候補キーがある場合、そのうち一つを主キーとして選びます。選ばれなかった候補キーは代替キーとして一意制約等で保護できます。主キーには一意性とNULLを許さない条件が必要です。

複数属性を組み合わせるキーを複合キーと呼びます。注文明細の主キーを注文IDと行番号にすると、別の注文で同じ行番号を使えます。一方、同じ注文内の同じ行番号を二度使うことはできません。行番号だけを主キーにする設計とは制約が違います。

業務上のコードを使う自然キーと、内部で採番する代理キーも区別します。顧客IDのような代理キーを付けても、業務上重複させてはいけない顧客コードの制約は別に必要です。採番したIDが一意であるだけでは、同じ顧客の二重登録を自動で防げません。

内部IDには整数を使う例です。図では型の細部を省きますが、外部キーの型も参照先と整合させます。概念モデルは実装型を決める前にも使えますが、論理表へ落とす段階でキーと制約を具体化します。

外部キーと参照整合性

注文の顧客IDは顧客表の顧客IDを参照する外部キーです。一人の顧客が複数回注文できるため、注文表の顧客IDは重複して構いません。外部キーを一意にしてしまうと、一人につき一注文しか保存できなくなります。

参照整合性は、参照する値に対応する行が参照先に存在することなどを守る条件です。この業務では注文の顧客は必須なので、注文の顧客IDはNOT NULLと外部キーの両方が必要です。外部キーだけではNULLを禁止するとは限りません。

参照される顧客を削除するときは、拒否する、関連データも削除する、参照を解除するなどの方針があります。この例は注文履歴を残すため、顧客の物理削除を拒否し、取引停止の状態を管理します。ON DELETE CASCADEを付ければ常に正しい、とは判断しません。

関連の多重度：最小件数と最大件数を読む

関連は対象同士の結び付きです。多重度には「幾つまで」と「必ずあるか」の両方があります。顧客から注文を見れば0件以上、注文から顧客を見ればちょうど1件です。新規登録だけの顧客が存在できるなら、顧客に注文が必須という図は業務と合いません。

一対多では、通常「多」側へ「一」側を参照する外部キーを置きます。注文の顧客IDがその例です。一対一を表す場合は外部キーに一意制約が必要になることがあり、最大件数と存在必須を別々に実装します。

確定注文に一つ以上の明細があるという規則は、明細から注文への外部キーだけでは保証できません。明細がゼロの注文も親表へ保存できるためです。登録処理のトランザクションや確定時の検査など、複数行をまたぐ業務規則の実装を別に考えます。

多対多を交差エンティティで表す

注文には複数商品が入り、商品は複数注文に現れます。注文と商品の関係は多対多です。関係を直接一つの外部キーで表すと、商品を一つしか置けないか、商品の繰返し列を増やす設計になってしまいます。そこで注文と商品を結び付ける注文明細を置きます。

顧客(顧客ID PK, 顧客名)
注文(注文ID PK, 顧客ID FK 必須, 注文日)
商品(商品ID PK, 商品名, 標準価格)
注文明細(注文ID PK/FK, 行番号 PK,
  商品ID FK 必須, 数量, 受注単価)

注文明細は交差エンティティで、注文と商品の関連に属する数量・受注単価を持ちます。ただし、この業務では同じ商品を一注文に複数行記載できます。そのため注文IDと商品IDを主キーにすると必要な繰返しを禁止してしまい、注文IDと行番号を識別に使います。

履歴：現在値と取引時点の値を分ける

商品表の標準価格が変わっても、過去の受注金額は変わってはいけません。このため明細に受注時点の単価を保存します。単価と数量から求める明細金額を保存するか計算するかは、丸め・税・監査要件なども含めて決めます。

商品名も変更されるなら、履歴画面に現在の商品名を出すのか、注文時点の名称を残すのかを要件で決めます。「同じ項目は一切重複保存しない」という判断だけでは履歴要件を満たせません。意図的な時点情報と、同じ事実の無管理な二重保持を区別します。

正規化、スーパータイプ・サブタイプ、DDLの詳しい設計は別テーマです。ここではまず一件の識別と関連の条件を確定します。SQL記事ではこの構造を前提に、複数表をどう結合して集計するかを扱います。

モデルの確認：具体的な登録と変更を試す

図を描いた後は、未注文顧客、一人の複数注文、一注文の複数商品、同一商品の複数行、商品の価格改定、参照中の顧客削除という具体例を当てはめます。図の記号を覚えるだけでなく、必要な事実を保存でき、禁止する状態を防げるかを点検します。

図法によって多重度の記号は異なります。0..*、1..*、カラスの足などを読み替える際は、両端の最小・最大件数を文章で確認します。本文で明示されない重複禁止や必須条件を、都合よく推測して追加しないことも大切です。

演習1：未注文の顧客

条件：登録した顧客が、まだ一度も注文していないことを許す。

問い：顧客から見た注文の最小件数は幾つか。

解答例：0件。登録後に注文がなくても存在できるため。

根拠と誤答の確認：最大が多数であることと、最小が1であることは別の条件です。

演習2：外部キーの重複

条件：顧客101が注文201と202を行う。

問い：注文表の顧客IDを一意にすべきか。

解答例：すべきではない。同じ顧客を複数の注文から参照するため。

根拠と誤答の確認：注文を識別する主キーと顧客を参照する外部キーを混同しません。

演習3：明細の識別

条件：同じ注文で同じ商品を複数行記載できる。行番号は注文内で一意。

問い：明細の主キーに適する組は何か。

解答例：注文IDと行番号。注文内の各行を一意に識別する。

根拠と誤答の確認：注文IDと商品IDでは同一商品の複数行を保存できません。

演習4：必須の顧客

条件：各注文は必ず存在する一人の顧客に属する。

問い：顧客IDに必要な制約を答える。

解答例：顧客表への外部キーとNOT NULL。参照先の存在と値の必須を守る。

根拠と誤答の確認：外部キーだけでNULLの禁止まで保証されるとは限りません。

演習5：価格改定

条件：商品表の標準価格を変更しても、過去の受注金額は維持する。

問い：受注単価をどこに保存するか。

解答例：注文明細に受注時点の単価として保存する。

根拠と誤答の確認：現在の標準価格との結合だけで再計算すると履歴が変わってしまいます。

演習6：明細の存在

条件：明細表に注文への外部キーを設定した。

問い：全注文に一明細以上あることまで保証できるか。

解答例：できない。明細がゼロでも注文行を作れるため、確定時の検査等が必要。

根拠と誤答の確認：子から親への参照整合性と、親が必ず子を持つ条件は別です。

参照資料とこの記事の範囲

事例・図・演習は教材用に独自に作成しました。技術仕様とIPAの公開資料を照合し、特定年度の問題本文を前提にせず学べる構成にしています。

PostgreSQL 主キー・外部キー・一意制約

PostgreSQL 表の基本

IPA APシラバス

関連テーマを続けて学ぶ

IP・サブネット・経路・NAT｜宛先と変換前後を追って通信を理解する

DNSと名前解決｜FQDN・レコード・キャッシュ・TTLを一つの流れで理解する

SQLの結合と集計｜JOIN・NULL・副問合せを結果表から理解する

記述式の設問と本文根拠の読み方