はじめに
最近DB設計をする機会がありました。
自分が作成したテーブル定義に対して、「なんでそーしたの？」と聞かれた時にうまく答えられず
肌感覚でDB設計をしていることを痛感しました。
ロジカルに設計できるようになるための第一歩として
明確な理論がある正規化について、理解を深めようと思います。
対象読者
正規化をなんとなくやってる方
対象範囲
第１〜３正規形について
正規化
正規化をするモチベーション
そもそも、なぜ正規化をする必要があるのでしょうか？
まず、自分自身で目的を明確に認識していませんでした。
正規化の目的は、はっきりしています。
データの異常を防ぐためです。
その異常が発生する原因が、データの重複です。
つまり、正規化でやることはデータ整合性を高くするために、
重複をなくしていく作業のことをいいます。
正規形の種類
正規化には段階があり、それを正規形(Normal Form、NF) と呼びます。
- 第１正規形
- 第２正規形
- 第３正規形
- ボイスゴット正規形
- 第４正規形
- 第５正規形
- 第６正規形
正規形の最大の特徴は、より高いレベルの正規形は下位レベルを含んでいることで
例えば、第５正規形であれば、第１〜４正規形の条件を満たしている状態になります。
また、一般的にDB設計をする際は、第３正規形まで行えば十分という意見が多いです。
第１正規形（1NF）
非正規形からの第一歩である第１正規形についてですが、
プロダクトに関わったことのあるエンジニアの方であれば、雰囲気でわかるかも知れません。
でも、「理論的に説明して下さい」と言われたらどうでしょう。
条件はこちらになります。
1NFの条件
- 重複する行が存在しない
- 行と列の交差点に１つの値が含まれる
条件の内容 と どんな問題あるのか をセットに解説していきたいと思います。
重複する行が存在しない
いたってシンプルに、
テーブルに全く同じレコードを含んではいけないということです。
どんな問題が発生するか？
こんなテーブルがあったとします。
【ワンピース登場人物テーブル】
| 名前 | 海賊名 | 悪魔の実 | 身長(cm) | 出身地 |
|---|---|---|---|---|
| モンキー・D・ルフィ | 麦わらの一味 | ゴムゴム | 174 | 東の海 |
| ポートガス・D・エース | 白ひげ海賊団 | メラメラ | 185 | 南の海 |
| ポートガス・D・エース | 白ひげ海賊団 | メラメラ | 185 | 南の海 |
重複してますね。
このようなクエリを作成した時に問題が発生します。
SELECT AVG(身長(cm)) FROM 登場人物;
身長の平均値を求めるクエリですが、
重複してるため平均値は間違ったデータになります。
つまり、重複した行があるとRDBの集約関数の結果に異常が発生します。
行と列の交差点に１つの値が含まれる
仮に、行と列の交差点を「セル」と名前をつけた時
セルの値に複数値を入れてはいけないということです。
例えば、カンマ区切りで複数の値を入れるようなテーブルは
第１正規形ではありません。
どんな問題が発生するか?
このような非正規形のテーブルがあったとします。
【サザエさん親子テーブル】
| 父 | 子 |
|---|---|
| 磯野 波平 | サザエ,カツオ,ワカメ |
| フグ田 マスオ | タラオ |
| 波野 ノリスケ | イクラ |
① SELECT時
検索条件をつけたクエリ作成時に問題が発生します。
カツオの父をSELECTしたい時はどうなるでしょうか？
おそらく、こんなクエリになると思います。
SELECT 父 FROM 親子 WHERE 子 LIKE '%カツオ%';
このクエリの問題点は、検索性能と速度になります。
-
検索性能
単語の先頭と末尾にワイルドカードを利用しているため
「カツオ」「イケカツオ」「オオクチイケカツオ」「ミナミイケカツオ」「カツオブシ」
すべて検索に引っかかります。 -
速度
先頭にワイルドカードを使用しているため、検索が遅くなります。
なぜかと言うと、インデックス(索引)を使用できないからです。
② UPDATE時
子カラムを更新時の値作成ロジックが複雑になる問題です。
ロジックが複雑になるということは、データ整合性が低くなることを意味します。
例えば、サザエ,カツオ,ワカメに対してジンベイザメを追加しようとすると
UPDATE 親子 SET 子 = 'サザエ,カツオ,ワカメ,ジンベイザメ' WHERE 父 = '磯野 波平';
サザエ,カツオ,ワカメ,ジンベイザメという値を
アプリケーション側でロジック処理をすることになります。
具体的に言うと、
- SELECT
サザエ,カツオ,ワカメを取得 -
サザエ,カツオ,ワカメにジンベイザメ追加したstringを作成 - UPDATE
なんてことない処理だと思うかも知れませんが、
今後「順番を入れ替えたい」「任意の名前を変えたい」などの新たな要件が追加された時
開発コストが上がり、よりロジックが複雑化してしまいます。
第１正規形にすると
【非正規化】
| 父 | 子 |
|---|---|
| 磯野 波平 | サザエ,カツオ,ワカメ |
| フグ田 マスオ | タラオ |
| 波野 ノリスケ | イクラ |
↓
【第１正規化】
| 父 | 子 |
|---|---|
| 磯野 波平 | サザエ |
| 磯野 波平 | カツオ |
| 磯野 波平 | ワカメ |
| フグ田 マスオ | タラオ |
| 波野 ノリスケ | イクラ |
子に複数値を持たせるのではなく、複数レコードにすることで
先にあげた問題点は、解消されるのがわかると思います。
第２正規形（2NF）
第２正規形の説明するためには、前提知識が必要になるので解説します。
- 候補キー
- 関数従属性
候補キー
テーブルのレコードを一意に識別することが出来る単体、複数カラム（属性）のことです。
複数カラムの場合は、一意性を確保できなくなる組み合わせの最小でなくてはいけません。
実際のRDBでは、PRIMARY KEY・UNIQUE制約の対象にされていることが多いです。
関数従属性（Functional Dependency、 FD）
集合概念で部分集合の関係があるものです。
集合Aと集合Bがあった時、AがBを含んでいる状態で
Aの値がわかれば、Bの値が１つに求められる時「BはAに関数従属する（A→B）」といいます。
また、関数従属性は２つにパターンに分けられます。
完全関数従属性
- １つの属性Aがある場合にCが１つ決まる場合のこと。
- ２つの属性AとBがある場合にCが１つ決まり、 属性A,Bがセットでないと決まらない場合のこと。
いわゆる複合キーでユニークとなる場合です。
部分関数従属性
- ２つの属性AとBがある場合にCが１つ決まり、属性A,Bのどちらかの属性でCが１つ決まる場合のこと。
2NFの条件
2NFの条件は、こちらになります。
- 第１正規形であること
- 候補キーと非キー属性で部分関数従属性が含まれていないこと
部分関数従属性は、候補キーが複数の場合にしか含まれないので、
第１正規形 かつ 候補キーが１つ の場合は、自動的に2NFになります。
候補キーと非キー属性で部分関数従属性が含まれていないこと
こんなテーブルがあったとします。
【コミックテーブル】
| 作品名 | 作者 | 出身地 | 連載 | 出版社 |
|---|---|---|---|---|
| 呪術廻戦 | 芥見下々 | 岩手県 | ジャンプ | 集英社 |
| 名探偵コナン | 青山剛昌 | 鳥取県 | サンデー | 小学館 |
| HUNTER×HUNTER | 冨樫義博 | 山形県 | ジャンプ | 集英社 |
まず、下線が引かれたカラムが候補キーになります。
今回は作品名のみを候補キーにすると、同じ作品名のコミックがあり得るため、
作品名,作者を候補キーとし、コミックテーブルのレコードの一意性を担保することにしました。
その一方で候補キー以外のカラムが、非キー属性になります。
では、部分関数従属性を確認して行きたいと思います。
-
２つの属性AとBがある場合にCが１つ決まる
-
作品名,作者のセットで、出身地,連載,出版社は１つに決まります。
-
-
属性A,Bのどちらかの属性でCが１つ決まる
-
作品名で出身地,連載,出版社は１つに決まりません。 -
作者で連載,出版社は１つに決まりませんが、出身地は決まります。
-
つまり、作者と出身地に部分関数従属性があります。
どんな問題が発生するか?
ある作者であれば、１つの出身地という重複をしてるため、
データの更新異常が発生する原因になります。
このようにレコードが増えて行った後に、
| 作品名 | 作者 | 出身地 | 連載 | 出版社 |
|---|---|---|---|---|
| 呪術廻戦 | 芥見下々 | 岩手県 | ジャンプ | 集英社 |
| 名探偵コナン | 青山剛昌 | 鳥取県 | サンデー | 小学館 |
| HUNTER×HUNTER | 冨樫義博 | 山形県 | ジャンプ | 集英社 |
| 幽☆遊☆白書 | 冨樫義博 | 山形県 | ジャンプ | 集英社 |
| レベルE | 冨樫義博 | 山形県 | ジャンプ | 集英社 |
作者カラムの冨樫義博の出身地を更新する場合に
全てを１度に更新しないとデータ異常が起こってしまいます。
第２正規形にすると
このように、作者と出身地の部分関数従属性を解消するために新しいテーブルを作成して
元のテーブルを無損失分解します。
【正規化前】
| 作品名 | 作者 | 出身地 | 連載 | 出版社 |
|---|---|---|---|---|
| 呪術廻戦 | 芥見下々 | 岩手県 | ジャンプ | 集英社 |
| 名探偵コナン | 青山剛昌 | 鳥取県 | サンデー | 小学館 |
| HUNTER×HUNTER | 冨樫義博 | 山形県 | ジャンプ | 集英社 |
↓
【コミックテーブル】
| 作品名 | 作者 | 連載 | 出版社 |
|---|---|---|---|
| 呪術廻戦 | 芥見下々 | ジャンプ | 集英社 |
| 名探偵コナン | 青山剛昌 | サンデー | 小学館 |
| HUNTER×HUNTER | 冨樫義博 | ジャンプ | 集英社 |
【作者テーブル】
| 作者 | 出身地 |
|---|---|
| 芥見下々 | 岩手県 |
| 青山剛昌 | 鳥取県 |
| 冨樫義博 | 山形県 |
無損失分解とは、分解後のテーブルを利用して分解前のテーブルを再構築できることをいいます。
このように、正規化前のテーブルは、分解したテーブルを利用して復元できます。
SELECT c.作品名, c.作者, c.連載, c.出版社, b.出身
FROM コミック as c
JOIN 出身地 as b
ON c.作者 = b.作者;
無損失分解したことによって、更新時に
作者テーブルの出身地の１箇所を変更するだけ良くなり、データ整合性が高くなります。
第３正規形（3NF）
第３正規形の条件は、第２正規形と似ています。
3NFの条件
- 第２正規形であること
- 非キー属性の中で関数従属性が含まれていないこと
第２正規形では、候補キーと非キー属性で部分関数従属性を解消したのに対し、
第３正規形では、非キー属性の中で関数従属性を解消します。
非キー属性の中で関数従属性が含まれていないこと
こちらは、第２正規化後のコミックテーブルになります。
| 作品名 | 作者 | 連載 | 出版社 |
|---|---|---|---|
| 呪術廻戦 | 芥見下々 | ジャンプ | 集英社 |
| 名探偵コナン | 青山剛昌 | サンデー | 小学館 |
| HUNTER×HUNTER | 冨樫義博 | ジャンプ | 集英社 |
今度は、非キー属性である連載,出版社カラムに注目します。
連載の値が決まると、出版社は１つに決まる という関数従属性を含んでいます。
よって、第２正規形同様に無損失分解をすることで第３正規形となります。
どんな問題が発生するか?
こちらは第２正規形と同じなので割愛します。
第３正規形にすると
このように、連載と出版社の関数従属性を解消するために新しいテーブルを作成して
元のテーブルを無損失分解します。
【正規化前】
| 作品名 | 作者 | 連載 | 出版社 |
|---|---|---|---|
| 呪術廻戦 | 芥見下々 | ジャンプ | 集英社 |
| 名探偵コナン | 青山剛昌 | サンデー | 小学館 |
| HUNTER×HUNTER | 冨樫義博 | ジャンプ | 集英社 |
↓
【コミックテーブル】
| 作品名 | 作者 | 連載 |
|---|---|---|
| 呪術廻戦 | 芥見下々 | ジャンプ |
| 名探偵コナン | 青山剛昌 | サンデー |
| HUNTER×HUNTER | 冨樫義博 | ジャンプ |
【連載テーブル】
| 連載 | 出版社 |
|---|---|
| ジャンプ | 集英社 |
| サンデー | 小学館 |
第１〜３正規形についての解説は以上になります。
ありがとうございました。
まとめ
- 正規化の目的は、データの異常を防ぐこと
- 第１正規形は、行と列の交差点に１つの値が含まれること
- 第２正規形は、候補キーと非キー属性の間で部分関数従属性を解消すること
- 第３正規形は、非キー属性間で関数従属性を解消すること