はじめに
今回はテーブルの正規化について要点を整理したいと思います。
正規化とは
正規形とは、 データベースで保持するデータの冗長性を無くし、一貫性と効率性を持たせたデータの保持形式を指します。
正規化とは正規形を実現するための方法論です。
正規化には5段階あり、段階が上がるごとに正規化のレベルが上がります。第3正規形が達成できていれば、それ以降の段階の正規形の条件を満たすことが多いため、一般的には第3正規形までを考えていきます。
第1正規形
第1正規形は1つのセルに複数のデータが入る状態を無くし、１つのセルには1つのデータが入る形式を指します。
- 非正規形
| 生徒ID | 名前 | 部活 |
|---|---|---|
| 1 | 田中 | サッカー 将棋 |
| 2 | 工藤 | |
| 3 | 斉藤 | バスケットボール |
- 第1正規形
| 生徒ID | 名前 | 部活 |
|---|---|---|
| 1 | 田中 | サッカー |
| 1 | 田中 | 将棋 |
| 2 | 工藤 | |
| 3 | 斉藤 | バスケットボール |
１つのセルに1つの値が入っているため、この段階で第1正規形の条件を満たします。
しかしこの状態では、「主キーが決められない」「生徒、部活という2つのエンティティの情報を含んでおりレコードの単位を理解しにくい」という２つの問題があります。
そこで次のようにテーブルを分割することでそれらの問題を回避できます。
- 第1正規形（テーブル分割）
生徒テーブル
| 生徒ID | 名前 |
|---|---|
| 1 | 田中 |
| 2 | 工藤 |
| 3 | 斉藤 |
部活テーブル
| 生徒ID | 部活 |
|---|---|
| 1 | サッカー |
| 1 | 将棋 |
| 3 | バスケットボール |
生徒と部活を紐づけた情報を得たいときは、生徒IDをキーにして結合することで可能です。
関数従属性
関数従属性は正規形を理解する上で非常に重要な概念です。
Y=f(X)という関数では、Xの値が決まればYの値が1つに決まります。これはYがXに従属している状態です。
リレーショナルデータベースではこのXとYの関係を{X} → {Y}と表し、X列の値が決まればY列の値が決まることを指します。(XやYは複数の列の組み合わせでも良い)
先ほどの非正規形の例では、{生徒ID}→{生徒名}が成立しています。一方で、{生徒ID}→{部活}は成立しません。生徒ID「1」に対して部活「サッカー、将棋」が該当しているためです。
正規化後の例では、部活テーブルで｛生徒ID｝→｛部活｝が成立しています。生徒テーブルにおいても｛生徒ID｝→｛名前｝が成立しています。
第2正規形
第2正規形は部分関数従属が解消された完全関数従属のみで構成されるデータ形式を指します。
- 第1正規化
| チームID | チーム名 | 選手ID | 選手名 | 年齢 | ポジションID | ポジション名 |
|---|---|---|---|---|---|---|
| 1 | バルセロナ | 1 | ブスケッツ | 34 | 3 | MF |
| 2 | レアルソシエダ | 1 | 久保 | 21 | 1 | FW |
| 3 | レアル・マドリード | 7 | モドリッチ | 37 | 3 | MF |
このテーブルは1つのセルに1つの値が入っているため第1正規形になります。
しかし、まだ関数従属が完全ではないため第2正規形ではありません。
このテーブルの主キーは{チームID、選手ID}であり、他の全ての列の値は{チームID、選手ID}に従属しなければいけないのですが、チーム名はチームIDに従属しています。
{チームID}→{チーム名}
このように主キーの一部に従属がある部分を部分関数従属といい、主キーに従属がある部分を完全関数従属といいます。
第2正規形では、完全関数従属のみのテーブルを作っていきます。
部分関数従属を解消する手段は、部分関数従属の部分を独立したテーブルにすることです。
例の場合、次のようにチームIDとチーム名のテーブルを作ります。
- 第2正規形
選手テーブル
| チームID | 選手ID | 選手名 | 年齢 | ポジションID | ポジション名 |
|---|---|---|---|---|---|
| 1 | 1 | ブスケッツ | 34 | 3 | MF |
| 2 | 1 | 久保 | 21 | 1 | FW |
| 3 | 7 | モドリッチ | 37 | 3 | MF |
チームテーブル
| チームID | チーム名 |
|---|---|
| 1 | バルセロナ |
| 2 | レアルソシエダ |
| 3 | レアル・マドリード |
こうすることで選手テーブルもチームテーブルも全ての列が主キーに完全従属するようになりました。
第2正規形のメリット
第2正規化前のテーブルだと、選手情報がないチームはテーブルに含まれません。
一時的にダミーの選手情報を入れることで回避はできますが根本的な解決にはなっていません。
また、レコード登録時にちくいちチーム名も登録しなければならないため、誤ったチーム名を登録してしまう可能性があります。
これらの問題はチームテーブルを作ることで回避できます。
第3正規形
第3正規形は推移的関数従属を解消したデータ形式を指します。
第2正規化されたテーブルにはポジションID、ポジション名がありますが、従属関係は{ポジションID}→{ポジション名}となっています。
また、{チームID、選手ID}→{ポジションID}という従属関係もあります。
つまり、{チームID、選手ID}→{ポジションID}→{ポジション名}という2段階の関数従属が存在しています。
このようにテーブル内に段階的に存在する関数従属の関係を推移的関数従属と言います。
推移的関数従属を解消する場合もテーブル分割を行います。
- 第3正規形
選手テーブル
| チームID | 選手ID | 選手名 | 年齢 |
|---|---|---|---|
| 1 | 1 | ブスケッツ | 34 |
| 2 | 1 | 久保 | 21 |
| 3 | 7 | モドリッチ | 37 |
チームテーブル
| チームID | チーム名 |
|---|---|
| 1 | バルセロナ |
| 2 | レアルソシエダ |
| 3 | レアル・マドリード |
ポジションテーブル
| ポジションID | ポジション名 |
|---|---|
| 1 | FW |
| 2 | DF |
| 3 | MF |
こうすることで、全ての列が主キーに対してのみ従属するようになり、推移的関数従属は解消されました。
さいごに
データベース設計に関して学ぶことはまだまだたくさんあるので、引き続き学習をしていきます。
本記事を作成するにあたり、「達人に学ぶDB設計指南書」を参考にさせて頂きました。