DB設計は仕事をし始めたときに学習したけど、最近になって再学習する機会ができたので、達人に学ぶDB設計 徹底指南書を読んだ。
これは、テーブルの正規化についてのまとめ。
テーブルの正規化
テーブルの正規化とは、データの冗長性を排除し、一貫性や効率性を保持するようなデータ形式になる形でテーブルを定義すること。そのために、関数従属性という性質を用いる。
関数従属性
関数従属性とは、入力X、出力Yに対して
{X} -> {Y}
Xが決まればYが一意に決まることを示す。このときYはXに従属するという。
テーブル設計では主キーとその他のカラムとの関係を、この性質を用いて整理する。
例えば、社員IDと社員名を持つテーブルがあり、その2つの関数従属性が成り立つなら
{社員ID} -> {社員名}
このように表現され、そのテーブルでは社員名は社員IDに従属する。
正規化と正規形
正規化には段階があり、それを正規形という。正規形には、第1から第5までの正規形と第3と第4の間に位置づけられる正規形の計6つがある。本書では、普通は第3正規形まで理解ですれば十分、としている。
第1正規形
カラムの値のすべてが、それ以上分離できない形の値(スカラー値)となるようにした形。
データベースのテーブルは第1正規形である。DB設計の経験がある人にとってはむしろ、成り立たないケースを考えることが難しく感じるくらい、基本的な形である。
第2正規形
あるテーブルで主キーが複数あるときに
{X1,X2} -> {Y}
X1のみ、もしくはX2のみで成り立つYを、別のテーブルとして定義する。
例えば、会社コードと社員IDを主キーとしたテーブルで
{会社コード,社員ID} -> {社員名}
は自然である。一方で
{会社コード,社員ID} -> {会社名}
会社名は会社コードのみに従属し、社員IDには従属しないと考えるのが自然である。
こういう場合は
{会社コード} -> {会社名}
会社名が会社コードに従属するようなテーブルを定義して元テーブルからは会社名を削除するような形にする。
主キーが複数となるとき、そのすべてによって従属関係が成り立つ関係(社員名)を完全関数従属、一部のみで成り立つ関係(会社名)を部分関数従属という。
第2正規形が成立すると、部分関数従属を持つテーブルはなくなり、完全関数従属を持つテーブルのみで構成された形になる。
第3正規形
あるテーブルの従属関係で
{X} -> {Y}
YがXに従属し、同じテーブルで
{Y} -> {Z}
ZがYに従属するとみなせる関係を、別のテーブルとして定義する。
例えば、会社コードと社員IDを主キーとしたテーブルで
{会社コード,社員ID} -> {部署コード}
{会社コード,社員ID} -> {部署名}
部署コードと部署名がそれぞれ主キーに従属しているとする。
一方で部署コードと部署名は
{部署コード} -> {部署名}
部署名が部署コードに従属すると考えるのが自然である。こういう場合は、この従属を示すテーブルを定義して
{会社コード,社員ID} -> {部署コード} -> {部署名}
会社コードと社員IDを主キーとしたテーブルでは部署コードのみが従属し、部署名は部署コードを経由する形で間接的に従属するような形にする。
ある従属関係が別の従属関係とつながることで間接的な従属関係が成り立つような関係(会社コード,社員IDと部署名の関係)を推移的関数従属という。
ボイス-コッド正規形
第3正規形と第4正規形の間にある正規形。「第3.5正規形」と呼ばれたりもするらしいが、第3正規形を満たしていてもそれらの従属関係が問題となるケースがあり、それを解決するのがこの正規形。
ある2つの従属関係によってその関係性が循環するような形をこの正規形で確認、解決する。
本書の例では
{社員ID,チームコード} -> {チーム補佐}
上記の従属関係があるテーブルで
{チーム補佐} -> {チームコード}
このような関係が業務上必要なときに
{社員ID} -> {チーム補佐}
{チーム補佐} -> {チームコード}
従属関係を変更して循環しないようにする。
第4正規形
ここではまず、主キーのみで成り立つするテーブルに対して新たなカラムを追加するような状況を考える。
例えば、X、Yという2つのカラムを持ち、それらを主キーとしたテーブルがあるとする。
このテーブルに新たなカラムZを追加するとしたときに、ZがXとYに対してどのような関係になるか。
一つは、ZがX,Yに従属する。
{X,Y} -> {Z}
これは第3正規形である。
もう一つはZがX,Yに従属しない、つまりX,Yに対してZが複数値をとる。
このときは、3つの主キーのみを持つテーブルとなる。これもまた第3正規形である。
一方で、例えばあるXの値のデータのZの値を更新したいときは、これらを含むデータを複数更新することになる。つまり、テーブルは正規形になっているのに、冗長性を排除するという正規化の目的は達成されていない。
こういう構成を解決することを検討するのが第4正規形と第5正規形である。
第4正規形ではカラム間のデータの関係を考える。
ここで、XとYを主キーとするテーブルを考える。
この主キーの値はXとYの組み合わせになるが、このことにより、Xの値を一つ決めるとYの値が複数になる。同様にYの値を一つ決めるとXの値は複数になる。
このように、一つの値とそれに該当する複数の値の関係を多値従属性といい
{X} ->-> {Y}
{Y} ->-> {X}
このように表現する。
第4正規形は多値従属性を複数持つようなテーブルを、一つのテーブルに対して多値従属性を1つのみ持つように分離する。
最初の例に戻ると、X,Y,Zを主キーにするテーブルでは、この中のどれか一つのカラムを起点にした多値従属性を考え
{X} ->-> {Y}
{X} ->-> {Z}
このような構成を持つテーブルに分離する。これが第4正規形である。
第5正規形
第5正規形は第4正規形の発展形。
第4正規形で示した
{X} ->-> {Y}
{X} ->-> {Z}
この状況で、ZがYの複数値に従属する必要があるときは
{X} ->-> {Y}
{X} ->-> {Z}
{Y} ->-> {Z}
YとZの多値従属性を持つテーブルを追加する。
振り返ってみて
最初に学習したときは確か第3正規形までだったと思う。第5まであるのは記憶にあったが、ボイス-ゴッド正規形は憶えていなかった。
関数従属性はそれ自体は難しいものではないが、例示しながら説明しようとすると難しい。それでも、一見して当然だと感じるテーブル構造に関数従属性が成り立つことが確認できたのはよかった。
ただ、最近はフレームワークを使うこと多く、必然的にORMを使用する。そうするとORMで使用するオブジェクトやそのクラスをクラス図を使って設計する。このとき特に意識するのは、単一責任の原則、ドメイン駆動設計でのドメインとその境界線など。
こうして設計したあとにORMの定義を書き、DBにテーブルを作成する。
DBにテーブルを作成したあとは、そこからER図を作成してテーブルやテーブル間の関係に問題がないか確認する。正規化のことを意識するのはこのときぐらい。インデックスの定義もこのときに行う。
このやり方が定着しているしているので、ORMを使用していない古いシステムの設計をするときも、同様の方法で行う。
そういう点で、昔ほど正規化を意識しなくなった。