とある案件でやりたいことがうまくできなかった
とある商品に関連する紹介ページに対して、その商品に関するジャンルを紹介用文言の中に表示するというものなのですが、これを表示させるのにDB上のフラグを0or1で取る他、商品IDとジャンルIDが別のテーブルに存在するため、どうやって取ってくるか悩むというものです。今思えば、前に別の案件で名前とそれに関連するIDを調べる際に直にSQLクエリでjoin（テーブル結合）させたことがあるので、中間テーブルの存在まではいかなくとも、joinさせればいい、という考え方にいきたかったですね。ここにたどり着けなかった理由は、ORMであるActiveRecordとにらめっこしていたからなのですが、これに関してはまた別途記事にしていきたいと思います。
さて、とうわけで今回、圧倒的に技術知識不足を痛感した瞬間がタイトルにもある、データベースの正規化と中間テーブルです。
筆者がイメージし易いように例としてあげるDBテーブルに全てジョジョを採用しています。ジョジョ（主に今やってる第５部）を知らないと何このカラム？となる恐れがあります。
もしかしたらどこか解釈間違っているかもしれません・・・お気づきになられた方、ぜひ指摘をお願いします！！
そも、非正規化のデータベースは何が問題で、正規化が重要なのは何故か？
一言で言ってしまうと、データの重複や整合性を保つためにデータベースを設計する必要があるからである。正規化によって、データの更新、追加、などがやり易くなる。やり易くなる理由は、非正規なデータベースで起き得る問題を解決してくれるからである。
具体的にどんな問題が起こるのかというと、
| id | character_name | gang_team |
|---|---|---|
| 1 | Blono Bucciarati | underling_team |
| 2 | Leone Abbacchio | underling_team |
| 3 | Peschi | assassin_team |
上記のような顧客テーブルがあったとして、gang_teamカラムのunderling_teamが重複しています。これを繰り返し項目と呼ぶ。
こういったデータベースで起こり得る弊害は、
- 事前登録不可
- 重複更新/重複登録
- 関係性の喪失
が挙げられる。
###事前登録不可
例えば、先のテーブルの場合、gang_teamを登録するためには、character_nameも用意しなければならないということ。加えて。character_nameに属するデータがない場合、gang_teamを作れない弊害もある。
こんな状態にするならば、テーブルを分けて後でテーブル結合した方が合理的・・・と、早くもテーブル結合の利点と直結する感じ。
重複更新/重複登録
例えば、ブチャラティが幹部クラスに昇進したので、ブチャラティチーム全員をgang_teamのunderling_teamからexecutive_teamへと更新する必要が出ます。ブチャラティが幹部になったおかげで、ブチャラティのチームにいる皆んながUndeling_team（下っ端チーム）からexecutive_team（幹部チーム）へと昇格するからです。
この時に、gang_teamのunderling_teamを更新したいと思った時に、上記のテーブルだと２つのレコードに対してUpdateをしなければならないので、もし、更新に抜け漏れがあった場合、データの整合性が取れなくなる(重複更新)。別テーブルで分けておけば、更新はもっと楽になるなーというのもまた頷けるところ。また、データベースに新たなギャングスタがunderling_teamの所属だった場合、またunderling_teamと同じデータを入れなくてはならなくなる(重複登録)。
関係性の喪失
上記のテーブルのように、Peschiを削除する場合、一緒にassassin_teamというgang_teamまで消失してしまう。これは、かつて存在していたgang_teamを残すことができない設計である。故に、データの整合性に欠けるデータベース、つまり設計の弱いデータベースと言える。
以上の理由から、データベースの正規化は非常に重要であるということが理解できる。
正規化における基本概念
正規化されたデータベースを見る上で必要になってくる基本視点。
### 関数従属性
上記のテーブルで言えば、idを使えば、character_nameを特定することができる。もし仮に同姓同名の人間がいたとしても、idを使えば調べることができる。こう言った関係を関数従属性と呼び、関係性を→で表す。
id → character_name
これは、『character_nameが、idに関数従属している』ことを意味する。
関数の従属性には以下のような推論の法則が働く。
反射律
ベン図を考えると、イメージしやすい。例えば、柴犬が犬の部分集合だったとする。すると、それは、犬→柴犬という従属関係が成立することを意味する。そして、柴犬が成立すれば、犬であることが成立するということも意味する。
増加律
増加律からは、下記のテーブルを使って考える。
| id | character_name | stand_name |
|---|---|---|
| 1 | Giorno Giovana | Gold Experience |
| 2 | Guido Mista | Sex Pistols |
| 3 | Narancha Ghirga | Aerosmith |
id→character_nameが成立するのならッ、別のカラムを加えたとしてもその従属関係は崩れないッッ！
{id, stand_name}→{character_name, stand_name}が成り立つということだッ。
推移律
先のテーブルを使って同じく、これも考えてみる。
id→character_nameかつ、character_name→stand_nameならば、id→stand_nameである。ということ。
1に従属するGiornoとGiornoに従属するGold Experienceならば、Gold Experienceは1に従属するッてことじゃあないかッ！
合併律
さっきの推移律の理屈を使うと・・・
id→character_nameかつ、id→stand_nameならば、id→{character_name, stand_name}ということができる。
分解律
単純に合併律の逆なだけだッッ！！
### 完全関数従属と部分関数従属と推移関数従属
関数従属性には種類がある。カラム同士の関係性には、相互的なものと、そうでないものがある。
完全関数従属
| faculty | student_ID | student_name | addres | country |
|---|---|---|---|---|
| Medical | 1 | Chocolata | Roma ×××-×× | Italy |
| Archeology | 1 | Jonathan Joestar | London ○○○-○○ | United Kingdom |
| Marine_biology | 4 | Kuujou Joutarou | Moriou-town △△ | Japan |
{faculty, student_ID}→student_nameという関係になるが、２つのカラムを利用しなければ、生徒の名前が分からないようになっている。上記のテーブルではスペースの問題のため書いていないが、医学部には他にもたくさん生徒がいるだろうし、クラス番号が1の生徒は、他の学部にもいるということを考えると、facluty→student_IDや、student_name→student_IDは成立しないということになる。両方が存在しない限り、検索をかけられない関数従属を、完全関数従属と呼ぶ。
部分関数従属
部分関数従属は、{student_ID, student_name}→addressのような関係のことで、完全関数従属と異なり、どちらか一方が成立していれば、従属関係を再現できる。上記のテーブルでは、student_ID→addressは成り立たないが、student_name→addressは成立する。
推移関数従属
１つの従属関係が導き出されることによって、成立し、最初と最後のカラム同士では従属関係が結ばれないようになる従属関数。ここでは、student_name→addressであり、この関係が成り立つということは、address→countryが導き出される。つまり、student_name→address→countryということになり、student_nameからcountryが導き出されることはない従属関係があることを意味する。
### キー属性
キー属性とは、今までやってきたカラムの従属性を導くためのポイントになり得る性質を持つということを意味する言葉です。
候補キー
テーブルに於いてカラムとカラムの組み合わせによってレコードを特定できるようになるカラムのこと全般を指す。
主キー（primary_key）
候補キーの中で、1レコードから、最も多くのもしくは全てのフィールドを取ってこれるように設計されたカラムを指す。
代替キー
候補キーの中で主キー程ではないが、特定するのに使えるカラムを指す。
非キー
候補キー以外のカラムを指す。
外部キー
他のテーブルの主キーや候補キーを参照できるカラムを指す。
キーの種類から、上記の２つのテーブルにおけるカラムが何キーになるか見ていきます。
以下の２つのテーブルを用いて見ていきます。
| id | character_name | stand_name | style |
|---|---|---|---|
| 1 | Risottot Nero | Metallica | solo |
| 2 | Formaggio | little feat | solo |
| 3 | Prosciutto | the grateful dead | pair |
| 4 | Tizano | talking-head | pair |
| id | tel_num | gang_team |
|---|---|---|
| 1 | 090-2243-4324 | assassin_team |
| 2 | 090-3432-4255 | assassin_team |
| 3 | 090-3457-8456 | assassin_team |
| 4 | 020-1242-2466 | boss_superiors |
まず、候補キーの内、主キーは間違いなくidです。そして、候補キーの内、character_nameや、stand_name、tel_numは代替キーになり得ると思います。gang_teamやstyleは非キーになると思われます。
また、別テーブルを参照する時に使う外部キーは主キーでもあるidになります。
ここまでが、正規化の必要性と、正規化するにあたり持っていなくてはならない前提知識になります。
正規形には段階がある
ようやく、ここから正規化理論に本格的に入っていきます。正規形には段階があり、非正規形、第一正規形、第二正規形、第三正規形、第四正規形、第五正規形からなる。数字が上に進むにつれ、正規化が進んでいく。レベル感覚でいくと、全部第五正規形でいいじゃないかと思われるかもしれないが、正規化が進めば進むほど、データがテーブルで分けられ、テーブルの数が増えるということになりデータ取得の際のレスポンスがダウンするというデメリットが発生するので、扱うデータによってベストな正規形を選択する必要がある。
今回は、第三正規形までをアウトプットします。残りはまたおいおい加筆していきたいと思います。
非正規形
非正規形のデータベースは以下のような繰り返しグループがあるデータベースのことを指します。繰り返しグループとは、繰り返し項目とは違い、同一レコード内に複数のデータが入り込んでいる状態を指す。この状態を非正規形と呼ぶ。繰り返し項目のあるデータベースは非正規形とは呼ばないので、区別をしっかりしておくこと。
| id | stand_user | stand_name | power | speed | range | persistence | precision | Growth | gang_num |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Vinegar Doppio | King Crimson | A | A | C | C | E | C | 0002 |
| Diavolo | 0001 | ||||||||
| 2 | Ghiaccio | White Album | A | C | C | A | E | E | 0035 |
| 3 | Pannacotta Fugo | Purple Haze | A | B | C | E | E | B | 0456 |
stand_userにはVinegar DoppioとDiavoloが一緒になって入っています。これが非正規データベースです。
第一正規形
第一正規形は、非正規形データベースの繰り返しグループを修正してあげるだけの状態を指す。つまり、先の非正規形データベースを例にすると、
| id | stand_user | stand_name | power | speed | range | persistence | precision | growth | gang_num |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Vinegar Doppio | King Crimson | A | A | C | C | E | C | 0002 |
| 2 | Diavolo | King crimson | A | A | C | C | E | C | 0001 |
| 3 | Ghiaccio | White Albu | A | C | C | A | E | E | 0035 |
| 4 | Pannacotta Fugo | Purple Haze | A | B | C | E | E | B | 0456 |
ということになる。
第二正規形
ここから、テーブルを分離させていく。第一正規形では、部分関数従属がスタンドのステータスを表すpower~growthまで存在した。{id, stand_name}→power...growth
故にこれを全て完全関数従属になるようにテーブルを分ける。
Stand_Userテーブル
| id | stand_user | gang_num |
|---|---|---|
| 1 | Vinegar Doppio | 0002 |
| 2 | Diavolo | 0001 |
| 3 | Ghiaccio | 0035 |
| 4 | Pannacotta Fugo | 0456 |
Standテーブル
| stand_user | stand_name | power | speed | range | persistence | precision | growth |
|---|---|---|---|---|---|---|---|
| Vinegar Doppio | King Crimson | A | A | C | C | E | C |
| Diavolo | King crimson | A | A | C | C | E | C |
| Ghiaccio | White Album | A | C | C | A | E | E |
| Pannacotta Fugo | Purple Haze | A | B | C | E | E | B |
部分関数従属性を排除してあるデータベースを第二正規形という。
第三正規形
第二正規形の状態から、推移関数従属性を排除したものを第三正規形と呼ぶ。今回の場合は、stand_userからstand_nameへ、そして、そこからスタンドのステータスを示すpower~growthを割り出すことができるという推移関数従属性が存在するので、ここの関係を削除していく。
Stand_User-gang_numテーブル
| id | stand_user | gang_num |
|---|---|---|
| 1 | Vinegar Doppio | 0002 |
| 2 | Diavolo | 0001 |
| 3 | Ghiaccio | 0035 |
| 4 | Pannacotta Fugo | 0456 |
Stand_user-Stand_nameテーブル
| stand_user | stand_name |
|---|---|
| Vinegar Doppio | King Crimson |
| Diavolo | King Crimson |
| Ghiaccio | White Album |
| Pannacotta Fugo | Purple Haze |
Stand_name-Stand_statusテーブル
| stand_name | power | speed | range | persistence | precision | growth |
|---|---|---|---|---|---|---|
| King Crimson | A | A | C | C | E | C |
| White Album | A | A | C | C | E | C |
| Purple Haze | A | B | C | E | E | B |
これで第三正規化されたデータベースができたことになります。
中間テーブルについて
今回の場合、Vinegar Doppio,Diavoloとpower~growthが多対多関係ですので、ここに中間テーブルを作成できます。
中間テーブルは、多対多のテーブルが存在する時に作られるもので、２つのカラムの関係性をこの関係性を示したい２つ以外のカラムで共通しているカラムを用いてそぞれを分類し、JOINさせて出来上がります
この中間テーブル用のIDも割り振ります。
中間テーブル
| id | stand_user | power | speed | range | persistence | precision | growth |
|---|---|---|---|---|---|---|---|
| 1 | Vinegar Doppio | A | A | C | C | E | C |
| 2 | Diavolo | A | A | C | C | E | C |
| 3 | Ghiaccio | A | A | C | C | E | C |
| 4 | Pannacotta Fugo | A | B | C | E | E | B |
参考文献
『データベース技術 第6版』 アイテック情報技術教育研究所編著