はじめに
DBの設計について勉強してみて、DB設計にはハード分野の物理設計、DB内部の論理設計など幅広い知識が必要であることを知りました。私がその中でも「正規化」は学習コストも低く、最低限知識として持っていたほうが良いと感じましたのでまとめてみたいと思います。（正規形には６段階あるのですが今回は特に重要な第３正規形までの正規化をまとめます。興味のあるかたはそれ以降の正規化についても調べてみてください。下にリンクを載せておきます。）
正規化って何？
正規化は、データの冗長性をなくし、データの更新等の作業で無駄な作業が減るようにテーブルのフォーマットを整理をすることです。一言でまとめると 変更に強いDBを作るための決まり事 だと言えます。
また、正規化されたテーブルの形式を正規形と呼びます。正規化することによってDBのメンテナンスがしやすくなりデータの不整合性も起こりにくくなります。ただ文章だけですと理解しづらいので具体例をあげてみたいと思います。
以下は元々ユーザーのテーブル内で都道府県のデータを持っていましたがユーザーテーブルから都道府県を別のテーブルに切り出して正規化した例です。
まず、正規化される前のテーブルには次のような欠点があります。
- ユーザーが存在しない都道府県はデータとして登録できない
- データが冗長であること
この２点について説明してみたいと思います。
１．ユーザーが存在しない都道府県はデータとして登録できない
まず１の「ユーザーが存在しない都道府県はデータとして登録できない」ですが今回の例で使っている正規化前のテーブルでは「北海道」「青森県」「東京都」のみのデータしかありません。現実には他にも都道府県があるわけです。しかしながら都道府県のデータを登録しようにもユーザーが存在したいためにデータとして登録できません。しかし、正規化後のテーブルであれば都道府県のデータは「都道府県テーブル」に切り出されているのでユーザーの存在に関係なくデータの登録ができます。
２．データが冗長であること
続いて２の「データが冗長であること」ですが正規化前のテーブルには「東京都」が２行に存在しています。これが「データが冗長である」ということなのですが、これの何が問題なのかということを考えてみます。例えばもし「東京都」が「東京県」に変更されたとします。この場合２行のレコードのデータを「東京都」から「東京県」に変更しなければなりません。
つまり、
更新しなければならないレコード数 ＝ 「東京都」が含まれているレコード数
となります。
更新するレコードの数は「東京都」が含まれたすべてのレコードの数になるわけです。これが数万のレコードにわたって更新しなければならない状況が訪れた場合を考えるとデータの冗長性の何が問題なのか容易にわかると思います。
続いて、正規化後のテーブルを見てみましょう。
正規化後のテーブルでは都道府県データがユーザーテーブルから「都道府県テーブル」に切り出されています。そのため、県名の変更という作業は都道府県テーブルの「東京都」たった１行を「東京県」に変更するだけでデータの更新が終わります。もちろんレコードの数が数万とあってもです。
正規化は「無損失分解」である
正規化には「無損失分解」という特性があります。この無損失分解とは正規化前のテーブルの状態に戻せることです。
先ほどのテーブルの例であればユーザーテーブルの県名IDを外部キーにして都道府県テーブルのIDと結合を行うことで正規化前のテーブルの状態に戻すことができます。
主な正規化のメリットについて理解できたかと思います。つづいて正規形の種類と正規化の手順について説明していこうと思います。
正規形の種類
正規形には第１正規形から第５正規形までの５種類＋ボイスゴット正規形（第3正規形と第4正規形の間の正規形）の合わせて６種類があります。順にテーブルの正規化をすることでデータの整合性が高まり見通しの良いDB設計ができます。
以下、第３正規形までを順に紹介していきたいと思います。
第１正規形 「スカラ値の原則」
第１正規形は「スカラ値の原則」に基づいて正規化することです。スカラ値とは「単一の値」という意味です。一つのセルには値は一つでなければならないという原則です。これは次のテーブルを見てもらえばすぐにわかると思います。
上のテーブルは親と子の情報が入ったテーブルです。正規化前のテーブルには一つのセルに２つのデータが入った部分があります。この部分が「スカラ値の原則」が守られていないということです。この部分を解消し右のテーブルのようにします。
関数従属
今回は、第１正規形のテーブルができましたがここでこのテーブルには大きな問題点があります。それは主キーが決められないということです。ここで数学で学生時代に見たであろうy=f(x)の関数を思い出していただきたいです。関数はxが決まればyが決まります。この性質を「関数従属姓」というのですがテーブルの設計においても関数従属姓を満たさなければなりません。
テーブルにおいては 'x'が主キー 'y'が主キーから決まる属性となります。このことをDBでは次のように表します。
{x} → {y}
第１正規化によって「スカラ値の原則」は満たされましたが関数従属でなくなってしまいました。このことを解消するために親のテーブルから子を別のテーブルに切り出すことで関数従属になります。
{親.ID} → {親.名前}
{子.ID} → {子}
もちろん結合によって元のテーブルに戻すことができます。(無損失分解)
第２正規形 「部分関数従属の解消」
第２正規形は「部分関数従属」を解消することによって求められます。「部分関数従属」とはある列が主キーの一部に対して関数従属があることです。以下の資料を見てみましょう。（＊がついてる列は主キー）
今回の場合ですと{会社コード,社員ID}が主キーですが「会社名」(主キーの一部)が「会社コード」に従属しています。そのためこの部分を別のテーブルに切り出すことですべての列が主キーに従属することになり部分関数従属が解消されます。もちろん無損失分解ですので会社コードを外部キーに会社テーブルと結合することが可能です。
「部分関数従属」の問題点
これは冒頭でも述べた「都道府県」の内容と同じですが「社員テーブル」に社員が存在しない会社名を登録できないこととデータの冗長性によって更新が難しくなることです。第２正規形に正規化することによってこれらの問題点は解消され、社員がいない会社名も登録できますし、会社名が変わったとしても変更する会社名のみの変更で済みます。
第３正規形 「推移的関数従属の解消」
第３正規形は「推移的関数従属の解消」です。少しややこしいのですが推移的関数従属はすべての列が主キーに従属しているが、主キー以外の列で関数従属があることです。
今回のテーブルではすべての列は主キーに従属しています。しかしながら｛部署コード｝ → ｛部署名｝が関数従属です。
つまりこの {社員ID(主キー)} → {部署コード} → {部署名} の部分が推移的関数従属になっています。
({部署コード} → {部署名})を別のテーブルに切り出すことで「推移的関数従属」が解消されます。
推移的関数従属の問題点
推移的関数従属の問題点は「部分関数従属」と同じです。社員が存在しない部署名は登録できないこと、データの冗長性です。ですので今回の場合も「部署テーブル」という別のテーブルに分けることでこの問題は解消されます。
第４正規形以降の正規形
第３正規形以降の正規化には「第４正規形」「第５正規形」「ボイスコッド正規形」がありますが第３正規形までができればそれ以降の正規化も自然とできることが多かったりするので興味のある方は下記のリンクが参考になりますので見てみてください。
正規化と非正規化のトレードオフ
ここまで正規化についてまとめさせていただきましたが実は正規化にはデメリットがあります。それは「パフォーマンスの悪化」です。正規形の次数が上がっていくとテーブルの分割が増えるためデータの検索の際にテーブルの結合を行わなければなりません。この点、非正規化であればテーブルの結合を行わない分早く検索が行えます。ですので一概に「必ず正規化をしなければならない」とは言えません。
手順としてはまず正規化する。その後様々なパフォーマンスチューニングをした結果どうしてもパフォーマンスが出ない場合の「最後の手段」として非正規化を考えるようにするといいかもしれません。私自身まだ実務経験がないので実務経験を積む中でトレードオフに対するバランス感覚を身に着けていきたいと思います。
最後に
今回はDBの設計で特に重要な正規化についてまとめさせていただきました。私自身まだまだ経験不足な点がございますのでわかりにくい所や誤りがありましたらお気軽にコメントしてください。最後まで読んでいただきありがとうございました。