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


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

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

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

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

https://qiita.com/n_yamadamadamada/items/8f0651dd59fce6923849

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

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

【障害お問い合わせシステムの要件】
- あるアプリケーションの障害内容を登録できる
- 障害に対しタグを追加でき、誰がタグを追加したかわかる
- 障害対応するエンジニアが誰かわかる
- エンジニアの連絡先がわかる


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

**非正規形**
|  障害ID  | 障害タイトル | タグ | タグ作成者 | 担当エンジニア | 担当エンジニア_email |
| --- | --- | --- | --- | --- | --- | 
|  D100  | ログインできない | LOGIN | ケニー | 初心者エンジニア | beginner@dummy.com |
|  D100   |  ログインできない  |  PC | マー君 | 初心者エンジニア | beginner@dummy.com | 
|  D200   |  マウスが動かない  |  HARD | ジョン | 玄人エンジニア | master@dummy.com | 

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

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

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

![](https://storage.googleapis.com/zenn-user-upload/890c5d3257fe-20231125.png)

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

### 第1正規形
**障害**
|  障害ID  | 障害タイトル | 担当エンジニア | 担当エンジニア_email |
| --- | --- | --- | --- | 
|  D100  | ログインできない | 初心者エンジニア | beginner@dummy.com |
|  D100   |  ログインできない  | 初心者エンジニア | beginner@dummy.com | 
|  D200   |  マウスが動かない  |  玄人エンジニア | master@dummy.com | 

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

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

### ポイント

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

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

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

![](https://storage.googleapis.com/zenn-user-upload/d57cb88d425b-20231125.png)

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

```
{ 障害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正規形は、推移的関数従属を解消した状態です。

### 推移的関数従属って？

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

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

# 担当エンジニア_emailは担当エンジニアが決めれば決定できる
{ 障害ID（主キー）}　→ { 担当エンジニア } 　→ { 担当エンジニア_email 　}
```
![](https://storage.googleapis.com/zenn-user-upload/8f80967559f4-20231125.png)

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

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

**障害_タグ**
|  障害ID  |  タグ | 
| --- | --- | 
|  D100   | LOGIN | 
|  D100    |  PC |
|  D200   |  HARD |

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

**担当エンジニア**
|  担当エンジニア | 担当エンジニア_email |
| --- | --- | 
|  初心者エンジニア | beginner@dummy.com |
|  玄人エンジニア | master@dummy.com | 

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

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

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

### 第4正規化とは
多値従属性を解消した状態です。

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

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

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

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

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


#### 第4正規化にしてみる

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

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

**担当エンジニア**
|  担当エンジニア | 担当エンジニア_email |
| --- | --- | 
|  初心者エンジニア | beginner@dummy.com |
|  玄人エンジニア | master@dummy.com | 

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


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

## 参考文献
https://www.shoeisha.co.jp/book/detail/9784798124704

https://www.oreilly.co.jp/books/9784873115894/