正規化とは
DB 設計にて定義された テーブル（エンティティ） をルールに沿って 分割する こと
嬉しいこと
- 「こういうふうにデータを組み合わせて出力したい」、「新しくカラムを作りたい」みたいな将来的なシステムの変更に強くなる（拡張性）
- 何度も出現するデータをグループ化し、別テーブルにまとめることで領域の節約になる。そしてそのデータの更新コストも減る（冗長性）
予備知識
- 主キーとは、属するレコードの識別子である
- 外部キーとは、参照したい他テーブルのレコードの主キーである
- 部分関数従属とは、主キーが複数ある時に全ての主キーが分からずとも、いづれかの主キーが分かればあるカラムの値が特定できる性質のこと。部分的な関数なので、一部にXならばYみたいな関係があればって感じですね。
- 推移的関数従属とは、主キーではないが、その値が分かると他のカラムの値を特定できてしまう性質のこと。つまり、Xが分かるとY,Zが分かるが、そもそもYが分かるとZが分かってしまうって感じですね。
正規化（第1~第3）
第1正規形
- セル内に複数のデータがあれば、レコードを分割し1つのセルに対し1つの値が入るようにする。
- 分割されたデータがあれば、最低限の主キーを設ける（個人的に）
元テーブル
| 弁当番号 (主キー) |
弁当名 | 材料 | おかず | 原産地番号 | 原産地 |
|---|---|---|---|---|---|
| 1 | 唐揚げ弁当 | 鳥もも肉 大根 |
唐揚げ たくあん |
1 2 |
宮崎県 北海道 |
| 2 | 鮭弁当 | 鮭 卵 大根 |
焼き鮭 卵焼き すりおろし大根 |
2 3 4 |
北海道 茨城県 千葉県 |
正規化後
| 弁当番号 (主キー) |
弁当名 | 材料番号 (主キー) |
材料 | おかず | 原産地番号 | 原産地 |
|---|---|---|---|---|---|---|
| 1 | 唐揚げ弁当 | 1 | 鳥もも肉 | 唐揚げ | 1 | 宮崎県 |
| 1 | 唐揚げ弁当 | 2 | 大根 | たくあん | 2 | 北海道 |
| 2 | 鮭弁当 | 3 | 鮭 | 焼き鮭 | 2 | 北海道 |
| 2 | 鮭弁当 | 4 | 卵 | 卵焼き | 3 | 茨城県 |
| 2 | 鮭弁当 | 2 | 大根 | すりおろし大根 | 4 | 千葉県 |
セルの複数データを分割し、材料番号を主キーとして新しく設けました。
これで弁当番号と材料番号がわかれば、おかずや原産地など全て把握することができます。
第2正規形
部分関数従属のデータを他テーブルに分割する
今回、弁当番号と材料番号の２つの主キーがありますが、弁当番号が分かれば弁当名が分かりますし、材料番号が分かれば材料が分かると思います。
そのため、部分関数従属になっているので、これらをテーブル分割します。
弁当テーブル
| 弁当番号 (主キー) |
弁当名 |
|---|---|
| 1 | 唐揚げ弁当 |
| 2 | 鮭弁当 |
材料テーブル
| 材料番号 (主キー) |
材料 |
|---|---|
| 1 | 鳥もも肉 |
| 2 | 大根 |
| 3 | 鮭 |
| 4 | 卵 |
元テーブル
| 弁当番号 (主キー・外部キー) |
材料番号 (主キー・外部キー) |
おかず | 原産地番号 | 原産地 |
|---|---|---|---|---|
| 1 | 1 | 唐揚げ | 1 | 宮崎県 |
| 1 | 2 | たくあん | 2 | 北海道 |
| 2 | 3 | 焼き鮭 | 2 | 北海道 |
| 2 | 4 | 卵焼き | 3 | 茨城県 |
| 2 | 2 | すりおろし大根 | 4 | 千葉県 |
重複がかなり消えてスッキリしましたね。
第3正規形
推移的関数従属のデータを他テーブルに分割する。（主キー以外に主キーになっていそうなものを探す）
今回、弁当番号と材料番号の２つの主キーから、おかず・原産地番号・原産地が特定できます。
ただ、その中でも原産地コードから原産地は特定できる性質があるので、これらをテーブル分割します。
原産地テーブル
| 原産地番号 (主キー) |
原産地 |
|---|---|
| 1 | 宮崎県 |
| 2 | 北海道 |
| 3 | 茨城県 |
| 4 | 千葉県 |
元テーブル
| 弁当番号 (主キー・外部キー) |
材料番号 (主キー・外部キー) |
おかず | 原産地番号 (外部キー) |
|---|---|---|---|
| 1 | 1 | 唐揚げ | 1 |
| 1 | 2 | たくあん | 2 |
| 2 | 3 | 焼き鮭 | 2 |
| 2 | 4 | 卵焼き | 3 |
| 2 | 2 | すりおろし大根 | 4 |
さらにスッキリしましたね。
まとめ
元テーブルはもはやおかず情報しかないので、おかずテーブルにしちゃいます。
結果、以下の4つのテーブルができてかなりスッキリしましたし、拡張もしやすそうですね。
ただし、取り出す時は結合をする必要があるので、そこそこに抑えないと検索パフォーマンスが落ちてしまうので注意です。
弁当テーブル
| 弁当番号 (主キー) |
弁当名 |
|---|---|
| 1 | 唐揚げ弁当 |
| 2 | 鮭弁当 |
材料テーブル
| 材料番号 (主キー) |
材料 |
|---|---|
| 1 | 鳥もも肉 |
| 2 | 大根 |
| 3 | 鮭 |
| 4 | 卵 |
原産地テーブル
| 原産地番号 (主キー) |
原産地 |
|---|---|
| 1 | 宮崎県 |
| 2 | 北海道 |
| 3 | 茨城県 |
| 4 | 千葉県 |
おかずテーブル
| おかず番号 (主キー) |
おかず | 弁当番号 (外部キー) |
材料番号 (外部キー) |
原産地番号 (外部キー) |
|---|---|---|---|---|
| 1 | 唐揚げ | 1 | 1 | 1 |
| 2 | たくあん | 1 | 2 | 2 |
| 3 | 焼き鮭 | 2 | 3 | 2 |
| 4 | 卵焼き | 2 | 4 | 3 |
| 5 | すりおろし大根 | 2 | 2 | 4 |
即修了（即終了）！！！