実務リレーションシップ設計（交差エンティティ・サブタイプ・履歴・代理キー）

リレーションシップ設計では、現在の状態だけでなく、再参加、種別変更、過去時点の照会などを考えます。多対多を表にすること、サブタイプを実装すること、履歴を記録することはそれぞれ別の判断です。表が作れるだけでなく、必要な制約と検索を実現できるかを確認します。

連関エンティティと業務上の一意性

履修の評点は学生にも講義にも単独では属さず、特定の学生による特定回の履修に属します。同じ学生が同じ講義を再履修するなら、学生番号と講義番号だけをUNIQUEにしてはいけません。年度や開講回を含めた実際の識別規則を確かめます。

連関表の主キーを整数の代理キーにしても、同じ業務上の組合せを重複登録してよいことにはなりません。代理キーは識別の安定性や参照列の数を減らすための選択であり、JOINが必ず高速になる保証ではありません。自然キーを選ぶ場合も、変更頻度、NULLの可否、長さ、参照先への変更の波及を比較します。

サブタイプの実装を比較する

親子の表に分けたことだけで第3正規形や完全・排他的な分類が自動的に保証されるわけではありません。たとえば個人と法人の両子表に同じ会員を登録できる設計で、それを許すのか防ぐのかを確認します。全体検索に必ず性能劣化が起きる、という断定も避け、必要な取得列と実行計画で比較します。

履歴には、何の時点を保存するかを決める

商品価格が業務上有効だった期間と、その情報をシステムへ登録した時点は異なります。遡及訂正を扱うなら、有効時刻と登録・変更時刻を区別する必要があります。ここでは有効期間を開始日以上・終了日未満の半開区間として表し、終了日NULLは上限なしとします。

SELECT 単価
FROM 商品単価履歴
WHERE 商品コード = 'P001'
  AND 適用開始日 <= '2026-04-01'
  AND (適用終了日 > '2026-04-01' OR 適用終了日 IS NULL);

この例では4月1日は後の行だけが一致します。BETWEENは両端を含むため、前後の期間の境界を同じ日にすると二重に一致します。また、終了日NULLをBETWEENに入れると比較が不定になるので、上限なしの扱いを明記します。

期間の重複を、並行処理も含めて防ぐ

主キー（商品コード,適用開始日）は、開始日が異なる重複期間を防げません。PostgreSQLでは範囲型と排他制約を使う方法があります。コード列の等値比較をGiSTで扱う場合には、型に応じた演算子クラスやbtree_gist拡張などの設定も必要です。製品を問わず、事前SELECTの結果だけを見てINSERTすると、同時に2件の登録が通る可能性があります。

アプリケーションやトリガーで検査する場合も、同じ商品や社員の更新を直列化するロック、または直列化可能な処理と再試行が必要です。期間重複の禁止と、期間の途切れを禁止することも別の制約です。

演習1：期間の境界

条件：単価100の期間は1月1日以上4月1日未満、単価120は4月1日以上で上限なし。

問い：4月1日の単価は何か。

解答例：120。

根拠：半開区間の上限は含めないため、最初の履歴は一致しない。NULLの終了日は別条件で上限なしとして扱う。

演習2：本務所属の重複

条件：社員は同時に本務部署を1つだけ持つ。同じ社員の履歴を複数処理が同時に登録する。

問い：重複期間の検査に必要な並行制御を説明しよう。（35字以内）

解答例：同じ社員の履歴更新を排他制御して検査する。（21字）

根拠：単に事前確認するだけでは、双方が空きを見て登録できる。

復習で確かめること

例の数値や業務条件を変えて同じ結論になるか確認してください。用語の定義だけでなく、問題文のどの条件から、どの制約・SQL・対策を選んだのかを自分の言葉で説明できれば、次の過去問に進みます。

出典と仕様を確認する

IPA：DBシラバス Ver.4.1

PostgreSQL 18：一意性と外部キー

PostgreSQL 18：範囲型と排他制約

関連するテーマ

概念データモデリングとER図記法・多重度（カーディナリティ）設計

トランザクションACID特性とロック機構・デッドロック回避の設計