DB正規化 まとめ
はじめに
上記、参考書で正規化の基本的な方法について書かれていたので、纏めた。
個人的には思ったより、今まで業務の中で先輩方に教えていただいた事と相違がなかったので良い復習となった。
先に、個人的結論
正規化とは各エンティティを抽出し、１つのテーブルとして切り離すことでテーブルが持つデータが何のデータなのか明確にすること。
上記を守れば主キーと主キーに従属する従属列のみからなる、完全関数従属なテーブルが出来上がる。
しかし、何でもテーブルを切り離して分割すれば良いということではない。処理時にテーブルの結合が頻繁に起こればパフォーマンスに影響を与える為、要件を鑑みて適切に判断することが大切。
実際、画面上で沢山の値を出す際には、テーブルの結合を避けて膨大な列を持っているテーブルに出会う事もあった。
正規化とは
データベースで保持するデータの冗長性を排除すること。
データの冗長性を排除
複数箇所に同じ内容のデータが存在することを無くす。あるデータを参照したり更新する時に目的のデータが複数箇所に存在すると、どの箇所のデータを参照、更新すべきなのか分からなくなる。また更新時にある箇所のデータのみ更新し忘れたりする可能性がある他、タイムラグによって一時的にデータの不整合が発生してしまう。
正規化のレベル
正規化のレベルには５つあるが、通常はレベル3である第三正規形まであれば通常の業務で対応できる。
第一正規形
・ 1セルには必ず１つの値のみ(スカラ値の原則)
・ 繰り返しデータをカラムの列で管理しない。
・ RDMSでは標準機能に配列型を採用しているが基本的に使わない方が良い。
正規化前
case.1
社員番号が00001の中田社員の子カラムには英寿と浩二の２つの値が格納されてしまっている。
同様に社員番号が00002の中村社員の子カラムには俊輔と憲剛の２つの値が格納してある。
このままでは、中田英寿さんを特定したい時に主キーである社員番号でデータを引っ張っても中田英寿さんだけでなく中田浩二さんまで一緒に取得できてしまう。
| 社員番号 | 社員名 | 子 |
|---|---|---|
| 00001 | 中田 | 英寿 浩二 |
| 00002 | 中村 | 俊輔 憲剛 |
| 00003 | 加藤 |
case.2
スカラ値は守られているが、子供をカラム列で管理してしまっている。子が増えれば子1,2,3に名前の値を入れていく運用となっている。
しかしこれでは子供が4人になったときにカラムを新たに追加する必要がある。さらに全てのレコードに影響を与えてしまうので、子がカラム数未満の場合は必然的にnull、もしくはデフォルト値を入れていく必要がある。
| 社員番号 | 社員名 | 子1 | 子2 | 子3 |
|---|---|---|---|---|
| 00001 | 中田 | 英寿 | 浩二 | |
| 00002 | 中村 | 俊輔 | 憲剛 | |
| 00003 | 加藤 |
正規化後
明確に社員と子テーブルに分割することでテーブルが格納しているデータが明確になった。
また各テーブルでスカラ値の原則が守られている為、特定のデータを参照できるようになった。例えば中田英寿さんのデータを取得したい場合、社員テーブルと子テーブルを社員番号を条件に結合することで社員番号が00001の子カラムの値が「英寿」さんのみを取得できるようになる。
さらに今後子供が増えていっても子テーブルのレコードを足していけば良いだけなので、他のレコードに影響を与えることがない。またnull値も不用意に増えることがない。
・ 社員テーブル
| 社員番号 | 社員名 |
|---|---|
| 00001 | 中田 |
| 00002 | 中村 |
| 00003 | 加藤 |
・ 子テーブル
| 社員番号 | 子 |
|---|---|
| 00001 | 英寿 |
| 00001 | 浩二 |
| 00002 | 俊輔 |
| 00002 | 憲剛 |
第一正規化のメリット
エンティティを分割管理することでエンティティが何のデータを保持しているのか明確になる。
子を持たない加藤さんでもNullの子データを保持する必要がなくなる。主キーを社員番号と社員名の組み合わせ、社員番号と子の組み合わせで各テーブルで一意のデータを特定することが可能。
第二正規形
・ 部分関数従属を無くし、完全関数従属のみのテーブル構成にする。
正規化前
下記の社員テーブルの主キーは会社コードと社員IDである。あくまで社員テーブルなので主キーの会社コードと社員IDの組み合わせに従属しているカラムのみで出来ていれば完全関数従属のみのテーブルと言える。
しかし会社名カラムに注目してみると会社コードがあれば会社名が自然に特定されるので会社名は会社コードに従属しており、社員IDには従属していない。(部分関数従属)
・ 社員テーブル
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 |
|---|---|---|---|---|
| AAAA | AAA株式会社 | 00001 | 川口 | 35 |
| AAAA | AAA株式会社 | 00002 | 中澤 | 34 |
| AAAA | AAA株式会社 | 00003 | 加地 | 33 |
| BBBB | BBB株式会社 | 00001 | 中澤 | 45 |
| BBBB | BBB株式会社 | 00002 | 福西 | 39 |
正規化後
部分関数従属にあった会社コード→会社名の部分を会社テーブルとして分断させる。
分断後の社員テーブルを見てみると会社コードと社員IDを主キーに、他の社員名や年齢は従属列として成立している。
同様に会社テーブルも会社コードを主キーに会社名が従属列として成立しており、完全関数従属なテーブルとなった。
・ 社員テーブル
| 会社コード | 社員ID | 社員名 | 年齢 |
|---|---|---|---|
| AAAA | 00001 | 川口 | 35 |
| AAAA | 00002 | 中澤 | 34 |
| AAAA | 00003 | 加地 | 33 |
| BBBB | 00001 | 中澤 | 45 |
| BBBB | 00002 | 福西 | 39 |
・ 会社テーブル
| 会社コード | 会社名 |
|---|---|
| AAAA | AAA株式会社 |
| BBBB | BBB株式会社 |
第二正規化のメリット
正規化前と比べて会社テーブルが独立しているので仮に社員情報が不明な新規の会社を簡単に追加する事ができる。正規化前の状態でこれを行おうとすると、社員情報が明確になるまで登録を待つ必要があるか、仮の社員情報を入れてレコードを挿入する必要があるかもしれない。
さらに、会社コードがAAAAなのに会社名がBBB株式会社のレコードも登録できてしまう。アプリケーションコードで制御をすれば問題ないが制御ロジックを書き忘れたりした場合、不整合なデータ登録ができてしまう。
なので会社コードと会社名の組み合わせは会社テーブルで保管しておき、会社情報を取り扱う必要のある他のテーブルでは会社コードを使い回すようにすれば上記のような不整合も起こらない。
第三正規形
・ 推移的関数従属を排除し、完全関数従属のみのテーブル構成にする。
正規化前
先ほどの正規化前のテーブルを用いて、さらに部署コードと部署名を追加して場合で考えてみる。
・ 社員テーブル
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名称 |
|---|---|---|---|---|---|---|
| AAAA | AAA株式会社 | 00001 | 川口 | 35 | 01 | 営業 |
| AAAA | AAA株式会社 | 00002 | 中澤 | 34 | 02 | 人事 |
| AAAA | AAA株式会社 | 00003 | 加地 | 33 | 03 | 開発 |
| BBBB | BBB株式会社 | 00001 | 中澤 | 45 | 01 | 経理 |
| BBBB | BBB株式会社 | 00002 | 福西 | 39 | 02 | 総務 |
テーブルはあくまで社員テーブルなので、この場合の主キーは会社コードと社員IDの複合キーとなる。
第二正規形では会社名は会社コードに従属しており、別テーブルである会社テーブルとして独立。
しかし上記のテーブルの場合、これだけでは主キーとそれに従属する従属列だけで構成された完全関数従属のテーブルとは言えない。
部署名称に注目すると、これは会社コードと社員IDに従属しておらず、どちらかと言えば部署コードに従属している。さらに部署コードは会社コードに従属しており、関係性で言えば会社コード→部署コード→部署名となる。(推移的関数従属)
正規化後
会社コードから見て推移的関数従属の関係にあった部署コードと部署名称を切り離して部署テーブルを作成。これで全てのテーブルが完全関数従属のテーブルとなった。
・ 社員テーブル
| 会社コード | 社員ID | 社員名 | 年齢 |
|---|---|---|---|
| AAAA | 00001 | 川口 | 35 |
| AAAA | 00002 | 中澤 | 34 |
| AAAA | 00003 | 加地 | 33 |
| BBBB | 00001 | 中澤 | 45 |
| BBBB | 00002 | 福西 | 39 |
・ 会社テーブル
| 会社コード | 会社名 |
|---|---|
| AAAA | AAA株式会社 |
| BBBB | BBB株式会社 |
・ 部署テーブル
| 会社コード | 部署コード | 部署名 |
|---|---|---|
| AAAA | 01 | 営業 |
| AAAA | 02 | 人事 |
| AAAA | 03 | 開発 |
| BBBB | 01 | 経理 |
| BBBB | 02 | 総務 |
第三正規化のメリット
第二正規化のメリットに通ずるものがあるが、こちらでも各エンティティが明確にテーブルに分けられたので、どんなデータが格納されているのか明確になったのと、仮に社員が一人もいない部署が出来たとしても部署テーブルにレコードを足せば良いだけなのでダミーの社員を作る必要がなくなった。
Discussion