HexWolf Blog

正規化のノートを読み返して、自分のテーブル設計と答え合わせした話

はじめに

8ヶ月くらい前、基本情報技術者試験の対策として正規化・主キー・外部キー・関数従属あたりのDB設計の基礎を勉強していました。当時は「第1正規形から第3正規形までの流れ」も「部分関数従属と完全関数従属の違い」もそれなりに言葉で説明できるところまで理解したつもりでいました。

ところが、卒業制作のテーブル設計やER図を実際に自分の手で書くタイミングになって上手く活用できませんでした。そこで当時のノートを見直しつつ、実際に自分が組んだテーブル設計で答え合わせをしてみます。

読者想定

  • 正規化や主キーといったDB設計の教科書的な用語は勉強したけど実際に自分でテーブルを設計するとき不安のある方。
  • Railsを例にしています。ただし考え方自体は特定のフレームワークに依存しません。

正規化のおさらい

  • 第1正規形: 繰り返し項目を分離する
  • 第2正規形: 複合キーの一部だけに従属している列（部分関数従属）を切り出し、完全関数従属の状態にする
  • 第3正規形: 主キー以外の列に従属している列（推移的関数従属）を切り出す

教科書的な例だとこうです。

社員番号部署コード部署名
1001D01営業部
1002D02開発部

部署コードさえ分かれば部署名は一意に決まるので、部署名は「社員番号」ではなく「部署コード」に従属しています。これが部分関数従属です。
これを放置するとデータの重複が起こり、更新の不整合が生じる可能性があるので解消する必要があります。

主キー・外部キーのおさらい

  • 主キー: 一意性・NOT NULL・最小性・不変性を満たす列。テーブルの中で1行を必ず特定できるもの
  • 外部キー: 他のテーブルの主キーを参照する制約。参照先に存在しないデータを挿入できないようにして整合性を保つ

ここまでは当時のノートの内容とほぼ変わらず、今読み返しても間違ってはいませんでした。

実際に自分が設計したテーブルで答え合わせ

vitals-rollでは、Character・Weapons・buff_presets・buffsという4つのテーブルを設計しました。

  • Character: name、race、各種ステータス（dexterity, agility, strength等）、hp、mpなど
  • Weapons: character_idを外部キーに持ち、1キャラが複数の武器を持てるように別テーブルに分離(まだ実装してないけど必要なため)
  • buff_presets: 管理者が用意する共通バフのマスタ（クリティカルレイなど）
  • buffs: キャラごとに実際に適用されているバフ。buff_preset_idを持つが、name・target_status・bonus_value・duration_roundsもbuff_presets側と同じ列を独自に持つ

Characterのステータス列は全部character_idに直接従属しているだけなので1テーブルにまとめて問題なし、Weaponsを分離したのは「1キャラが複数の武器を持てる」という繰り返し項目を分けただけなので、これは第1正規形の考え方を実践できていました。

1番の発見はbuff_presetsとbuffsの関係でした。教科書通りの正規化ならbuff_preset_idさえ持っていれば、名前や補正値はJOINで取ればいいと考えます。ただし人や独自ルールによって自分のbuffは自分で違う管理がしたいという需要があった時にこまるわけです。

buff_presetsは管理者が後から公式のルールに合わせて数値を修正する可能性があります。そのとき、すでにキャラクターに適用済みのバフの数値まで後から変わってしまうと、進行中のセッションでバフ効果が急に変わってしまい都合が悪いです。そのためbuffsテーブルには適用した瞬間の値のスナップショットを独自に持たせる必要がありました。

これは正規化のルールだけを見ると重複した設計に見えますが、実際は意図的な非正規化だと感じました。8ヶ月前のノートにはこの視点がまったくなく、「正規化していれば正しい」という理解で止まっていました。

気づいたこと

  • 正規化のルールを覚えることと、いつそのルールをあえて破るべきかを判断することは別のスキル
  • 教科書の例は「静的なマスタとの関連付け」を説明するには十分だけど、時間とともに変化する値の履歴やスナップショットを残すというケースは別に学ぶ必要があった
  • 基本情報技術者試験レベルの知識は土台としては間違っていなかったものの、実際に自分の手でテーブル設計をする段になって初めてなぜこの設計にするのかを自分の言葉で説明できるようになった

次にやること

  • 当時空欄のままだった「関数従属」のノートを、この記事の内容で埋める
  • ER図全体を見直して、他にもスナップショットパターンが必要な箇所がないか確認する