この記事の内容
- 障害お問い合わせシステムを題材として、正規化の流れを解説
- 第1~第3正規形までの正規化を実践
補足
オライリージャパン社のSQLアンチパターン本を参考にしています。
正規化とは?
データベース設計において、冗長性や非一貫性を極力ださないようにする方法論が正規化です。
正規化とはなんぞやを話すと長くなるので、詳しくは別の記事で解説しています。
早速やってみよう!
実際にやって覚えた方が早いので、障害お問い合わせシステムを例に正規化してみます。
正規化をする際は、要件定義で決めた内容をもとに正規化することが重要です。
これは、要件によってテーブル構造が変わるためです。「テーブル同士は1対多の関係なのか?」、「どれが主キーなのか?」など、正規化は常に要件と照らし合わせて行います。
例として、 障害お問い合わせシステムの簡単な条件を以下に書きます。
【障害お問い合わせシステムの要件】
- あるアプリケーションの障害内容を登録できる
- 障害に対しタグを追加でき、誰がタグを追加したかわかる
- 障害対応するエンジニアが誰かわかる
- エンジニアの連絡先がわかる
非正規形
下のテーブルは、すべての要素をとりあえず1つにまとめただけの状態になります。
ここから正規化していきます。
非正規形
障害ID | 障害タイトル | タグ | タグ作成者 | 担当エンジニア | 担当エンジニア_email |
---|---|---|---|---|---|
D100 | ログインできない | LOGIN | ケニー | 初心者エンジニア | beginner@dummy.com |
D100 | ログインできない | PC | マー君 | 初心者エンジニア | beginner@dummy.com |
D200 | マウスが動かない | HARD | ジョン | 玄人エンジニア | master@dummy.com |
※実は上のテーブルはすでに第1正規形なのですが、不適切な状態のため、便宜上、非正規形と呼ばせていただきます。
第1正規形をつくろう
第1正規形とは「1つのセル(フィールド)に1つの値しか含まない状態」です。
非正規形をみると、タグ以外の値が重複している行があります。この状態では、主キーを決めることができません。例えば、「障害ID」「タグ」を複合主キーと考えても、「タグ」をつけない障害があるかもしれません。その場合、NULLの主キーが発生してしまいます。しかし、主キーはNULLを含めることができません。
そこで、タグの役割を持つカラムを分離してあげます。
第1正規形
障害
障害ID | 障害タイトル | 担当エンジニア | 担当エンジニア_email |
---|---|---|---|
D100 | ログインできない | 初心者エンジニア | beginner@dummy.com |
D100 | ログインできない | 初心者エンジニア | beginner@dummy.com |
D200 | マウスが動かない | 玄人エンジニア | master@dummy.com |
障害_タグ
障害ID | タグ | タグ作成者 |
---|---|---|
D100 | LOGIN | ケニー |
D100 | PC | マー君 |
D200 | HARD | ジョン |
1つの障害に複数のタグをつけられるため、「障害情報」と「タグ情報」は1対多の関係です。
また、障害_タグテーブルは、「障害ID」と「タグ」が複合主キーとなります。
ポイント
主キーにNULLを含むことはできない
第2正規形をつくろう
第2正規形は、部分関数従属を解消した状態です。
部分関数従属って?
複合主キーを持つカラムに対し、従属するカラムが存在する場合、部分関数従属という関係になります。
障害_タグテーブルを例に見てみましょう。
{ 障害ID, タグ(複合主キー)} → { タグ作成者 }
- タグ作成者は複合主キーが決まれば決定できる
- タグ作成者はタグに関数従属している
ことから、タグ作成者はタグとの部分関数従属になります。
第2正規形にしてみる
第2正規形にするには、部分関数従属にあたるカラムを丸ごと別テーブルに切り出します。
障害_タグテーブルからタグテーブルを抜き出しました。
障害
障害ID | 障害タイトル | 担当エンジニア | 担当エンジニア_email |
---|---|---|---|
D100 | ログインできない | 初心者エンジニア | beginner@dummy.com |
D100 | ログインできない | 初心者エンジニア | beginner@dummy.com |
D200 | マウスが動かない | 玄人エンジニア | master@dummy.com |
障害_タグ
障害ID | タグ |
---|---|
D100 | LOGIN |
D100 | PC |
D200 | HARD |
タグ
タグ | タグ作成者 |
---|---|
LOGIN | ケニー |
PC | マー君 |
HARD | ジョン |
第3正規形をつくろう
第3正規形は、推移的関数従属を解消した状態です。
推移的関数従属って?
推移的関数従属は2段階の関数従属がある関係のことです。
障害テーブルを見ると、「担当エンジニア_email」が2段階の関数従属であることがわかります。
# 担当エンジニアは障害IDが決まれば決定できる
{ 障害ID(主キー)} → { 担当エンジニア }
# 担当エンジニア_emailは担当エンジニアが決めれば決定できる
{ 障害ID(主キー)} → { 担当エンジニア } → { 担当エンジニア_email }
第3正規形にしてみる
第3正規化にするには、推移的関数従属にあたるカラムを丸ごと別テーブルに切り出します。
障害
障害ID | 障害タイトル | 担当エンジニア |
---|---|---|
D100 | ログインできない | 初心者エンジニア |
D200 | マウスが動かない | 玄人エンジニア |
障害_タグ
障害ID | タグ |
---|---|
D100 | LOGIN |
D100 | PC |
D200 | HARD |
タグ
タグ | タグ作成者 |
---|---|
LOGIN | ケニー |
PC | マー君 |
HARD | ジョン |
担当エンジニア
担当エンジニア | 担当エンジニア_email |
---|---|
初心者エンジニア | beginner@dummy.com |
玄人エンジニア | master@dummy.com |
これで、第3正規形になりました。
非正規形に比べて、1つ1つのテーブルがかなり見やすくなったと思います。
おまけ(第4正規化をやってみる)
よく正規化の話題で、「第3正規形までで止めるのが普通だよ〜」という声を聞きます。
しかし私の経験上、第4正規形まで正規化しているテーブルは少なくありません。
つまり、個人的には「第4正規形までで止めるのが普通だよ〜」ですね。
第4正規化とは
多値従属性を解消した状態です。
多値従属性って?
1つのキーに対し、集合の関係を持つ場合、多値従属性といいます。
従属性がキーと非キーの間ではなく、キーと集合の間でできている関係です。
障害お問合せサービスを例にすると、障害に対応するエンジニアを複数人必要な場合、第3正規形では対応することができません。
# 障害キーに対して、担当エンジニアの集合の関係ができている
{ とある障害1 } → { 担当エンジニアA } 、{ 担当エンジニアB }
{ とある障害2 } → { 担当エンジニアA }
{ とある障害3 } → { 担当エンジニアC } 、{ 担当エンジニアB }
第4正規化にしてみる
集合を解消するため、障害_担当エンジニアテーブルを新たに作ります。
障害
障害ID | 障害タイトル |
---|---|
D100 | ログインできない |
D200 | マウスが動かない |
担当エンジニア
担当エンジニア | 担当エンジニア_email |
---|---|
初心者エンジニア | beginner@dummy.com |
玄人エンジニア | master@dummy.com |
障害_担当エンジニア
障害ID | 担当エンジニア |
---|---|
D100 | 初心者エンジニア |
D100 | 玄人エンジニア |
D200 | 玄人エンジニア |
まとめ
正規化の方法を障害お問い合わせシステムを例に解説しました。
とにかく繰り返し正規化をすることで、だんだん身についていくのでたくさん手を動かしていきましょう!
参考文献