はじめに
達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ
当方、実務に入って5ヶ月目のエンジニアです。
業務の中で、DBやテーブル構成についての理解を深める必要があると感じたため、上記書籍を読んで得た知見をまとめています。また、図などは前述の書籍から引用させていただきました。
今回は、その中でもっとも重要であると感じた「正規化」について記述していきます。
正規化とは
「正規化」という言葉自体は、リレーショナルデータベース以外の分野でも使われている。
一般的な意味合いとしては、「あるルールに従った形」に整理することをいう。
ざっくりいうと、正規化とは実際にあるデータを「リレーショナルデータベースに適した状態」に整形していくこと。
正規化によって作られたデータの形を「正規形」という。
正規形とは、保持データの冗長性をなくし、一貫性と効率性を保持するためのデータ形式。
正規形は第１〜５まで段階があるが、通常の業務レベルでは第３正規形までを行えればOK。
今回の記事でも、第3正規形までをまとめています。
第１正規形
リレーショナルデータベースのテーブルは、いずれも既に第１正規形を満たす形となっている。
-
第１正規形の定義 スカラ値の原則
「一つのセルの中には一つの値しか含まない」
スカラ（scalar）は、「単一の」という意味
なぜリレーショナルデータベースではスカラ値のテーブルしか認められないのか?
セルに複数の値を許せば、主キーが各列の値を一意に決定できないから。
リレーショナルデータベースでは、主キーが各列の値を一意に決定できなければいけないというルールがある。
たとえば、以下のようなテーブルの場合
table名「扶養者」
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 信二 |
| 000B | 藤本 | |
| 001F | 三島 | 敦 陽子 清美 |
「子」列のセル内に複数のデータが入ってしまっているため、第一正規形の条件を満たしていないということになる。
この状態を解決する方法として、以下の２通りのパターンが挙げられます。
① 列を増やす（列持ち・横持ち）パターン
| 社員ID | 社員名 | 子1 | 子2 | 子3 |
|---|---|---|---|---|
| 000A | 加藤 | 達夫 | 信二 | |
| 000B | 藤本 | |||
| 001F | 三島 | 敦 | 陽子 | 清美 |
② 1行ずつ持つ（行持ち・縦持ち）パターン
| 社員ID | 社員名 | 子 |
|---|---|---|
| 000A | 加藤 | 達夫 |
| 000A | 加藤 | 信二 |
| 000B | 藤本 | |
| 001F | 三島 | 敦 |
| 001F | 三島 | 陽子 |
| 001F | 三島 | 清美 |
様々な理由から、この2つのパターンは後者の方が良いとされている。
しかし後者を選択しても、この状態では主キーを決められないという問題が発生する。
原因
・1レコードの列をすべて特定しようとすると、{社員ID, 社員名, 子} とすべてを指定する必要がある。
・主キーは一部であってもNULLを含んではならないが、子のいないレコードが存在するため矛盾が生じる。
そのため、上記の②のパターンは更にもうひと手間かける必要があります。
table名「社員」
| 社員ID | 社員名 |
|---|---|
| 000A | 加藤 |
| 000B | 藤本 |
| 001F | 三島 |
table名「扶養者」
| 社員ID | 社員名 |
|---|---|
| 000A | 達夫 |
| 000B | 信二 |
| 001F | 敦 |
| 000B | 陽子 |
| 001F | 清美 |
以上のようにテーブルを分けることにより、先程の問題は解決し、なおかつテーブルの意味やレコードの単位がすぐ理解できるようになりました。
関数従属性
リレーショナルデータベースではスカラ値のテーブルしか認められない。
それは前述した「セルに複数の値を許せば、主キーが各列の値を一意に決定できない」という理由からである。
スカラ値だけのテーブルであれば、主キーは各列を一意に決めることができるようになる。
上記の内容を理解する鍵となる概念として、「関数従属性」というものがあります。
Y = f(X)
こちらの関数は「入力（X)に対して出力（Y）を一つに決めるための箱」である。
リレーショナルデータベースでは、このXとYの関係を {X} → {Y} というように表現する。
これは「X列の値を決めれば、Y列の値が一つに決まる」ということを示している。
つまり、先程の関数と同じことを示しているということでもある。
正規化とは、テーブルすべての列が関数従属性を満たすように整理していくこと。
第２正規形
table名「社員」
| 会社コード | 会社名 | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|---|
| C0001 | A商事 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | A商事 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | A商事 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | B科学 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | B科学 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | B科学 | 010A | 渋谷 | 33 | D04 | 総務 |
上記のような構成のテーブルが存在しているとする。
このテーブルはそれぞれのセルがスカラ値によって構成されているため、第一正規形であるといえますが、まだまだ問題があります。
このテーブルの主キーは{ 会社コード, 社員ID }です。そのため、他のすべての列はこのキーに従属しています。
しかし「会社名」列だけは主キーの一部である「会社コード」に従属している。
つまり、以下のような関数従属性があるといえます。
{ 会社コード ｝ → { 会社名 }
このように、「主キーの一部の列」に対して「従属する列」がある場合、この関係を部分関数従属という。
また、これに対して「主キーを構成するすべての列」に従属性がある場合を完全関数従属と呼びます。
第2正規形は、部分関数従属を解消すること
以下は、第2正規形にした後の「社員」テーブルと「会社」テーブルです。
これによって、いずれのテーブルも主キーに完全関数従属することになりました。
table名「社員」
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
table名「会社」
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B科学 |
第2正規形でないと何が悪いのか
- 第２正規形にする前のテーブルでは、社員の情報が不明の会社があった場合、その会社をテーブルに登録することができない
-
運用を誤ると会社コードと会社名の対応がレコードによってマチマチになってしまう危険がある
（例）{ C0001, A商事 } というレコードの他に、{ C0001, A商社 }というデータが登録されてしまう可能性がある。
第2正規形とは、異なるレベルの実体（エンティティ）を分割する作業
このような観点から見ると、第二正規化というのは「会社」と「社員」というそれぞれに異なるレベルの実体（エンティティ）を、きちんとテーブルとしても分離してやる作業。
現実の世界では会社があって、その下に社員がある。
このように階層構造となっているものや概念を分離し、リレーショナルデータベースの世界で表現するといったイメージです。
無損失分解
第二正規化は可逆的な操作である。
テーブルを分割することによって、元のテーブルに戻せなくなってしまうのでは？
という疑問はもっともですが、「第2正規化は必ず正規化する前の状態にテーブルを戻すことができる」ようになっています。
このような「情報を完全に保持したまま」テーブルを分割する操作のことを、無損失分解といいます。
正規化されたテーブルから非正規化された状態のテーブルに戻すには？
→結合(JOIN)を行う
第3正規形
先ほど第2正規化したテーブルをもう一度見ていると、ある問題が浮上します。
table名「社員」
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード | 部署名 |
|---|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 | 開発 |
| C0001 | 000B | 藤本 | 32 | D02 | 人事 |
| C0001 | 001F | 三島 | 50 | D03 | 営業 |
| C0002 | 000A | 斎藤 | 47 | D03 | 営業 |
| C0002 | 009F | 田島 | 25 | D01 | 開発 |
| C0002 | 010A | 渋谷 | 33 | D04 | 総務 |
table名「会社」
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B科学 |
「部署コード」と「部署名」について注目してみると、「社員」テーブルからは開発、人事、営業という3つの部署が存在している事がわかる。
しかし、A商事の部署がこの3つだけである保証はない。
- 「広報」という部署は存在しているが、たまたま社員が一人もいないだけかもしれない
- 新たな部署が追加される場合、社員がいなければ登録ができない
このような問題が発生するのは、「社員」テーブルの中にまだ隠れた関数従属性が残っているため。
現在でいうと
{ 部署コード } → { 部署名 }
という関数従属性が存在していることが見えてくる。
一方で、
{ 会社コード, 社員ID } → { 部署コード }
という関数従属も存在している。全体として考えると
{ 会社コード, 社員ID } → { 部署コード } → { 部署名 }
という2段階の関数従属が存在している。
このように、テーブル内部に存在する段階的な従属関係のことを、推移的関数従属と呼びます。
推移的関数従属の解消
これを解消するためには、第2正規化のときと同じようにテーブル分割を行い、それぞれの関数従属関係を独立させる。
table名「社員」
| 会社コード | 社員ID | 社員名 | 年齢 | 部署コード |
|---|---|---|---|---|
| C0001 | 000A | 加藤 | 40 | D01 |
| C0001 | 000B | 藤本 | 32 | D02 |
| C0001 | 001F | 三島 | 50 | D03 |
| C0002 | 000A | 斎藤 | 47 | D03 |
| C0002 | 009F | 田島 | 25 | D01 |
| C0002 | 010A | 渋谷 | 33 | D04 |
table名「会社」
| 会社コード | 会社名 |
|---|---|
| C0001 | A商事 |
| C0002 | B科学 |
table名「部署」
| 部署コード | 部署名 |
|---|---|
| D01 | 開発 |
| D02 | 人事 |
| D03 | 営業 |
このように、新しく「部署」テーブルを作成することで、非キー列はキー列にのみ従属するようになり、推移的関数従属も解消された。
また、一人も存在しない部署を追加できない、という問題も「部署」テーブルが独立したことによって解消されている。
そして、このテーブル分割も第2正規化のときと同じ「無損失分解」なので、SQLでJOIN句を用いることによって分割前の状態に戻すことも可能です。
まとめ
以降、
- ボイスーコッド正規形
- 第4正規形
- 第5正規形
と続いていきますが、実際の業務ではほぼこの第3正規形でテーブル設計を行えればひとまずは問題ないとされています。
テーブル設計を学ぶことで、設計者がどのような意図を持ってこの構成にしたのか、といったことが以前よりはっきりとわかるようになりました。
冒頭にも紹介しましたが、達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへは非常に読みやすく、わかりやすく解説されており非常に参考になりました。
また学習が深まって来たら、正規化による功罪（パフォーマンス）等の問題についてもまとめていければと思います。