正規化は第3以上ではこれが一番簡単でしたのでこの際勉強してみましょう。
業務上、正規化は第3正規型までで十分である。
誰が言い出したかしらないこの一文のせいで多数のエンジニアに学習されること無く埃にかぶっている第4正規化ですが、
ついにパンドラの箱を開ける時が来ました。
(えらそうなこと言ってますが私もそんな意識の低いエンジニアの一人でした)
どんな時に第4正規化に反するレコードができるの？
一つの関連テーブルを使いまわして
複数の「N:Nのリレーション」を表現すると発生します。
関連テーブルとはN:Nのリレーションを表現するために用意されるテーブルのことです。
例えば2つのN:Nの関連があり
- コースと参考図書
- コースと講師
それを下記のような一つのテーブルで表現してしまったとします。
| コース | 参考図書 | 講師 |
|---|---|---|
| AHA | Silberschatz | John D |
| AHA | Nederpelt | John D |
| AHA | Silberschatz | William M |
| AHA | Nederpelt | William M |
| AHA | Silberschatz | Christian G |
| AHA | Nederpelt | Christian G |
| OSO | Silberschatz | John D |
| OSO | Silberschatz | William M |
- コースAHAの関連図書は{Silberschatz,Nederpelt}の2冊です
- コースAHAの講師は{John D,William M,Christian G}の3人です。
※講師によって参考図書が変わるということは今回は起こりません。学生時代に高額な教材を買わされたことは一旦忘れてください。
この独立した2つのN:Nのリレーションを1つのテーブルで表現してしまったために、
直積集合(AHAの場合、(参考図書2冊)✕(講師3人)=(レコード6件))が現れています。これは明らかに冗長です。
このため、データの整合性を取るためにコースに参考図書を1冊増やすたびに講師の数だけレコードを作らないといけません。
また主キーが
- コース
- 参考図書
- 講師
の3つの組にしかしようが無いので(この3組を指定しないと行を一つに特定できません)
どれかがnullのレコードをつくることができません。
講師が決まっていないコースの参考図書を一旦登録したり、参考図書の無いコースを登録することができなくなります。
どうすれば第4正規型に正規化できますか？
独立しているN:Nの関連を個別のテーブルに分解する
この方法に尽きます
コースと参考図書
| コース | 参考図書 |
|---|---|
| AHA | Silberschatz |
| AHA | Nederpelt |
| OSO | Silberschatz |
コースと講師
| コース | 講師 |
|---|---|
| AHA | John D |
| AHA | William M |
| AHA | Christian G |
| OSO | John D |
| OSO | William M |
以上で第4正規化はおしまいです。
第4正規型のちゃんとした定義
恐らくWikipediaと同じ解説になってしまうと思うのでwikipediaを参照してください。
解説にwikipediaと同じテーブルを使っているので
ここまで読んだ人は0から読むより早く理解できると思います
参考
SQLアンチパターン(付録の正規化)
達人に学ぶDB設計 徹底指南書
関係の正規化 - Wikipedia