病院の外来予約システムにおける正規化と一意性制約の設計
300床の急性期病院Bは、外来予約システムを刷新する。現行の予約テーブルは1行に予約ID、患者ID、患者氏名、患者電話番号、診療科コード、診療科名、医師ID、医師氏名、予約日時、予約種別、状態、作成日時を保持している。外来は1日約2,500件、年間約60万件、過去5年分をオンラインで保持している。患者マスタは別システムにあり、夜間バッチで連携している。患者の住所や電話番号の変更時に更新漏れが起き、過去の予約行に旧姓や旧電話番号が残る。診療科名の変更（「内科」から「総合内科」）では全予約行の更新が必要になり、夜間バッチが4時間を超えた。医師の異動時にも医師氏名の不整合が発生している。診療科は40、医師は180名である。さらに、同一患者が同一医師に対して同じ日に複数回予約を取れてしまい、月に約30件の苦情が出ている。予約の取消は行を残したまま状態フラグで管理し、取消率は約12%である。画面の検索は「医師ID＋予約日」が中心で、応答が2秒を超えることがあり、「患者ID」単独の検索も1日約800件ある。
1. 予約テーブルに成立している関数従属を列挙し、正規形を判定して分解せよ。予約時点の氏名を残す必要がある場合の扱いにも触れること。
Read the worked answer
関数従属：
- 予約ID → 患者ID, 医師ID, 予約日時, 予約種別, 状態, 作成日時
- 患者ID → 患者氏名, 患者電話番号
- 医師ID → 医師氏名, 診療科コード
- 診療科コード → 診療科名
判定： 主キーが単一列なので第2正規形は満たすが、予約ID → 患者ID → 患者氏名 のような推移的関数従属があり、第3正規形に違反する。診療科名も同様。
分解：
- 予約（予約ID, 患者ID, 医師ID, 予約日時, 予約種別, 状態, 作成日時）
- 患者（患者ID, 氏名, 電話番号）
- 医師（医師ID, 氏名, 診療科コード）
- 診療科（診療科コード, 診療科名）
予約時点の氏名を残す場合： 患者表を参照するのではなく、予約に「予約時氏名」という別属性として持つ。これは正規化違反ではなく、更新しないことを業務ルールとして明記した履歴属性である。属性名を「患者氏名」のままにすると、更新すべき項目と誤解されて同じ問題が再発する。連絡先も履歴が必要なら、患者連絡先（患者ID, 開始日, 電話番号）として期間付きで持つ。
2. 同一患者・同一医師・同一日の重複予約を、アプリケーション任せにせず防ぐ設計を示せ。取消済み予約の扱いを含むこと。
Read the worked answer
業務ルールを一意制約に落とす。
- 「同一日」は予約日時から導出できるが、導出列に一意制約は張れない。予約日（DATE）を列として保持し、（患者ID, 医師ID, 予約日）に一意制約を置く。
- アプリ側の「SELECTして無ければINSERT」は、2つのトランザクションが同時に「無い」と判定でき、READ COMMITTEDでは両方INSERTできてしまう。DB制約を最終防衛線にする。
取消済み予約の除外：
- 取消行を制約対象から外す必要がある。PostgreSQLなら部分一意索引（CREATE UNIQUE INDEX ... ON 予約(患者ID,医師ID,予約日) WHERE 状態 <> 'C'）、SQL Serverならフィルタ付きインデックスを使う。
- 別解として、取消時に一意制約用の列をNULLにする。NULLは一意制約上重複可能なので、取消行が何件あっても衝突しない。ただしこの列の意味をリポジトリに定義しておく。
- 午前・午後で同一医師の複数予約を許すなら、一意キーに時間帯区分を加える。制約を入れる前に業務ルールを確定させる。
- 制約違反時のエラーコードをアプリで捕捉し、患者に「同日の重複予約」と伝える導線を用意する。
3. 「医師ID＋予約日」検索の性能を改善する物理設計を、索引とパーティションの観点で述べよ。患者ID単独検索への配慮も含めること。
Read the worked answer
索引：
- 複合索引（医師ID, 予約日）を作る。等価条件を先、範囲条件を後に置くのが原則で、この順なら医師を絞ってから日付範囲を索引内で走査できる。
- 画面が患者IDや予約種別も必要とするなら、INCLUDE句で列を持たせたカバリングインデックスを検討する。テーブル本体への参照を減らせる。
- 患者ID単独検索が1日800件あるため、（患者ID, 予約日）の索引も候補になる。ただし索引は更新コストとバッファを食うので、頻度と実行計画を見て追加する。索引を増やしすぎるとINSERTの性能が落ちる。
パーティション：
- 年間60万件、5年で300万件であり、単独ではパーティション必須ではない。ただし5年で削除する運用と、古いデータへの参照頻度が低いことを考えれば、予約日による月次パーティションが有効である。削除がDROP PARTITIONで済み、検索時はプルーニングが効く。
- パーティションキーは検索条件に含まれる列を選ぶ。予約日を含まない検索が多いとプルーニングが効かない点に注意する。
- 導入後は統計情報の更新を自動化し、実行計画の変化を監視する。