データベース正規化についてまとめてみる
データベース初心者の私にとって正規化という概念が非常に理解しづらかったため、理解を深めるために正規化について一度まとめておく。
正規化とは
データベースを作る際にデータを扱いやすいようにテーブルを分割すること。
例えば、以下のテーブルを見てみる。
- 取引テーブル
取引ID | 商品ID | 商品名 | 個数 | 購入者ID | 購入者名 |
---|---|---|---|---|---|
001 | 001 002 |
ハサミ のり |
3 1 |
0001 | 田中太郎 |
002 | 001 | ハサミ | 9 | 0003 | 山田花子 |
003 | 002 003 |
のり 鉛筆 |
6 8 |
0002 | 鈴木健介 |
このテーブルにはデータベースで扱う際に問題となる点がいくつか存在している。
1. 一つのフィールドに複数の商品が設定されており、このままではデータベースに登録することができない。
2. 同じ商品名が複数箇所に存在しており、もしデータの修正が必要になった場合全ての箇所を直さなくてはならない。
3. 商品IDと商品名、購入者IDと購入者名はそれぞれ紐づいているがその組み合わせが正しいことを保証できない
この問題は以下のようにテーブルを分割することで解決する。
- 取引商品テーブル
取引ID | 商品ID | 個数 |
---|---|---|
001 | 001 | 3 |
001 | 002 | 1 |
002 | 001 | 9 |
003 | 002 | 6 |
003 | 003 | 8 |
- 取引購入者テーブル
取引ID | 購入者ID |
---|---|
001 | 0001 |
002 | 0003 |
003 | 0002 |
- 商品テーブル
商品ID | 商品名 |
---|---|
001 | ハサミ |
002 | のり |
003 | 鉛筆 |
- 購入者テーブル
購入者ID | 購入者名 |
---|---|
0001 | 田中太郎 |
0002 | 鈴木健介 |
0003 | 山田花子 |
テーブルを分割したことによって、上記に挙げた問題は全て解決した。
- 一つのフィールドで一つの商品のみを取り扱うようにしたため、データベースに登録できるようになった。
- 商品名を別テーブルに分けたことにより、修正する際の変更箇所は1箇所となった。
- 商品IDと商品名、購入者IDと購入者名の組み合わせをそれぞれ別テーブルで管理することにより、組み合わせの正しさを保証できるようになった。
このようにデータを効率的に管理できるようにするのが正規化である。
正規化の順序
正規化は3つの段階に分けられる。(厳密には第4段階以降も存在するが、一般的に行われる正規化は第3段階までである為、本稿では割愛させていただく。)
各段階において決まりに基づいてテーブルを整理していく。
また、正規化を一切行っていないテーブルを非正規形という。
第一正規化
取引ID | 商品ID | 商品名 | 個数 | 購入者ID | 購入者名 |
---|---|---|---|---|---|
001 | 001 | ハサミ | 3 | 0001 | 田中太郎 |
001 | 002 | のり | 1 | 0001 | 田中太郎 |
002 | 001 | ハサミ | 9 | 0003 | 山田花子 |
003 | 002 | のり | 6 | 0002 | 鈴木健介 |
003 | 003 | 鉛筆 | 8 | 0002 | 鈴木健介 |
まず第一段階では、フィールド内に複数のデータを持たないようにしていく。
例えば取引ID[001]は今まで[ハサミ]と[のり]のデータを1行のレコードに持っていたが、これを2行に分割することで[ハサミ]と[のり]をそれぞれ一つのフィールドで管理できるようにできた。
第二正規化
- 取引商品テーブル
取引ID | 商品ID | 個数 |
---|---|---|
001 | 001 | 3 |
001 | 002 | 1 |
002 | 001 | 9 |
003 | 002 | 6 |
003 | 003 | 8 |
- 取引購入者テーブル
取引ID | 購入者ID | 購入者名 |
---|---|---|
001 | 0001 | 田中太郎 |
002 | 0003 | 山田花子 |
003 | 0002 | 鈴木健介 |
- 商品テーブル
商品ID | 商品名 |
---|---|
001 | ハサミ |
002 | のり |
003 | 鉛筆 |
次に、第二段階では部分関数従属を排除していく。
なかなか聞き慣れない言葉だが、要するに主キーを構成するカラムの一部分のみで絞り込めるものは別テーブルに分けましょうということ。
例えば取引商品テーブルの主キーは取引IDと商品IDだが、このうち商品IDのみに紐づいている商品名というカラムがあるので、商品テーブルという別テーブルに分けてしまう。
また取引IDのみに紐づいているカラムも存在しているため、これも取引購入者という別テーブルに分ける。
これで全カラムが主キー全体に紐づいている状態になった。
この状態を完全関数従属という。
第三正規化
- 取引商品テーブル
取引ID | 商品ID | 個数 |
---|---|---|
001 | 001 | 3 |
001 | 002 | 1 |
002 | 001 | 9 |
003 | 002 | 6 |
003 | 003 | 8 |
- 取引購入者テーブル
取引ID | 購入者ID |
---|---|
001 | 0001 |
002 | 0003 |
003 | 0002 |
- 商品テーブル
商品ID | 商品名 |
---|---|
001 | ハサミ |
002 | のり |
003 | 鉛筆 |
- 購入者テーブル
購入者ID | 購入者名 |
---|---|
0001 | 田中太郎 |
0002 | 鈴木健介 |
0003 | 山田花子 |
そして(通常では)最後の第三段階。
推移的関数従属しているカラムを別テーブルに切り出す。
推移的関数従属とは、主キー以外のカラムによって値が決定される状態をいう。
例えば取引購入者テーブルの主キーは取引IDだが、別に購入者IDも持っておりこれに紐づく購入者名というカラムが存在している。
これも購入者テーブルという別テーブルに分けてしまう。
こうすることで、推移的関数従属されているカラムもなくなり、第三正規化完了となる。
まとめ
第一正規化については理解しやすいものの、第二正規化と第三正規化の区別は初心者にはやや難しかった。
第二正規化は主キーの一部に紐づくカラムを切り出し、第三正規化では主キー以外に紐づくカラムを切り出すと覚えると良いだろう。
Discussion