データベース設計
データベース設計
データベースはアプリケーションの中核であり、データベースの設計で失敗してしまうと、アプリケーションの開発、保守、運用が恐ろしく難しくなってしまう。
データベース設計は大まかに以下の順序で進める。
①概念の抽出
どのような流れでデータを取得するのかを具体的にイメージし、必要な項目を洗い出して表を作成する。
▼データ取得のイメージ
▼作成した表
| 購入ID | 顧客ID | 顧客名 | 年齢 | 性別 | 商品ID | 商品名 | 単価 | 数量 | 購入日 |
| 1 | C0001 | 山田ヤマト | 15 | 男 | P0001 | ランチパック | 130 | 2 | 2022/03/27 |
| 2 | C0002 | 松下ゆう | 53 | 女 | P0002 | お茶 | 120 | 1 | 2022/03/28 |
| P0003 | おにぎり | 160 | 2 | ||||||
| 3 | C0003 | 川下さと | 32 | 女 | P0002 | お茶 | 120 | 1 | 2022/03/29 |
| P0004 | プリン | 100 | 1 |
②第１正規化
第１正規化では、「①概念の抽出」で得られた表から空欄や結合セルを排除し、データベースで扱えるような形に変形する。
▼第１正規化を実施したテーブル
| 購入ID | 顧客ID | 顧客名 | 年齢 | 性別 | 商品ID | 商品名 | 単価 | 数量 | 購入日 |
| 1 | C0001 | 山田ヤマト | 15 | 男 | P0001 | ランチパック | 130 | 2 | 2022/03/27 |
| 2 | C0002 | 松下ゆう | 53 | 女 | P0002 | お茶 | 120 | 1 | 2022/03/28 |
| 2 | C0002 | 松下ゆう | 53 | 女 | P0003 | おにぎり | 160 | 2 | 2022/03/28 |
| 3 | C0003 | 川下さと | 32 | 女 | P0002 | お茶 | 120 | 1 | 2022/03/29 |
| 3 | C0003 | 川下さと | 32 | 女 | P0004 | プリン | 100 | 1 | 2022/03/29 |
③第２正規化および第３正規化
第１正規化だけでは、購入しなかった顧客や購入されなかった商品の情報を格納できなかったり、年齢が変わったときに複数行に渡って変更する必要があったりと実用上の問題が発生する。そこで次に、第２正規化および第３正規化を実施していく。
第２正規化および第３正規化では、「②第１正規化」で得られたテーブルから行が重複している列を切り出していき、これを第３正規形になるまで繰り返す。なお、第３正規形になっているかどうかは、主キーの値が決まれば非主キー列が直接一意に決まるかどうかを見れば良い。
「②第１正規化」で得られたテーブルをよく見ると、以下の色付きで示した部分の行が重複していることがわかる。
そこで、重複している部分を切り出して、以下のように３つのテーブルに分割していく。なお、分割したテーブルを結合すると分割前の状態に戻せる（無損失分解）ことに注意されたい。
▼購入テーブル ※ 主キー：購入ID
| 購入ID | 顧客ID | 顧客名 | 年齢 | 性別 | 購入日 |
| 1 | C0001 | 山田ヤマト | 15 | 男 | 2022/03/27 |
| 2 | C0002 | 松下ゆう | 53 | 女 | 2022/03/28 |
| 3 | C0003 | 川下さと | 32 | 女 | 2022/03/29 |
▼商品テーブル ※ 主キー：商品ID
| 商品ID | 商品名 | 単価 |
| P0001 | ランチパック | 130 |
| P0002 | お茶 | 120 |
| P0003 | おにぎり | 160 |
| P0004 | プリン | 100 |
▼購入明細テーブル ※ 複合主キー：購入ID、商品ID
| 購入ID | 商品ID | 数量 |
| 1 | P0001 | 2 |
| 2 | P0002 | 1 |
| 2 | P0003 | 2 |
| 3 | P0002 | 1 |
| 3 | P0004 | 1 |
次に、これらのテーブルそれぞれに対して、主キーの値が決まれば非主キー列が直接一意に決まる（第３正規形）かどうかを見ていく。すると、購入テーブルの顧客名、年齢、性別は顧客IDに従属していて、主キーに直接従属していないことがわかる。
そこで、購入テーブルをさらに分割する。
▼購入テーブル ※ 主キー：購入ID
| 購入ID | 顧客ID | 購入日 |
| 1 | C0001 | 2022/03/27 |
| 2 | C0002 | 2022/03/28 |
| 3 | C0003 | 2022/03/29 |
▼顧客テーブル ※ 主キー：顧客ID
| 顧客ID | 顧客名 | 年齢 | 性別 |
| C0001 | 山田ヤマト | 15 | 男 |
| C0002 | 松下ゆう | 53 | 女 |
| C0003 | 川下さと | 32 | 女 |
このように正規化を実施していくと、重複が排除され、一つの事実が一つのテーブルの一行の中にだけ存在するように変形できる。最終的に以下の４つのテーブルが得られる。
▼購入テーブル ※ 主キー：購入ID
| 購入ID | 顧客ID | 購入日 |
| 1 | C0001 | 2022/03/27 |
| 2 | C0002 | 2022/03/28 |
| 3 | C0003 | 2022/03/29 |
▼顧客テーブル ※ 主キー：顧客ID
| 顧客ID | 顧客名 | 年齢 | 性別 |
| C0001 | 山田ヤマト | 15 | 男 |
| C0002 | 松下ゆう | 53 | 女 |
| C0003 | 川下さと | 32 | 女 |
▼商品テーブル ※ 主キー：商品ID
| 商品ID | 商品名 | 単価 |
| P0001 | ランチパック | 130 |
| P0002 | お茶 | 120 |
| P0003 | おにぎり | 160 |
| P0004 | プリン | 100 |
▼購入明細テーブル ※ 複合主キー：購入ID、商品ID
| 購入ID | 商品ID | 数量 |
| 1 | P0001 | 2 |
| 2 | P0002 | 1 |
| 2 | P0003 | 2 |
| 3 | P0002 | 1 |
| 3 | P0004 | 1 |
④ER図の作成
上で作成した４つのテーブルをER図にまとめると以下のようになる。
