同じ商品の現在名を注文の全行に持つと、名前の変更で更新漏れが起きる（正規化）
正規化は、何が決まれば何が一意に決まるかという関数従属を整理し、同じ事実を何か所も更新する構造を減らす。注文時の商品名のように、現在の値と別の事実を保存することとは区別する。
用語
- 候補キー
- 行を一意に決め、列をどれか外すと一意性を失う最小の列の組。候補キーから主キーを選ぶ。
- 関数従属
- Xの値が同じならYの値も必ず同じになる関係（X→Y）。偶然同じになっている現在のデータではなく、業務上成り立つ規則で判断する。
- 第1正規形
- 各セルをその列で扱う単位の1値にし、繰り返し項目を別の行へ分けた関係。
- 第2正規形
- 第1正規形を満たし、どの候補キーにも含まれない列が、候補キーの一部だけに関数従属しない状態。
- 第3正規形
- 自明でない関数従属X→Aごとに、Xが行を一意に決める列の組（スーパーキー）か、Aが候補キーの構成列となる状態。この例ではカテゴリ名を分離して満たす。
図と例
第1：商品ごとに明細を1行にする → 第2：注文IDで決まる注文日を注文表へ → 第2：商品IDで決まる商品情報を商品表へ → 第3：カテゴリIDで決まる名称をカテゴリ表へ
仮定の例：商品P1の現在名「青いペン」が100件の明細へ複製されている。現在名を「ブルーペン」に変える際、99件だけ更新すると同じ商品の現在名が食い違う。商品表に現在名を1件だけ持ち、明細は商品IDで参照すれば、現在名の変更先は1件になる。購入時の名称を残す明細は、別の履歴として更新しない。
実際のサービス・製品では
Microsoft Access
Microsoftの入門資料は、第1〜第3正規形を表の分割で説明し、同じ事実を複数の場所で変更することによる保守上の問題を示す。
資料を読む
解説
商品マスターや会員マスターを設計するとき、変更する事実ごとに更新先を確認する。重複した文字列をすべて消すことを目標にせず、その列が「現在の商品名」か「注文時に確定した商品名」かを決める。注文時の名前や単価は履歴として明細へ保存できる。読み取り用に意図的な非正規化を選ぶ場合は、正本とコピーの更新方法も決める。
仮に、注文内で同じ商品は1行だけとし、注文ID・商品ID・数量・注文日・現在の商品名・カテゴリID・現在のカテゴリ名を1つの表に持つ。候補キーは行を一意に決める最小の列の組で、この例では（注文ID, 商品ID）だけとする。商品IDが決まれば現在の商品名が決まる関係を、商品ID→商品名という関数従属で表す。第1正規形では商品1・商品2のような繰り返し列をやめ、商品ごとに行を分け、各セルを扱う単位の1値にする。第2正規形では、候補キーの一部だけで決まる非キー列を分離する。この例なら注文日を注文表、商品名とカテゴリ情報を商品表へ移し、明細には注文ID・商品ID・数量を残す。第3正規形へ進めるには、商品表に残った商品ID→カテゴリID→カテゴリ名という依存を整理し、カテゴリ名をカテゴリ表へ移す。分割後もキーで結合して元の情報を復元できるようにし、参照関係は外部キーなどで守る。
トレードオフ
- 現在の事実を正規化した表で持つ
- 得るもの：同じ事実の更新先を減らし、更新漏れを防ぎやすい
- 払うもの：一覧の表示には複数の表の結合が必要になる場合がある
- 読み取り用の表へ意図的に非正規化する
- 得るもの：画面や集計で必要な情報をまとめて読める
- 払うもの：コピーの更新・再構築と、反映が遅れる場合の扱いを決める必要がある
さらに学ぶ
チェック
仮定：商品IDから現在の商品名が一意に決まり、注文時の名前は別途保存する。同じ商品の現在名が明細ごとに食い違う問題を減らす設計は？
- 現在の商品名を商品表に1件持ち、明細は商品IDで参照する
- 明細ごとの現在名を独立に編集できるようにする
- 商品IDを外して、現在の商品名だけで明細を結び付ける
解答と解説
現在の商品名を商品表に1件持ち、明細は商品IDで参照する
現在名は商品IDで決まる事実なので、商品表にまとめると変更先を1件にできる。明細ごとの編集は食い違いを残し、名前だけの結び付けは名称変更で参照が不安定になる。注文時に確定した名前は別の事実なので、履歴として明細に残してよい。