はじめに
最近データベース設計に興味が出てきました。
知識を定着させるために、今回は正規化についてまとめていこうと思います。
正規化とは
リレーショナルデータベースにおいてエンティティの整理をすることで、テーブルを正規形に整えていくこと。
正規形：保持するデータの重複が排除されたデータ形式
正規化すると何がうれしい？
データの重複を排除することで、無駄なデータ領域と面倒な更新処理の発生を防ぐことができる。
関数従属性
Aの値を1つ決めればBの値も1つに決まる関係性のこと（A→Bと表記する）。
正規化とは、テーブルのすべての列が主キーに対して関数従属するように整理していく作業のこと。
正規形の種類
第1〜第5正規形まで存在するが、業務では第3正規形までを考えることが多い。
第3正規形まで達成した時点で、以降の正規形の条件も達成していることが多い。
第1正規化
1つのフィールドには1つの値しか含まない状態。
複数の値を許すと、主キーが各列の値を一意に定めることができなくなる。
1つのフィールドに複数の値が入っている
| 社員ID | 名前 | 趣味 |
|---|---|---|
| 1 | 鈴木 | ゴルフ テニス |
| 2 | 佐藤 | |
| 3 | 田中 | 料理 編み物 |
主キーを定められない
| 社員ID | 名前 | 趣味 |
|---|---|---|
| 1 | 鈴木 | ゴルフ |
| 1 | 鈴木 | テニス |
| 2 | 佐藤 | |
| 3 | 田中 | 料理 |
| 3 | 田中 | 編み物 |
趣味の列をいくつ用意すればいいかわからない
| 社員ID | 名前 | 趣味1 | 趣味2 |
|---|---|---|---|
| 1 | 鈴木 | ゴルフ | テニス |
| 2 | 佐藤 | ||
| 3 | 田中 | 料理 | 編み物 |
以下のように2つのテーブルに分けることで第1正規化できる
社員
| 社員ID | 名前 |
|---|---|
| 1 | 鈴木 |
| 2 | 佐藤 |
| 3 | 田中 |
趣味
| 社員ID | 趣味 |
|---|---|
| 1 | ゴルフ |
| 1 | テニス |
| 3 | 料理 |
| 3 | 編み物 |
第2正規化
部分関数従属が解消されていて、完全関数従属のみのテーブルになっている状態。
- 部分関数従属：複合キーの一部の列に対して従属する列がある状態
- 完全関数従属：主キーを構成するすべての列に対して従属している状態
主キーは{会社ID, 社員ID}だが、会社名は会社IDのみで決まる
| 会社ID | 会社名 | 社員ID | 名前 |
|---|---|---|---|
| 1 | A会社 | 1 | 鈴木 |
| 1 | A会社 | 2 | 佐藤 |
| 1 | A会社 | 3 | 田中 |
| 2 | B会社 | 1 | 高橋 |
| 2 | B会社 | 2 | 吉田 |
| 2 | B会社 | 3 | 松本 |
以下のように分割すると複合キーの一部だけで決まる列を含まなくなる
社員
| 会社ID | 社員ID | 名前 |
|---|---|---|
| 1 | 1 | 鈴木 |
| 1 | 2 | 佐藤 |
| 1 | 3 | 田中 |
| 2 | 1 | 高橋 |
| 2 | 2 | 吉田 |
| 2 | 3 | 松本 |
会社
| 会社ID | 会社名 |
|---|---|
| 1 | A会社 |
| 2 | B会社 |
第3正規形
推移的関数従属が解消されている状態。
- 推移的関数従属：{主キー}→{列A}→{列B}のように、2段階の関数従属が成立している状態
{部署ID}→{部署名}, {会社名ID,社員ID}→{部署名}という2段階の関数従属が成立している
社員
| 会社ID | 社員ID | 名前 | 部署ID | 部署名 |
|---|---|---|---|---|
| 1 | 1 | 鈴木 | 3 | 経理 |
| 1 | 2 | 佐藤 | 2 | 開発 |
| 1 | 3 | 田中 | 1 | 営業 |
| 2 | 1 | 高橋 | 2 | 開発 |
| 2 | 2 | 吉田 | 1 | 営業 |
| 2 | 3 | 松本 | 4 | 人事 |
関数従属している個所を切り出すことで推移的関数従属を排除できる
社員
| 会社ID | 社員ID | 名前 | 部署ID |
|---|---|---|---|
| 1 | 1 | 鈴木 | 3 |
| 1 | 2 | 佐藤 | 2 |
| 1 | 3 | 田中 | 1 |
| 2 | 1 | 高橋 | 2 |
| 2 | 2 | 吉田 | 1 |
| 2 | 3 | 松本 | 4 |
部署
| 部署ID | 部署名 |
|---|---|
| 1 | 営業 |
| 2 | 開発 |
| 3 | 経理 |
| 4 | 人事 |
最後に
第1・第2・第3正規形はそれぞれ、着目する従属関係の対象を変えながら、データの重複や更新時の不整合を防ぐためにテーブルを分離していく作業だと感じた。