習うより慣れろ！正規化実践〜障害お問い合わせシステムを例に〜

この記事の内容

• 障害お問い合わせシステムを題材として、正規化の流れを解説
• 第1~第3正規形までの正規化を実践

補足

オライリージャパン社のSQLアンチパターン本を参考にしています。

正規化とは？

データベース設計において、冗長性や非一貫性を極力ださないようにする方法論が正規化です。

正規化とはなんぞやを話すと長くなるので、詳しくは別の記事で解説しています。

早速やってみよう！

実際にやって覚えた方が早いので、障害お問い合わせシステムを例に正規化してみます。
正規化をする際は、要件定義で決めた内容をもとに正規化することが重要です。
これは、要件によってテーブル構造が変わるためです。「テーブル同士は１対多の関係なのか？」、「どれが主キーなのか？」など、正規化は常に要件と照らし合わせて行います。

例として、 障害お問い合わせシステムの簡単な条件を以下に書きます。

【障害お問い合わせシステムの要件】

• あるアプリケーションの障害内容を登録できる
• 障害に対しタグを追加でき、誰がタグを追加したかわかる
• 障害対応するエンジニアが誰かわかる
• エンジニアの連絡先がわかる

非正規形

下のテーブルは、すべての要素をとりあえず1つにまとめただけの状態になります。
ここから正規化していきます。

非正規形

障害ID	障害タイトル	タグ	タグ作成者	担当エンジニア	担当エンジニア_email
D100	ログインできない	LOGIN	ケニー	初心者エンジニア	[e-mail]
D100	ログインできない	PC	マー君	初心者エンジニア	[e-mail]
D200	マウスが動かない	HARD	ジョン	玄人エンジニア	[e-mail]

※実は上のテーブルはすでに第1正規形なのですが、不適切な状態のため、便宜上、非正規形と呼ばせていただきます。

第1正規形をつくろう

第1正規形とは「１つのセル（フィールド）に1つの値しか含まない状態」です。

非正規形をみると、タグ以外の値が重複している行があります。この状態では、主キーを決めることができません。例えば、「障害ID」「タグ」を複合主キーと考えても、「タグ」をつけない障害があるかもしれません。その場合、NULLの主キーが発生してしまいます。しかし、主キーはNULLを含めることができません。

そこで、タグの役割を持つカラムを分離してあげます。

第1正規形

障害

障害ID	障害タイトル	担当エンジニア	担当エンジニア_email
D100	ログインできない	初心者エンジニア	[e-mail]
D100	ログインできない	初心者エンジニア	[e-mail]
D200	マウスが動かない	玄人エンジニア	[e-mail]

障害_タグ

障害ID	タグ	タグ作成者
D100	LOGIN	ケニー
D100	PC	マー君
D200	HARD	ジョン

1つの障害に複数のタグをつけられるため、「障害情報」と「タグ情報」は１対多の関係です。
また、障害_タグテーブルは、「障害ID」と「タグ」が複合主キーとなります。

ポイント

主キーにNULLを含むことはできない

第2正規形をつくろう

第2正規形は、部分関数従属を解消した状態です。

部分関数従属って？

複合主キーを持つカラムに対し、従属するカラムが存在する場合、部分関数従属という関係になります。

障害_タグテーブルを例に見てみましょう。

{ 障害ID,　タグ（複合主キー）}　　→　 { タグ作成者 }

• タグ作成者は複合主キーが決まれば決定できる
• タグ作成者はタグに関数従属している

ことから、タグ作成者はタグとの部分関数従属になります。

第2正規形にしてみる

第2正規形にするには、部分関数従属にあたるカラムを丸ごと別テーブルに切り出します。
障害_タグテーブルからタグテーブルを抜き出しました。

障害

障害ID	障害タイトル	担当エンジニア	担当エンジニア_email
D100	ログインできない	初心者エンジニア	[e-mail]
D100	ログインできない	初心者エンジニア	[e-mail]
D200	マウスが動かない	玄人エンジニア	[e-mail]

障害_タグ

障害ID	タグ
D100	LOGIN
D100	PC
D200	HARD

タグ

タグ	タグ作成者
LOGIN	ケニー
PC	マー君
HARD	ジョン

第3正規形をつくろう

第3正規形は、推移的関数従属を解消した状態です。

推移的関数従属って？

推移的関数従属は２段階の関数従属がある関係のことです。
障害テーブルを見ると、「担当エンジニア_email」が２段階の関数従属であることがわかります。

# 担当エンジニアは障害IDが決まれば決定できる
{ 障害ID（主キー）}　→ { 担当エンジニア }　

# 担当エンジニア_emailは担当エンジニアが決めれば決定できる
{ 障害ID（主キー）}　→ { 担当エンジニア } 　→ { 担当エンジニア_email 　}

第３正規形にしてみる

第3正規化にするには、推移的関数従属にあたるカラムを丸ごと別テーブルに切り出します。

障害

障害ID	障害タイトル	担当エンジニア
D100	ログインできない	初心者エンジニア
D200	マウスが動かない	玄人エンジニア

障害_タグ

障害ID	タグ
D100	LOGIN
D100	PC
D200	HARD

タグ

タグ	タグ作成者
LOGIN	ケニー
PC	マー君
HARD	ジョン

担当エンジニア

担当エンジニア	担当エンジニア_email
初心者エンジニア	[e-mail]
玄人エンジニア	[e-mail]

これで、第３正規形になりました。
非正規形に比べて、１つ1つのテーブルがかなり見やすくなったと思います。

おまけ(第4正規化をやってみる)

よく正規化の話題で、「第３正規形までで止めるのが普通だよ〜」という声を聞きます。
しかし私の経験上、第４正規形まで正規化しているテーブルは少なくありません。

つまり、個人的には「第４正規形までで止めるのが普通だよ〜」ですね。

第4正規化とは

多値従属性を解消した状態です。

多値従属性って？

１つのキーに対し、集合の関係を持つ場合、多値従属性といいます。
従属性がキーと非キーの間ではなく、キーと集合の間でできている関係です。

障害お問合せサービスを例にすると、障害に対応するエンジニアを複数人必要な場合、第3正規形では対応することができません。

# 障害キーに対して、担当エンジニアの集合の関係ができている
{ とある障害１　}　→ {　担当エンジニアA }　、{　担当エンジニアB }　

{ とある障害２　}　→ {　担当エンジニアA }　

{ とある障害３　}　→ {　担当エンジニアC }　、{　担当エンジニアB }　

第4正規化にしてみる

集合を解消するため、障害_担当エンジニアテーブルを新たに作ります。

障害

障害ID	障害タイトル
D100	ログインできない
D200	マウスが動かない

担当エンジニア

担当エンジニア	担当エンジニア_email
初心者エンジニア	[e-mail]
玄人エンジニア	[e-mail]

障害_担当エンジニア

障害ID	担当エンジニア
D100	初心者エンジニア
D100	玄人エンジニア
D200	玄人エンジニア

まとめ

正規化の方法を障害お問い合わせシステムを例に解説しました。
とにかく繰り返し正規化をすることで、だんだん身についていくのでたくさん手を動かしていきましょう！

参考文献