こんにちは！バックエンドエンジニアの小室です。
先日、4月から入社予定の方に向け「データベース設計」について研修を行いました。
その中でもメイントピックであった「正規化」について改めてまとめてみました。
さっそくですが、データベースにおける正規化とは、
データベースで保持するデータの冗長性を排除し、
一貫性と効率性を確保するためのデータ形式へ変換することを指します。
一般的に第3正規形までで十分とされているため第3正規形までを取り上げます。
第1正規形
テーブルの行と列が交わる1つマスを「セル」と呼ぶことにします。
第1正規形の定義は「1つのセルには1つの値しか含まれない」です。
このように1つのセルに1つの値が含まれているとき、この値を「スカラ値」と言います。
上のようなテーブルがあった場合、1人の社員は複数の子を養っているので、このように表現したくなりますが、
リレーショナルデータベースでは規則違反になります。
以下のように第1正規形に変換する必要があります。
子の数だけ行を増やしました。これで全てのセルがスカラ値となり、第1正規形となりました。
しかし、以下の問題が発生。
1. 主キーが決められない。
- この場合1レコードを特定するには「社員ID、社員名、子」の3列を
指定する必要があるが、主キーの定義上、その一部がNULLを保持してはならない。
1. テーブルの意味やレコードの単位がすぐに分からない。
- このテーブルは「社員、扶養者」という2つのエンティティを含んでしまっている。
下記のように分割することで、この2つの問題を解決できます。
「扶養者」テーブルに子を持っている社員レコードのみに限定でき、
かつ主キーがNULLになることを防げています。
なぜセルにはスカラ値でないとダメなのか？
セルに複数の値を入れてしまえば、主キーが各列の値を一意に特定できないからです。
ここで1つ大事な概念があります。「関数従属性」です。
関数「 y = f(x)」のように、入力(x)に対し出力(y)が一意に決まります。
この関係を「yはxに従属する」と表現し、「{x} → {y}」と表記したりします。
上の「社員テーブル(非正規形)」を見てみると、
「{社員ID} → {子}」という関数従属は不成立になります。
一方「社員テーブル(第1正規形)」は、
{社員ID} → {社員名}
{社員ID} → {子1}
{社員ID} → {子2}
という関数従属が成立しています。
第2正規形
このテーブルは全てスカラ値からなるため、第1正規形であると言えます。
しかし第2正規形ではありません。
このテーブルの主キーは「会社コード、社員ID」です。
したがって、主キー以外の他の列はこの主キーに従属するのに対し、
「会社名」だけは「会社コード」のみに従属しています。
このように主キーの一部に対して従属する列がある場合、
この関係を「部分関数従属」と言います。
これに対し主キーを構成する全ての列に従属性がある場合を「完全関数従属性」と言います。
第2正規形の定義は、「部分関数従属を排除し、完全関数従属にする」です。
部分関数従属の関係にあったキー列と、従属列を独立させました。
これにより社員テーブルにおける全ての列が主キーに対して、完全関数従属関係となりました。
第2正規形でないと何がダメか？
「社員・会社テーブル(第1正規形)」に対し、社員情報が不明の会社を登録したくなった場合、
主キーの一部に社員IDが含まれているため、社員IDがNULLになり登録ができません。
しかし「社員と会社が分割されたテーブル(第2正規形)」では、
会社テーブルのみに登録する事で、社員情報が不明な会社も登録できます。
このように見ると第2正規形はエンティティ(実体)をテーブルごとに分割する作業
という見方もできますね。
第3正規形
「部署コード」と「部署名」に注目してみます。
会社コードが「C0001」のA商事に関してテーブルからわかることは、
「営業、開発、人事」の3つの部署があるということです。
しかし実際には「広報」という部署も存在するかもしれません。
しかし「社員テーブル」には社員情報がないと登録できないため、
広報に社員が0人ならば広報部署は登録することができません。
理由は主キーがNULLになってしまうためです。
このような不都合が発生するのは、まだ隠れた従属が残っているからです。
注意深く「社員テーブル」を見てみると、
{部署コード} → {部署}
という従属関係が見えます。
{会社コード、社員ID} → {部署コード}
という従属関係も明らかです。つまりは、
{会社コード、社員ID} → {部署コード} → {部署}
ということが言えます。このように段階的な従属関係を、
「推移的関数従属」と言います。
下記のように社員テーブルと会社テーブルと部署テーブルに分割しました。
これにより、非キー列は主キー列に対してのみ従属するようになり推移的関数従属もなくなりました。
これにより先ほど問題であった、社員情報なしに部署を登録することが可能となりました。
第3正規形の定義とは、
「第2正規形のテーブルから、推移的関数従属している列が切り出されたもの」です。
正規化の功罪
正規化のメリットとしては、
データを一元管理し、データの整合性を保ちやすくなります。
その反面、正規化によりテーブルの数が増える事で、結合操作がより必要になり
検索SQLのパフォーマンスが劣化すると言ったデメリットがあります。
検索SQLと正規化のパフォーマンス関係はトレードオフであり、
第1正規化、第2、第3 ... と正規化が進むにつれて、
検索パフォーマンスは低く、データの整合性は高くなります。
結論としては、正規化された方が良く、非正規化は最後の手段として考え、
他の手法によってパフォーマンスが向上されないか検討し切羽詰まった時にとる最終手段と言われています。
最後に〜
Wizではエンジニアを募集中です。
興味のある方は是非覗いてみてください!↓
