DB設計（主に正規化について）の話
今担当しているタスクで新しくテーブルを作成するということもあり、DB設計について改めて調べてみました。
必要なカラムの洗い出し
テーブルを新しく作る時、まずはそのテーブルに何を保存するのかから考えると思います。
「何を保存するか」はどのような要件なのかという要件定義と大きく関わってきます。
その為、まずは何をするシステムなのかをしっかり定める必要があります。
要件定義ができたら必要な情報が洗い出されると思うのでそれを元にテーブルを設計していきます。
要件定義がしっかりしているとこの時点で各カラムの型等も自ずと決まってくると思います。
正規化
必要な情報が揃ったとはいえ、そのまま1つのテーブルにするのが望ましいとは限りません。
例えば商品の販売履歴を管理するテーブルを作る場合に以下のようなテーブルだとどうなるでしょうか？
販売履歴
| customer_name | customer_prefecture | item_name | price | quantity | date |
|---|---|---|---|---|---|
| 田中一郎 | 大阪府 | テレビ | 50000 | 1 | 2022-09-01 |
| 田中一郎 | 大阪府 | スピーカー | 10000 | 1 | 2022-09-01 |
| 山田太郎 | 兵庫県 | テレビ | 50000 | 1 | 2022-09-02 |
| 佐藤花子 | 京都府 | テレビ | 50000 | 1 | 2022-09-03 |
テーブルの中で「テレビ」が重複してしまいます。
もしもテレビの価格が50000円から100000円に変えなければならなくなったら該当するレコードを全て更新しなければなりません。
こういう場合は商品マスタを別途作成してテーブル同士を関連付けることで整理できます。
販売履歴
| id | customer_name | customer_prefecture | item_id | quantity | date |
|---|---|---|---|---|---|
| 1 | 田中一郎 | 大阪府 | 1 | 1 | 2022-09-01 |
| 2 | 田中一郎 | 大阪府 | 2 | 1 | 2022-09-01 |
| 3 | 山田太郎 | 兵庫県 | 1 | 1 | 2022-09-02 |
| 4 | 佐藤花子 | 京都府 | 1 | 1 | 2022-09-03 |
商品マスタ
| id | name | price |
|---|---|---|
| 1 | テレビ | 50000 |
| 2 | スピーカー | 10000 |
こうすることで価格が変更された場合でも商品マスタの1レコードを更新するだけで済むようになりました。
このようにテーブル内の冗長性を排除することを正規化と呼びます。
正規形にはいくつかの段階があります。
第1正規形
第1正規形は1つのカラムに1つのデータが入っている状態のことです。
RDBでは1つのカラムに複数のデータを入れられませんので即ちテーブル化できる形のことです。
（JSON型やカンマつなぎ等、複数入れられる方法もあるにはありますが個人的に嫌いです）
第2正規形
第2正規形は部分関数従属性が無い状態のことです。
例えば一番最初のテーブルを改めて見てみると、
| customer_name | customer_prefecture | item_name | price | quantity | date |
|---|---|---|---|---|---|
| 田中一郎 | 大阪府 | テレビ | 50000 | 1 | 2022-09-01 |
| 田中一郎 | 大阪府 | スピーカー | 10000 | 1 | 2022-09-01 |
| 山田太郎 | 兵庫県 | テレビ | 50000 | 1 | 2022-09-02 |
| 佐藤花子 | 京都府 | テレビ | 50000 | 1 | 2022-09-03 |
価格を示すpriceは商品であるitem_nameによって決まります。
こうした関係を関数従属性と呼びます。
また、上記のテーブルで任意のレコードを特定できるキーの集合（候補キーと呼びます）は{customer_name, item_name, date}です。
候補キーの一部であるitem_nameにpriceは関数従属している為、これを部分関数従属性と呼びます。
第2正規形はこれを無くし、主キーによって一意に定まるようにテーブルを分割して完全関数従属させたものになります。
なので先程の例では第2正規形まで正規化ができているということになります。
第3正規形
もう一度第2正規化後の販売履歴テーブルを見てみましょう。
| id | customer_name | customer_prefecture | item_id | quantity | date |
|---|---|---|---|---|---|
| 1 | 田中一郎 | 大阪府 | 1 | 1 | 2022-09-01 |
| 2 | 田中一郎 | 大阪府 | 2 | 1 | 2022-09-01 |
| 3 | 山田太郎 | 兵庫県 | 1 | 1 | 2022-09-02 |
| 4 | 佐藤花子 | 京都府 | 1 | 1 | 2022-09-03 |
ここで佐藤花子さんが東京都に引っ越したとしたらどうでしょうか？
この場合、販売履歴テーブルの佐藤花子さんのレコードを全て更新しなければなりません。
テーブル全体で見るとcustomer_nameもcustomer_prefectureもitem_idも候補キーではありません。同じ都道府県に同姓同名の人がいることや同じ人が同じ商品を購入することはあり得ることだからです。一意には定まりません。
こうした候補キー以外のカラム（副キー属性と呼びます）間での従属関係を推移的関数従属と呼びます。
第3正規形とは推移的関数従属が無い状態のことを言います。
その為、もう一つ顧客マスタテーブルとして分割しましょう。
販売履歴
| id | customer_id | item_id | quantity | date |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2022-09-01 |
| 2 | 1 | 2 | 1 | 2022-09-01 |
| 3 | 2 | 1 | 1 | 2022-09-02 |
| 4 | 3 | 1 | 1 | 2022-09-03 |
商品マスタ
| id | name | price |
|---|---|---|
| 1 | テレビ | 50000 |
| 2 | スピーカー | 10000 |
顧客マスタ
| id | name | prefecture |
|---|---|---|
| 1 | 田中一郎 | 大阪府 |
| 2 | 山田太郎 | 兵庫県 |
| 3 | 佐藤花子 | 京都府 |
さらに言うと顧客マスタもまだ正規化できます。
販売履歴
| id | customer_id | item_id | quantity | date |
|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 2022-09-01 |
| 2 | 1 | 2 | 1 | 2022-09-01 |
| 3 | 2 | 1 | 1 | 2022-09-02 |
| 4 | 3 | 1 | 1 | 2022-09-03 |
商品マスタ
| id | name | price |
|---|---|---|
| 1 | テレビ | 50000 |
| 2 | スピーカー | 10000 |
顧客マスタ
| id | name | prefecture_id |
|---|---|---|
| 1 | 田中一郎 | 27 |
| 2 | 山田太郎 | 28 |
| 3 | 佐藤花子 | 26 |
都道府県マスタ
| id | name |
|---|---|
| ... | ... |
| 26 | 京都府 |
| 27 | 大阪府 |
| 28 | 兵庫県 |
| ... | ... |
最初のテーブルよりかなり整理することができました。
このようにどの情報をどこに持たせるかを予め丁寧に設計しておくことでデータの矛盾が無くなります。
逆にこの辺りを怠ると後から変えるのはかなり大変だと思います。
まとめ
以上、主に正規化についての話を中心にDB設計についてまとめてみました。
他にもインデックスや外部キー制約等、考慮すべき点は沢山あるのですがそれは次回に持ち越したいと思います。
正規化についてまとめる中で感じたのはDIの考え方に近いのではということでした。
冗長性を排除するということは他のテーブルに分割すべき知識を排除するということであり、これはまさに抽象に依存するということではないかと思います。
例えば↑の例で言うと販売履歴テーブルにitem_idを通じて商品マスタテーブルを注入しているけど、販売履歴に必要なのはitem_idだけで商品名などの知識は持っておく必要が無いという感じでしょうか。
理解や認識に誤ったところがあればご指摘いただけると幸いです。
