データベースの正規化についてまとめました(MySQL, PostgreSQL,...)
はじめに
基本、設計時には気をつけてはいますが、 体系的に学ぶことがなかったので、まとめてみました。
前提
- ここでは、表形式と同じようにテーブルの1箇所をセルと表現する。
- テーブルに下線が入っているものを主キーとする。
非正規系
このようなテーブル構成があったとする。 子供が1つのセルで2人管理されていることが問題となるため、第一正規化を実施する。
| 社員ID | 社員名 | 子供 |
|---|---|---|
| 1 | 工藤 | 一郎 |
| 次郎 | ||
| 2 | 服部 | NULL |
第一正規形
正規化する理由
セルに複数の値を許せば、主キーが各列の値を一意に決定できないためである。 (つまり、次郎を一意に取得することができない) これは主キーの定義に反している。
正規化対応
同一行内での繰り返しの排除する。 1つのセルには、1つの値しか入れない。 子持ちの社員を管理する時に以下のようになる。
| 社員ID | 社員名 | 子供 |
|---|---|---|
| 1 | 工藤 | 一郎 |
| 1 | 工藤 | 次郎 |
| 2 | 服部 | NULL |
この時、子供を一意に決定するためには主キーを全てにする必要がある。 ただ、ここで2つ問題が発生する。
- 子供がいない社員(服部)には、NULLを挿入することになるが、主キーにNULLは設定できない。
- このテーブルが、「社員」と「扶養者」という2つのエンティティ情報を含んで、テーブルの意味や単位を理解しづらい。
上記2つの問題を解決しようとすると、テーブルを分割する必要がある。 社員テーブル
| 社員ID | 社員名 |
|---|---|
| 1 | 工藤 |
| 2 | 服部 |
扶養者テーブル
| 社員ID | 子供 |
|---|---|
| 1 | 一郎 |
| 1 | 次郎 |
これで、以下を一意に特定できるようになった。
- 社員ID->社員名(=工藤)
- 社員ID->子供(=一郎)
- 社員ID->子供(=次郎)
まとめ
正規化とは、テーブルの全ての列が、関数従属性を満たすように整理していくことを目的としている。 (一意に情報を特定できること)
関数従属性について Y=f(X)の式で、X=5と定めると、Y=10となるように、Xに対してYが1つに決まることを、「YはXに従属する」という。
第二正規形
目的
部分関数従属性の排除 部分関数従属性とは、候補キーに従属性がある場合、それを排除する
完全関数従属を目指す 完全関数従属とは、主キーを構成する全ての列に対して、従属があるもの。
問題
以下のようなテーブルがある。これは第一正規化できている。 社員テーブル
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C1 | A商事 | A1 | 工藤 | 30 | B1 | 開発 |
| C2 | B商事 | A2 | 服部 | 27 | B2 | 営業 |
このテーブルの主キーは{会社コード,社員ID}である。 ただし、会社名については、会社コードから特定できるため、会社コードに関数従属している状態である。(部分関数従属性)
正規化対応
第一正規形と同じように、テーブルを分割することで対応する。
会社テーブル
| 会社コード | 会社名 |
|---|---|
| C1 | A商事 |
| C2 | B商事 |
社員テーブル
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C1 | A1 | 工藤 | 30 | B1 | 開発 |
| C2 | A2 | 服部 | 27 | B2 | 営業 |
これにより、部分関数従属の排除が完了した。 また、完全関数従属も満たすことができている。
正規化する理由
正規化前は社員の情報が不明の会社があった時に、主キーの一部に社員IDが含まれているため、テーブルに登録できない。
例)社員テーブルに社員情報不明の会社を追加(エラーになる)
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C1 | A商事 | A1 | 工藤 | 30 | B1 | 開発 |
| C2 | B商事 | A2 | 服部 | 27 | B2 | 営業 |
| C3 | C建設 | NULL | NULL | NULL | NULL | NULL |
また、運用を誤ると、会社コードと会社名の対応がレコードによって異なることが発生する。 「C1:A商事」と「C1:A商社」があったとき、どちらが正しいか分からない。
会社と社員という、異なるレベルの実態(エンティティ)を、きちんと分離する作業である。 現実世界でも、会社があって、そこに社員が属しているのでRDBの世界でもことなるテーブルで表現するのは自然な感じ。
第三正規形
目的
第二正規化したテーブルを改めて見直すと部署コード->部署名という関数従属が成立する。 {会社コード,社員ID}->部署コードも成立する。 {会社コード,社員ID}->部署コード->部署名という二段階の関数従属がある。
これを推移的関数従属と呼ぶ。 第三正規形では、この推移的関数従属を排除することを目的とする。
正規化する理由
部署コードと部署名のペアに注目する。 社員が存在しない時に、会社に部署が存在しているかどうか分からない。 社員が存在しない部署を第二正規形では登録できない。 (主キーをNULLで登録できないため。社員不明の会社をできないのと同じ)
正規化対応
部署テーブル
| 部署コード | 会社名 |
|---|---|
| B1 | 開発 |
| B2 | 営業 |
これにより、問題となっていた社員が存在しない部署を登録できないのも解決することができる。
まとめ
正規化を行うメリット
- データの冗長性が排除され、更新時の不整合を防止できる
- デーブルの持つ意味が明確になり、開発者が理解しやすい
デメリット
- テーブル数が増え、SQLでの結合を多様するのでパフォーマンスが落ちる
Discussion