はじめまして！VISITS Technologiesでエンジニアをしている@ham0215です。
主にRuby on Railsを使ってバックエンドを開発しています。
VISITSのAdvent Calendarなので弊社の紹介も兼ねて、VISITSのプロダクトのアサイン状況を管理するテーブルを作りながら正規形を学んでいきたいと思います。
...とは書きましたが説明のしやすさやプライバシーなどを考慮して役割や登場人物などは多少改変しています。
ご了承ください
正規形を勉強していると部分関数従属性や推移的関数従属性などの堅苦しい用語がたくさん出てくると思いますが、この記事ではそういった用語は極力使わずに具体例を使って説明していこうと思っています。
テーブル作成
それではここから具体的にプロダクトのアサイン状況を管理するテーブルを作成していきます。
データとして持ちたい項目は下記です。
- プロダクトの情報
- プロダクトID
- プロダクト名
- 役割：プロダクト開発する上で必要な役割。プロダクト内でユニークになること
- 役割の説明
- メンバー情報
- ユーザーID。オートインクリメント
- ニックネーム。メンバー間でユニークになること
- 事業部：メンバーが所属している事業部
- 事業部の説明
非正規形
まずはアサイン状況を１つの表で書いてみます。
アサインされているメンバーがいない場合、メンバー情報のカラムはNULLにしています。
アンダーバーがついているカラムはプライマリーキーです。
プロダクトID、プロダクト名１つに対して役割から右のデータが複数紐づいています。
このように1列の中に繰り返し項目がある場合に非正規形と呼ばれます。
このままではRDBにテーブルを作ることはできないので正規化していきましょう。
（実際にはjsonや配列を格納できるカラムを使うことで繰り返し項目を含んだテーブルを作ることはできますが制約などが使えないのでおすすめしません）
第1正規形
繰り返し項目があるとRDBのテーブルが作れないので、繰り返し項目をなくしました。
非正規形の場合はプロダクトIDだけで列が特定できたのですが、繰り返し項目をなくすることでプロダクトIDだけでは特定できなくなったのでプロダクトID・役割をプライマリーキーに変更しました。
これで各カラムがスカラ型になったので様々な制約を使うことができます。
ただ、プロダクトIDやプロダクト名など同じデータが入っているカラムがあり冗長ですね。
冗長な項目があると、例えばhello visitsの名称が変わって更新したい場合などに複数の列を更新しなければいけません。
第2正規形
冗長な項目をなくしていきましょう。
冗長な項目をなくすときの考え方はキー以外の項目がどのキーに依存しているかを考えます。
現状、キーはプロダクトIDと役割です。
プロダクト名はプロダクトIDが決まれば一意に定まります。
役割の説明は役割が決まれば一意に定まります。
その他の項目はプロダクトIDと役割が決まらないと一意に定まりません。
プロダクトID、役割それぞれのキーだけで特定できる項目とその他の項目でテーブルを分割します。
こちらはプロダクトIDがキーとなっているテーブルです。
「プロダクト」テーブルと呼ぶことにします。
こちらは役割がキーとなっているテーブルです。
「役割」テーブルと呼ぶことにします。
分離した項目を除いた「アサイン状況」テーブルです。
第１正規形の時はhello visitsの名称が変わった場合に5レコード更新する必要がありましたが、第２正規形では1レコード更新するだけでよくなりました。
ただ、まだまだ冗長な項目ありそうですよね。
第3正規形
さらに冗長な項目をなくしていきましょう。
次に冗長な項目をなくすときの考え方はキー以外の項目が他のキー以外の項目を一意に定めることができないか考えます。
アサイン状況テーブルをみてみると、ユーザーIDが決まればニックネームや事業部が一意に定まります。また事業部が決まれば事業部の説明が一意に定まります。
このようにキー以外の項目で一意に定めることができる項目を分離します。
※第2正規形から変更のないテーブルは省略しています。
こちらはユーザーIDがキーになっているテーブルです。
「ユーザー」テーブルと呼ぶことにします。
こちらは事業部がキーになっているテーブルです。
「事業部」テーブルと呼ぶことにします。
分離した項目を除いた「アサイン状況」テーブルです。
第3正規形まで正規化すると冗長な項目はほぼほぼなくなります。
よく正規化を勉強していると「第3正規形までは業務で使うから勉強しておきましょう」のようなことが書かれていることがあります。
このように言われる理由は、第3正規形までは冗長な項目をなくすことを主な目的としており、やらないメリットが少なく、RDBを使うシステムでは第3正規形にすることが多いためだと思われます。
それではさらに先の正規形を考えていきましょう。
第4正規形
続いてアサイン状況のテーブルをみてみましょう。
今まではある項目が定まれば一意に定めることができる項目に着目して分割してきましたが、
今回はある項目が定まれば一意の「集合」を定めることができる項目に着目します。
このテーブルの場合、プロダクトIDが定まれば役割の集合が定まります。
例えばプロダクトID=Vの場合は、役割=[PM, Golang, React, Swift, infra, QA]に定まりますね。
上記のような関係を持っているカラムを分割すると下記のようになります。
まずはプロダクトIDと役割のテーブルです。
「プロダクトに必要な役割」テーブルと呼ぶことにします。
元々はアサイン状況を管理する１テーブルでしたが、これはプロダクトに必要な役割を管理しているテーブルができました。
続いてアサイン状況テーブルです。
項目変わってないじゃないか！役割は分割したから役割カラムは不要なのではないのか？と思われるかもしれませんが、
もし役割がなければどの人がどの役割なのかを判断する情報が失われてしまうので、元々のアサイン状況テーブルを再現することができなくなります。
ただし、カラムは変わっていませんがユーザーがアサインされていないレコードはプロダクトに必要な役割テーブルを見ればわかるので削除することができました。
第4正規形にするメリットですが、上記の例でプロダクトに役割が増えた時を考えてください。
プロダクトID=Vに役割Rubyが増えたとします。まだ担当者は決まっていません。
この場合にプロダクトのアサイン状況は変わらないですが今まではアサイン状況テーブルにinsertする必要がありました。
第4正規化したテーブルの場合はプロダクトに必要な役割管理テーブルにinsertするだけでよくなりアサイン状況テーブルを触る必要がなくなります。
データベースでは更新処理を行う時に対象テーブルの一部または全体をロックします。
（今回の例では考えにくいですが）大量に役割を追加するときにアサイン状況テーブルをロックすることなく更新できることがこのテーブル分割のメリットだと思います。
大量トランザクションを扱うサービスの場合、ロックテーブルを局所化することはかなり重要なのでそういうケースでこの考え方が役立つと思います。
もう一つ、第3正規形のアカウント状況テーブルの保持しているデータの意味を考えると「メンバーのアサイン状況」だけではなく「プロダクトに必要な役割」（ユーザーID IS NULLのレコード）も保持していたと考えることができます。
1つのテーブルが持つデータは1つの意味にした方がシンプルに保ちやすいので、これも第4正規形にするメリットの1つだと思います。
ボイス・コッド正規形
ここまでにメンバーと役割の関係について明記していませんでした。
「メンバーは1つだけ役割を持つ」という条件があるとしましょう。
この条件を踏まえてアサイン状況テーブルを別の観点で見てみます。
今まではキー以外の項目を一意に定める項目を探してきましたが、キー項目を一意に定める項目がないか探してみます。
アサイン状況テーブルでは今回の条件を追加したことでユーザーIDが定まれば役割が一意に定まるようになりました。
このような状況を解消するために分割すると下記のようになります。
左はアサイン状況、右はメンバーの役割を表しています。
メンバーの役割テーブルはメンバーと役割が1対1なので、ユーザーIDのみをキーとしています。
ボイス・コッド正規形にするメリットですが、今回追加した「メンバーは1つだけ役割を持つ」という条件をユニーク制約で制御することができるようになることです。
元のテーブルではプロダクトIDと役割がキーだったので同じメンバーを複数の役割に紐づけることができましたが、分割後のテーブルではユーザーIDがキーになっているのでメンバーに複数の役割を紐づけることができません。
DBの制約が使えない場合、DBを利用する側で気をつけなければいけないのでプログラムやオペレーションが複雑になってしまいます。
データに条件がある場合、条件を満たさないデータは制約で格納できないようにしておくと利用者側で気をつけることが減るのでバグやミスを軽減することができます。
ただ、正規化をすればするほどjoinを多用しなければいけないシーンが増えるので、この記事では触れませんがパフォーマンスなども考慮するようにしましょう。
また、テーブルを分割するときは必ず元のデータが再現できることを確認しましょう。
今回の例の場合、もしユーザーが複数役割を持つことができるとしたら同じように分割はできません。仮にユーザーID=8のユーザーがRubyとPythonの役割を持っているとするとプロダクトIにRubyとしてアサインされているのかPythonとしてアサインされているのか判断できなくなってしまいます。
第5正規形
第5正規形の話を進める前に、ここまでで分離してきたテーブルの意味を整理します。
ボイス・コッド正規形の説明時に追加した「メンバーは1つだけ役割を持つ」という条件は第5正規形を説明する上で邪魔なので現実世界では複数言語扱える方がたくさんいて現実に合わないので一度忘れてください。
代わりに下記の条件を加えます。
- メンバーは複数の役割を持つことができる
- メンバーは複数のプロダクトにアサインすることができる
条件を変更したためボイス・コッド正規形で作成したアサイン状況テーブルは使えなくなります。
そこで第4正規形の時に作成したアサイン状況テーブルを基に追加した条件を満たすようにデータを更新すると下記のようになります。
変更点は下記の通り
- 複数の役割ができるメンバーは全ての役割を追加
- ユーザーID=6は複数プロダクト掛け持ちするように変更
- プロダクトIDと役割だけではユニークにならないのでユーザーIDもキーに追加
- カラム順はわかりやすさのために入れ替えただけ
[補足]
このテーブルの「役割」はプロダクトに所属しているユーザーができる全役割を表現しています。正規化の過程で別テーブルにしたプロダクトに必要な役割とは一致しないのでご注意ください。
例えばプロダクトVにはPHPは必須な役割ではないが、ユーザーID=4のメンバーがPHPの役割が可能なのでアサイン状況テーブルには役割＝PHPのレコードが存在しています。
前置きが長くなりましたが、このテーブルを第5正規形にしていきます。
今回はデータ表現している意味を考えていきます。
アサイン状況テーブルからは下記の情報がわかります。
- あるプロダクトにアサインされているメンバー
- あるプロダクトに揃っている役割
- あるメンバーが可能な役割
情報を1つずつ具体的にみていくと冗長な項目が見えてきます。
- プロダクトVにはユーザーID=4のメンバーがアサインされている。という情報が3レコードある。
- ReactができるメンバーがプロダクトVにいる。という情報が2レコードある。
- ユーザーID=6のメンバーはQAの役割ができる。という情報が3レコードある。
冗長なデータがあると、データ更新時に複数レコード更新する必要が出てきます。
これを解消するためにテーブルを正規化してみましょう。
このように分割すると、先ほど具体例で書いた1,2,3の冗長なデータがなくなります。
これで第5正規形になったのですが、ここで3つのテーブルの意味を考えてみます。
真ん中のテーブルはプロダクトにアサインされているメンバー、右のテーブルはメンバーが可能な役割です。
しかし、左のテーブルは意味が説明しづらいテーブルになっています。
今回の具体例の場合、プロダクトと役割の組でアサイン状況を管理する上で必要な情報はプロダクトに必要な役割です。そして、プロダクトに必要な役割テーブルはすでに作成済みなのでそれで代替することにしてこのテーブルは削除したほうがよさそう。
正規化ってなんだろう
最後のテーブル削除は第5正規形の話とはずれてしましましたが、正規化とは冗長なデータをなくしたり、更新箇所を局所化したり、テーブルの持つ意味をシンプルにしたり、つまりはシステム設計をシンプルにするための手法の一つです。
システム要件は様々であり、取得速度を上げるためにあえて正規形を崩して冗長にデータを持つことも多々あります。
ざっくりまとめると正規化をどこまでするかは状況次第となってしまうのですが、RDBを使う上で正規化は強力なツールであることは間違い無いのでRDBを使うなら学習しておくと良いと思います。
最後に
最後まで読んでいただきありがとうございます！
正規形を説明するために具体例で使っているデータを都合よく改変している箇所もありがたくさんあり、わかりづらい点も多かったと思います・・・
この記事を書くために正規形について再度調べたり、具体例やメリット・デメリットを調べたりしましたが、正規形の説明は抽象的な文言で書かれているので理解するのがとても大変でした。というより本当に理解できているのか未だに不安です。
また、ネットで見つかる記事の中には間違った情報も多いと感じました。
特に第4、第5正規形やボイス・コッド正規形は正しい説明なのか怪しいものも見かけました。
と書いている私の記事もその一つになっていないかとても不安です。何か問題点を見つけた場合はお手数ですがコメントしていただけるとありがたいです
この記事を通して少しでも正規形の理解に役立てたら嬉しいです。