はじめに
最近Kindleでの読書に目覚めました。
「何かデータベース周りの知識が足りていないな〜」と日頃感じていたこともあったので、社内で評判の良かった書籍『達人に学ぶDB設計 徹底指南書 ～初級者で終わりたくないあなたへ』を手にとりました！
そこで「正規化」について触れた箇所がありました。
「論理設計とは？」 「正規化とは？？」 といった状態だったのですが、
読み進めていくうちに、「正規化」はデータベース設計時に無意識に行っている作業だと気づきました！
さらに本書では、私を含め多くのエンジニアも「正規化」についてあまり理解せずに行っている作業であることが言及されていました。
折角なのでこの機会に「正規化」についてしっかり理解したいと思ったので、記事まとめました！
それではいってみよ〜
正規化とは何か
そもそも正規化とは何なのか。
本来であれば論理設計、物理設計の違いの説明から入るべきなのですが、
今回は正規化にフォーカスを当てていきたいので、ざっくり論理設計の流れについて説明します。
論理設計とは
まず論理設計には四つのタスクがあります。
1. エンティティの抽出
2. エンティティの定義
3. 正規化
4. ER図の作成
上から順に解説していきます！
エンティティの抽出
ここでは現実世界に存在するエンティティ（実体）を抽出することを指します。
開発にあたり、システムにはどのようなデータが必要かを考え、「社員」や「部署」といったエンティティを取り出し、最終的にはテーブルに落とし込むことになります。
具体例として「社員」や「部署」といったものを挙げましたが、物理的な実体を伴う必要はなく、「注文履歴」のように概念としてしか存在しないものも抽出することができます。
エンティティの定義
ここでは抽出したエンティティがどのようなデータを保持するかを決定します。
例えば、「社員」というエンティティであれば「部署」、「生年月日」、「電話番号」などのデータを保持すると定義することができます。
正規化
今回フォーカスする正規化ですが、本書では以下のように書かれています。
正規化(normalization)は、エンティティ（テーブル）について、システムでの利用がスムーズに行えるように整理する作業です。
特に、正規化は更新（データの登録、変更、削除）が整合的に行えるようにエンティティのフォーマットを整理することが重要な目的です。
ここでは正規化が最も重要な土台となると書かれています。
理由としては、エンティティを抽出し、定義しただけの状態ではシステムの利用に耐える状態にはなっていないためです。
どういうことかと言うと、正規化を行っていないテーブルにデータを追加する場合、本来追加したいデータを正常にデータベースに追加できなかったり、テーブル内のデータを更新したいときに更新処理が意図通りにできないといった不都合が発生してしまうのです。
これを防止するために正規化を行います。
本書ではこれに関して、以下のように言及されています。
正規形とは、一言でいうと、データベースで保持するデータの冗長性を排除し、一貫性と効率性を保持するためのデータ形式です。
「冗長性」？？？ となってしまいますが
ここでの冗長性とは一つの情報が複数のテーブルに存在することなどにより、不要なデータ領域と更新処理を発生させてしまうことを指しています。
端的に言うと、正規化を行うことでデータの管理が容易になり、データを変更するときには最小限の更新で済むようになるのです。
さらに無駄なデータを保持しなくなるので、データ容量の削減や処理効率の上昇にも繋がると言うことです。
先ほどの正規化していないテーブルで発生しうる不都合を未然に防止することができるというわけですね。
さらにそれによって発生しうる余計なデータ容量の使用や処理効率の低下も防止することができます。
詳しい正規化の方法については後述します。
ER図の作成
正規化を行うとエンティティであるテーブルを細かく分割していくことになるため、エンティティ同士の関係が把握しづらくなります。
これは開発の効率を下げることにもつながるため、エンティティ同士の関係を表現するためにER図を用います。
（余談ですが、ER図の種類や作成方法も本書では解説されています！
ER図について詳しく知りたい方やざっと復習したい方にもおすすめです。）
画像引用元：若手プログラマー必読！５分で理解できるER図の書き方５ステップ
以上のタスクを1~4の順に設計を行うのが論理設計になります！
そして、今回のメインである正規化には段階的にレベルがあり、一般的には第1〜第5まで知られています。
基本的には第3正規形まで理解し、行うことができれば通常の業務上問題のないとのことなので、
今回はこの第1〜第3正規形までをおさえたいと思います！
第1正規形
最もレベルの低い第1正規形は、「テーブルの行と列が交差する特定の1マスに1つの値しか含まない」と言うものです。
| 社員コード | 社員名 | 住所 | 部署ID | 部署 |
|---|---|---|---|---|
| A01 | 田中 | 東京 | 101 | 営業 |
| A02 | 佐藤 | 埼玉 | 102 | 開発 |
| A03 | 山本 | 東京 | 103 | 人事 |
| A04 | 鈴木 | 埼玉 | 104 | 経理 |
| A05 | 斎藤 | 千葉 | 105 | 生産管理 |
| A06 | 山田 | 茨城 | 104 | 経理 |
上記のように特定の1マスに1つの値が入っていれば第1正規形は完了です。
このような1つのマスに1つの値が入っていることをスカラ値(scalar value)と呼びます。
実際のところ、リレーショナルデータベースでは既に第1正規形を満たす形式になっているため、設計の際に意識することはあまりないかもしれません。
第2正規形
続いては第2正規形です。
今回は以下のテーブルを使って解説します。
| 企業ID | 企業名 | 社員コード | 社員名 | 住所 | 部署ID | 部署 |
|---|---|---|---|---|---|---|
| C01 | A商事 | A01 | 田中 | 東京 | 101 | 営業 |
| C02 | B製作所 | A02 | 佐藤 | 埼玉 | 102 | 開発 |
| C03 | C通信 | A03 | 山本 | 東京 | 103 | 人事 |
| C04 | D生命 | A04 | 鈴木 | 埼玉 | 104 | 経理 |
| C01 | A商事 | A05 | 斎藤 | 千葉 | 105 | 生産管理 |
| C03 | C通信 | A06 | 山田 | 茨城 | 104 | 経理 |
この第2正規形を行う前には主キーについて考える必要があります。
主キーとは
主キー(primary key)
主キーは関係(表)の中で一つだけ設定します。
一意制約(重複が認められない)と非ナル制約(NULLが認められない)を併せ持つもので、
候補キーの中から最もふさわしいものが選ばれます。
例えば、
商品を識別するために一意に振られるような商品番号とか、
社員を識別するために一意に振られるような社員番号などがあります。
このテーブルの主キーは企業IDと社員コードになります。
(企業IDだけでは同じ企業名が登録されているため、データ行の特定はできません。)
この主キーを組み合わせることで、レコードの特定をすることができるのですが、よく見ると主キーの一部である企業IDで企業名を特定できてしまいます。
この状態を企業名の列は主キーの一部である企業IDに従属しているということができます。
このように主キーの一部の列を確認しただけで、レコードを特定できるものがある場合（今回は企業IDを確認すると企業名が確認できる）は、第1正規形で止まっていると判断でき、この状態を部分関数従属性があると呼びます。
これに対して、主キーを構成するすべての列に従属性がある場合を完全関数従属性と呼びます。
第2正規形ではこの部分関数従属性を解消し、完全関数従属性を実現できるテーブル構成にする必要があります。
以下のようにテーブルを二つに分けます。
社員テーブル
| 企業ID | 社員コード | 社員名 | 住所 | 部署ID | 部署 |
|---|---|---|---|---|---|
| C01 | A01 | 田中 | 東京 | 101 | 営業 |
| C02 | A02 | 佐藤 | 埼玉 | 102 | 開発 |
| C03 | A03 | 山本 | 東京 | 103 | 人事 |
| C04 | A04 | 鈴木 | 埼玉 | 104 | 経理 |
| C01 | A05 | 斎藤 | 千葉 | 105 | 生産管理 |
| C03 | A06 | 山田 | 茨城 | 104 | 経理 |
企業テーブル
| 企業ID | 企業名 |
|---|---|
| C01 | A商事 |
| C02 | B製作所 |
| C03 | C通信 |
| C04 | D生命 |
これによって新らしく作成した企業テーブルと既存の社員テーブルのどちらもすべてのレコードが主キーに完全関数従属することになりました。
とはいっても元々、会社名の箇所以外は完全関数従属していたのでテーブルを分けたことで完全関数従属性が成り立つのは当然ですね。
この第2正規形を行うことで、社員の情報が不明な会社を登録することが可能になります。（正規化前のテーブルでは主キーの一部に社員コードが含まれているのでNULL状態ではレコードを追加きない）
また、会社コードと会社名の対応がレコードによってまちまちになってしまうといった不都合を事前に回避することができます。（例えば、一方のレコードでは{C01 A商事}がもう一方のレコードで{C01 A商社}と登録されてしまうような状況です。）
第3正規形
第2正規形まででデータ登録や更新時の不都合を防止できるのですが、まだ社員が一人もいない部署の追加ができないなど不都合が存在します。
理由は先ほどと同じように主キーの一部となっている社員コードがNULLでのレコード登録を許容していないからです。
社員テーブル
| 企業ID | 社員コード | 社員名 | 住所 | 部署ID | 部署 |
|---|---|---|---|---|---|
| C01 | A01 | 田中 | 東京 | 101 | 営業 |
| C02 | A02 | 佐藤 | 埼玉 | 102 | 開発 |
| C03 | A03 | 山本 | 東京 | 103 | 人事 |
| C04 | A04 | 鈴木 | 埼玉 | 104 | 経理 |
| C01 | A05 | 斎藤 | 千葉 | 105 | 生産管理 |
| C03 | A06 | 山田 | 茨城 | 104 | 経理 |
企業テーブル
| 企業ID | 企業名 |
|---|---|
| C01 | A商事 |
| C02 | B製作所 |
| C03 | C通信 |
| C04 | D生命 |
実はまだこのテーブル内には隠れた関数従属性が残っています。
{部署ID}→{部署}という関数従属性が存在していますが、
{企業ID、社員コード}→{部署ID}という関数従属性も存在しています。
全体としては{企業ID、社員コード}→{部署ID}→{部署}という二段階で関数従属性が存在しており、この段階的な関数従属性のことを推移的関数従属性と呼びます。
以下のようにテーブル分割をすることで正規化を行います。
社員テーブル
| 企業ID | 社員コード | 社員名 | 住所 | 部署ID |
|---|---|---|---|---|
| C01 | A01 | 田中 | 東京 | 101 |
| C02 | A02 | 佐藤 | 埼玉 | 102 |
| C03 | A03 | 山本 | 東京 | 103 |
| C04 | A04 | 鈴木 | 埼玉 | 104 |
| C01 | A05 | 斎藤 | 千葉 | 105 |
| C03 | A06 | 山田 | 茨城 | 104 |
企業テーブル
| 企業ID | 企業名 |
|---|---|
| C01 | A商事 |
| C02 | B製作所 |
| C03 | C通信 |
| C04 | D生命 |
部署テーブル
| 部署ID | 部署 |
|---|---|
| 101 | 営業 |
| 102 | 開発 |
| 103 | 人事 |
| 104 | 経理 |
| 105 | 生産管理 |
このように部署テーブルを作成することで推移的関数従属性が解消され、社員が一人もいない部署の追加が可能になりました。
以上が第3正規形までの正規化になります。
ただし、1つ注意点があります！それは
正規化後のテーブルをいつでも正規化前のテーブルの状態に戻せるようにテーブル作成を行う必要がある。
ということです。
これを無損失分解といい、SQLの内部結合を利用して正規化以前の状態に戻すことが可能な状態を指します。
無損失分解とはデータを一切失わない操作であるため、無損失分解ができておらず、正規化以前の状態に戻すことができない場合には、データベースで何かデータを失ってしまっていることが考えられます。
参考サイト：うさぎでもわかるデータベースの正規化・正規系判定（基本情報・応用情報）
終わりに
一通り第1~第3正規形まで簡単に解説しました。
当然のことながら、本書、『達人に学ぶDB設計 徹底指南書 ～初級者で終わりたくないあなたへ』 では正規形についてより詳細な内容を解説しています。
物理設計についてもかなり丁寧に解説されており、多くの学びを得ることができました。
今後もこちらで学んだことについて自分なりにまとめてみたいと思います。
内容も自分の理解が甘いところがあるので逐次修正していきたいと思います。
今回の記事に間違い等ございましたらご指摘いただければ幸いです。
これからもKindleでたくさん勉強していこう！