テーブル設計に必要な正規化
正規化とは？
RDBMS(リレーショナルデータベース)において、テーブルを作成しデータを格納する前に、必ず考えるべきことが
テーブルの正規化です。
テーブルの正規化をすることで得られる効果としては以下のようなものがあります。
- データ格納領域の削減
- データアクセス効率の向上
- データに対する一貫性の維持
では、正規化とは何かというと、テーブルに格納するデータをどのように分解して、どのように結び付けるかという事を考えることです。
簡単に例をあげると、以下のようなテーブルがあるとします。
旅行プラン
| 場所 | 日付 | 人数 | 価格(基本価格に対する倍率) |
|---|
| 日光 | 2008/07/05 | 100 | 8,400(1.05) |
| 日光 | 2008/07/06 | 120 | 8,800(1.10) |
| 日光 | 2008/07/07 | 80 | 8,000(1.00) |
| 日光 | 2008/07/08 | 80 | 8,000(1.00) |
| 日光 | 2008/07/09 | 80 | 8,000(1.00) |
| 伊豆 | 2008/07/05 | 100 | 9,450(1.05) |
| 伊豆 | 2008/07/06 | 120 | 9,900(1.10) |
| 伊豆 | 2008/07/07 | 80 | 9,000(1.00) |
この内容を見ると、『場所』に同じ値が複数出てます。
この『場所』にある『日光』を『鬼怒川』に変えようと思った場合、上記例では5レコードの変更が必要になります。
しかし以下のような表にした場合はどうでしょう？
旅行プラン
| 場所コード | 日付 | 人数 | 価格(基本価格に対する倍率) |
|---|
| 1 | 2008/07/05 | 100 | 8,400(1.05) |
| 1 | 2008/07/06 | 120 | 8,800(1.10) |
| 1 | 2008/07/07 | 80 | 8,000(1.00) |
| 1 | 2008/07/08 | 80 | 8,000(1.00) |
| 1 | 2008/07/09 | 80 | 8,000(1.00) |
| 2 | 2008/07/05 | 100 | 9,450(1.05) |
| 2 | 2008/07/06 | 120 | 9,900(1.10) |
| 2 | 2008/07/07 | 80 | 9,000(1.00) |
この場合、『場所』を『日光』から『鬼怒川』に変えたいと思った場合、旅行場所テーブルに存在する、
場所コードが1のレコードの場所名を変更するだけで、旅行プランの場所がすべて鬼怒川になります。
この様に、重複した値を分離してテーブルを分けて、コードで分離することが正規化といわれる作業になるわけですが、
正規化には手法があり、第1正規系～第5正規系、ボイス・コッド正規形、ドメイン・キー正規形というものがあります。
一般的に、第1～第3正規形までが行われています。
ボイス・コッド、第4、第5正規形までいくと、テーブルにアクセスするプログラムが発行するSQLの複雑さとの兼ね合いから
第3正規形や第3正規形をあえて第2正規形に戻したりして設計したりしているのが実務上の実体だと思います。
この理由から、このページでは第3正規形までを説明します。
それ以降の正規形については別ページにてわかる範囲で説明しようと思っていますので、そちらを参照していただければ幸いです。
非正規形
正規化する前に、非正規形のデータを考えてみます。
実は最初の例にあげた状態のテーブルでは、正規化されてしまっているのです。
これを非正規形で考えると以下のようなテーブルになります。
旅行プラン
| 場所 | 日付 | 人数 | 価格(基本価格に対する倍率) | 参加者名 |
|---|
| 日光 | 2008/07/05 | 100 | 8,400(1.05) | 安藤さん |
| 日光 | 2008/07/05 | 100 | 8,400(1.05) | 渡辺さん |
| 日光 | 2008/07/05 | 100 | 8,400(1.05) | 安部さん |
| 日光 | 2008/07/06 | 120 | 8,800(1.10) | 安田さん |
| 日光 | 2008/07/06 | 120 | 8,800(1.10) | 結城さん |
どのあたりが非正規形なのかというと『場所』～『価格』まで、同じ内容が繰り返されていて、
それに付随する『参加者』だけが繰り返されている状態です。
この様に
同じデータを繰り返して格納している状態が非正規形になります。
第1正規形
テーブルの値がスカラ値（値のみ）で行データとして重複データが存在しない状態を第1正規形と言われます。
この冒頭のページで示した最初の例のテーブルは実は現状入っているデータとしては第1正規形になっています。
なので、非正規形のデータと合わせて、第1正規形を作ってみます。
旅行プラン
| 場所 | 日付 | 人数 | 価格(基本価格に対する倍率) |
|---|
| 日光 | 2008/07/05 | 100 | 8,400(1.05) |
| 日光 | 2008/07/06 | 120 | 8,800(1.10) |
| 日光 | 2008/07/07 | 80 | 8,000(1.00) |
| 日光 | 2008/07/08 | 80 | 8,000(1.00) |
| 日光 | 2008/07/09 | 80 | 8,000(1.00) |
| 伊豆 | 2008/07/05 | 100 | 9,450(1.05) |
| 伊豆 | 2008/07/06 | 120 | 9,900(1.10) |
| 伊豆 | 2008/07/07 | 80 | 9,000(1.00) |
参加者
| 場所 | 日付 | 参加者名 |
|---|
| 日光 | 2008/07/05 | 安藤さん |
| 日光 | 2008/07/05 | 渡辺さん |
| 日光 | 2008/07/05 | 安部さん |
| 日光 | 2008/07/06 | 安田さん |
| 日光 | 2008/07/06 | 結城さん |
しかし、以下のようなテーブルにした場合は、第1正規形ではありません。
旅行プラン
| 場所 | 日付 | 人数 | 価格(基本価格に対する倍率) |
|---|
| 日光 | 2008/07/05 | 100 | 8,400(1.05) |
| 日光 | 2008/07/06 | 120 | 8,800(1.10) |
| 日光 | 2008/07/07,2008/07/08,2008/07/09 | 80 | 8,000(1.00) |
| 伊豆 | 2008/07/05 | 100 | 9,450(1.05) |
| 伊豆 | 2008/07/06 | 120 | 9,900(1.10) |
| 伊豆 | 2008/07/07 | 80 | 9,000(1.00) |
日付テーブルにカンマ(,)区切りでデータが格納されているデータが存在します。
この行は最初に示した例のように、行ごとに日付を持っていれば第1正規形といえる状態になります。
よって、
カンマ区切りなど区切り文字を使用して、実際の値を複数持つデータはスカラ値ではないという事も言えます。
この様なデータは
繰り返しグループや
反復群などと呼ばれています。
第2正規形
第2正規形は第1正規形をさらに分解します。
第2正規形はある項目の値が、一つの項目に対応することが条件になります。
よって例のテーブルを第2正規形にすると以下のように分解されます。
旅行プラン
| 場所コード | 日付 | 人数 | 価格(基本価格に対する倍率) |
|---|
| 1 | 2008/07/05 | 100 | 8,400(1.05) |
| 1 | 2008/07/06 | 120 | 8,800(1.10) |
| 1 | 2008/07/07 | 80 | 8,000(1.00) |
| 1 | 2008/07/08 | 80 | 8,000(1.00) |
| 1 | 2008/07/09 | 80 | 8,000(1.00) |
| 2 | 2008/07/05 | 100 | 9,450(1.05) |
| 2 | 2008/07/06 | 120 | 9,900(1.10) |
| 2 | 2008/07/07 | 80 | 9,000(1.00) |
参加者
| 場所コード | 日付 | 参加者名 |
|---|
| 1 | 2008/07/05 | 安藤さん |
| 1 | 2008/07/05 | 渡辺さん |
| 1 | 2008/07/05 | 安部さん |
| 1 | 2008/07/06 | 安田さん |
| 1 | 2008/07/06 | 結城さん |
場所テーブルを作成し、場所コードを作成しました。
その場所コードに対して場所名を持っているデータを作成し、
旅行プラン、参加者のテーブルのキー項目と関連を持っています。
この状態が第2正規形になります。
第2正規形の条件として、あるテーブル（この場合、旅行プラン、参加者テーブル）のある項目（場所コード）が決まれば、
場所テーブルを参照することで、場所名が一意に決まることが条件になります。
第3正規形
第3正規形は第2正規形をさらに分解します。
第2正規形まではキー項目に着目してテーブル分解および関連付を行っていましたが、
第3正規形では非キー項目に着目してテーブル分解および関連付けをします。
この例では、日付によって旅行料金が変動しています。（日付毎に一定倍率が設定されている）
また、その旅行料金を算出する基本料金は場所ごとに設定されています。
そこに注目してテーブルを分割し従属関係を作っていきます。
料金倍率テーブル
| 日付 | 基本価格に対する倍率 |
|---|
| 2008/07/05 | 1.05 |
| 2008/07/06 | 1.10 |
| 2008/07/07 | 1.00 |
| 2008/07/08 | 1.00 |
| 2008/07/09 | 1.00 |
基本価格テーブル
| 場所コード | 基本価格 |
|---|
| 1 | 8,000 |
| 2 | 9,000 |
旅行プラン
| 場所コード | 日付 | 人数 |
|---|
| 1 | 2008/07/05 | 100 |
| 1 | 2008/07/06 | 120 |
| 1 | 2008/07/07 | 80 |
| 1 | 2008/07/08 | 80 |
| 1 | 2008/07/09 | 80 |
| 2 | 2008/07/05 | 100 |
| 2 | 2008/07/06 | 120 |
| 2 | 2008/07/07 | 80 |
参加者
| 場所コード | 日付 | 参加者名 |
|---|
| 1 | 2008/07/05 | 安藤さん |
| 1 | 2008/07/05 | 渡辺さん |
| 1 | 2008/07/05 | 安部さん |
| 1 | 2008/07/06 | 安田さん |
| 1 | 2008/07/06 | 結城さん |
ボイス・コッド正規形
ボイス・コッド正規形は第3正規形を強化したものです。
ボイス・コッド正規形はほぼ第3正規形と同じですが、
主キー以外からの関連を認めていません。
これについては、今までの例では表せない（すでにBCNFを満たしている）ため、違う例でご紹介します。
| 社員番号 | 社員名 | メールアドレス |
|---|
| 1 | 安藤さん | andow@xxx.com |
このテーブルの場合、社員番号と社員名、社員番号とメールアドレス、社員名とメールアドレスという3つの関連が出来ています。
よって、これを分解することでボイス・コッド正規形が出来上がります。
| 社員番号 | メールアドレス |
|---|
| 1 | andow@xxx.com |
この様に、ボイス・コッド正規形まで行うと、1人の社員情報を引き出すために、複数テーブルを見る必要が出てきます。
そのため、実務では第3正規形までに留めて、データアクセスする際に見るテーブルの数を減らし
人間がわかりやすい様にしていることが多いです。
第3正規形以降について
ボイス・コッド正規形でも触れましたが、実務では第3正規形まで（またはボイス・コッド正規形まで）にとどめておくことが多いため、
第4、第5正規形については
テーブル設計に必要な正規化～第4、第5正規形～に記述することとします。