データベースの正規化をシェアハウスを題材にして学ぶ

# はじめに
みなさん、データベースをさわることって多いですか？
僕は全くです。

僕の普段の業務ではネイティブアプリの開発が主で、データベースとふれ合うことは滅多にありません。。
それでもこの記事を書こうと思ったのには理由があります。

僕は新卒2年目なのですが、弊社では新卒2年目向けの研修があります。
そこでは普段触れることが少ない技術や苦手な技術を担当してチームでサービス開発をします。

その研修を通して、**データベース**についてなかなか学べなかったなという反省がありました。
特に**正規化**~~（大学で学んだはずなのに抜けている...）~~について学ぶことを目標にしていましたが、他の作業で手をつけられずに不完全燃焼だったので何かしらの形でアウトプットして知見を深めようと心に決めました。

これはその行動の記録です。
ちなみに題材を「**シェアハウス**」にしたのは僕がたまたまシェアハウスに住んでいたのでイメージしやすいかなと思った、ただそれだけです。

# 正規化とは
`リレーショナルデータベース(RDB)において、表の関連性を失わないように項目を整理して表を分離する`とのこと。
正規化することで、データの更新や削除が容易に行えるようになります。

ほうほう。
じゃあ、実際にテーブルを正規化していってみよう

# この記事で扱うデータ
どこぞのシェアハウス運営会社がハウス・住民・ハウスを管理するマネージャーの情報を集約したデータベースを作ってシステムを構築したい。と仮定してテーブルの設計をします。

※住民やハウス等の情報は適当なものです。

# 正規形の種類
正規形には
- **非正規形**
- **第1正規形**
- **第2正規形**
- **第3正規形**
- ボイスコッド正規形
- 第4正規形
- 第5正規形
があるそうです。

本記事では、一般的と言われている太字になっている正規形について見ていきます。

## 非正規形
早速作ってみました。
<img width=100% alt=hiseikikei.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/c38025df-49c3-4ccf-5c0d-2600dc050000.png>
非正規形はエンジニアじゃなくても一般的にExcelとかでよく見かけるような形です。
重複する場所は結合されていてまとまって見やすいですね。

ただし非正規形はあくまで人の目で見てわかりやすい形になっているだけで、一行ずつのデータの操作を行うRDBでは扱うことができません。
そこで、正規化してRDBでも扱えるような形にしてあげます。

## 第1正規形
第1正規形では、繰り返し部分を除外することで「単一の値」としてRDBでも扱えるようにします。
要するに、下記の表のように結合されていた箇所を一行一行ちゃんと登録されてる状態にしましょうってやつですね。
<img width=100% alt=1seikikei.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/8d73e4ec-3ba8-5f0b-899e-caf1d1b05353.png>

これでDBの操作ができるようになりました。

ただ、これだけでは少し問題があります。

例えば、たろうさんが**ハウスB**に引っ越したとします。
そうすると、ハウスIDは**2（表の赤字）**になります。
それにつれて、「ハウス」や「所在地」、「マネージャー」等の情報も変更してあげなければいけませんね（**表の太字部分**）。
<img width=90% alt=1seikikeiproblem.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/a30710ee-ff0a-1da4-9971-22a008565dc9.png>

また、ハウスCのオーナーさんが「うちのコンセプトを新しくしたい！新しいコンセプトは**世界のゲーム**にする！」って言ったとします。
この場合、ハウスCのコンセプトの列を修正しないといけません。
作った表には3つのデータしかありませんが、このハウスが**100人規模のシェアハウスだったら100箇所のデータを更新してあげなければいけません**。
めんどくさいですよね...
<img width=90% alt=1seikikeiproblem2.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/a2d274b1-f84b-93b6-bf18-8b5ef26fc632.png>

じゃあ正規化してこのめんどくささを解消しましょう。

## 第2正規形
第1から第2正規形にするには、まず「**関数従属**」について知っておかなければなりません。
関数従属というのは`xが決まればyがわかる`といいったものです。

そして第２正規形にする条件は、`部分関数従属を排除されている`というものです。
はい？？

わかりにくいので、具体例で見てみましょう。
sharehousesテーブルのうち、ハウスIDがわかれば、ハウスよりも右の列の情報がわかりますが住民名や性別まではわかりません。
これが`ハウスよりも右の列の情報が部分的に関数従属している`ということなんですね。
これを排除してあげるのが、第2正規形にするということです。
<img width=90% alt=2seikikei.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/474c7c7e-26db-dfa2-3957-db3cb6d9e833.png>
やってみたのがこちら。
`residents(住民)テーブル`と`houses(ハウス)テーブル`に分割しました。
また、`residents(住民)テーブル`では`ハウスID`という列を追加してハウスの情報を参照できるようにしました。
<img width=120% alt=2seikikei2.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/3dc92125-f483-f62e-f094-e7700c9a9291.png>
これによって、冗長性がなくなりました。
さっきの第１正規形の例であった「ハウスCのコンセプトを**世界のゲーム**にしたい！」という話が上がっても、`housesテーブル`の該当箇所を1箇所直すだけでよくなり、`residentsテーブル`では`ハウスID`を参照しておけば良いという状態になります。

ハウスを引っ越す場合も`residentsテーブル`の`ハウスID`を更新すればそれで終わりです。

とても変化に柔軟になりました！

## 第3正規形
第２正規形で部分関数従属を排除できてスッキリできました。
しかし、まだ第２正規形のテーブルの中にはまだ冗長なものがあります。

第３正規形にするには`推移的関数従属を排除する`ことを行います。
推移的関数従属とは`xが決まればyが決まり、yが決まればzが決まる`というものです。

これを第２正規形のhousesテーブルで見ると、以下のようなイメージです。
`ID`がわかれば`マネージャーID`がわかり、`マネージャーID`がわかれば`マネージャー`がわかるという関係ですね。
<img width=60% alt=3seikikei2.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/d0d1dd8b-328d-8afc-8221-643e8f79fe05.png>

これを排除してあげれば良いので、`housesテーブル`からマネージャーの情報を分割させます。
できた表は以下になりました。
<img width=100% alt=3seikikei.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/0ec4ead5-3507-8f24-4db6-70b98a9fe007.png>

たまたま作成したテーブルにマネージャーの情報が少ないのでしなくても良いじゃんと思ったりするかもしれませんが、マネージャーの情報が増えてくると恩恵を感じられるようになるかと思います。

## 修正
作成したテーブルをチームの人たちにみてもらい上司から「この表の中にデータとしてあまり持たせない方が良いものがあるよ」と言われました。

さあ、どのテーブルのどの列で、どうするべきでしょうか？
<img width=100% alt=3seikikei.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/0ec4ead5-3507-8f24-4db6-70b98a9fe007.png>

正解は以下、**年齢を生年月日にする**でした。
<img width=60% alt=スクリーンショット 2020-12-08 1.38.12.png src=https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/b0508656-a80b-1ee3-b6e4-c1aa35ec677c.png>
なぜ年齢にしない方が良いかというと、データベースが1年に1回誕生日を迎えた人の年齢の値を都度都度更新しなければならず、**コストがかかる**からです。
データベースの操作ができるとはいえ、なるべくデータを操作しない設計にするのが望ましいのですね。

また、このテーブルについて議論しているうちに自分でもこの列はおかしいと思うものを見つけました。
それは、**家賃**です。
確かに`residents`が支払うものであるからここに持たせるのが自然かと思っていましたが、家賃というのはハウスや部屋ごとに決まるもののため、住民テーブルに持たせるのは不適切かもしれないと考えました。

そこで`住民テーブル`から`家賃`を切り離して`家賃テーブル(rents)`を持たせる設計にしてみました。
![スクリーンショット 2020-12-08 1.45.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/a854a0ed-1be9-3a1c-4e2b-634e51d0b25c.png)
これで家賃と住民、ハウスの関連性を持たせることができてスッキリしたかなと思います。


# SQLの影響
作成した各テーブルに対して、SQLを作成/実行したときにどのように変化もあるのかを考えてみました。

今回は、`マネージャーがまさしさんであるハウスの情報と住んでいる人の名前を知りたい`といったケースでのSQLについて考えてみました。

## SQL文
作成したSQL文は以下になります。※あくまで一例です。

**第1正規形**

```sql
SELECT id, housename, address, concept, residentname FROM sharehouse WHERE managername = 'まさし';
```

**第2正規形**

```sql
SELECT h.id, h.housename, h.address, h.concept, r.residentname FROM houses h
    LEFT JOIN residents r ON h.id = r.house_id 
    WHERE managername = 'まさし';
```

**第3正規形**

```sql
SELECT id, housename, address, concept, residentname FROM 
    (SELECT h.id, h.housename, h.address, h.concept, r.residentname, m.managername FROM houses h
        LEFT JOIN managers m ON h.manager_id = m.id
        LEFT JOIN residents r ON h.id = r.house_id) sharehouses
    WHERE managername = 'まさし';
```

正規化してテーブルが増えていくにつれて参照しなければいけないテーブルも増えるので、SQLが複雑になっていきます。

## パフォーマンス
ローカル環境にそれぞれの正規形のデータベースとテーブルを作成し、約12万件のサンプルデータを用意して実行速度を確認してみました。

|正規形|速度|
|---|---|---|
|第1正規形|0.09 sec|
|第2正規形|0.38 sec|
|第3正規形|0.10 sec|

数百件とかではまだ違いはみられませんでしたが、膨大なデータを扱うようになるとパフォーマンスに影響が現れました。

予想ではテーブルの数が多く、その分テーブル間の参照回数が多そうな第3正規形が一番遅くなるのかと思いきや第２正規形が最も遅くなる結果となりました。

ちなみに、実行計画は以下のようになりました。
**第1正規形**
![queryplan.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/0b9e3515-7765-33dc-b5bb-fa5fb41eb018.png)
**第2正規形**
![queryplan2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/43c2f579-0e07-8394-a5e6-08c883f77265.png)
**第3正規形**
![queryplan3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/550560/6f9e2796-ebbd-4768-1908-2c3a38efab19.png)

### 疑問点
さすがにSQLを見るに、第２正規形のものよりも複雑なSQLになっているのにここまで早くなるのはおかしいと思い、第３正規形のサブクエリ部分のみを実行して実行時間を確認してみました。

```sql
SELECT h.id, h.housename, h.address, h.concept, r.residentname, m.managername FROM houses h
        LEFT JOIN managers m ON h.manager_id = m.id
        LEFT JOIN residents r ON h.id = r.house_id
```
結果は**0.40sec**でした。
と、いうことは0.4sec + 0.10secの**0.50sec**くらい処理に時間がかかっている？

SQLの実行時に表示される時間が実行時間のどの部分を表しているのかまで今回突き止めることができなかったので、ここについて詳しい方がいたらご教授願いたいです(;_;)

# おわりに
正規化することで、テーブルの冗長性がなくなり管理しやすくなる一方でSQLのパフォーマンスにも影響が出るので、どの設計が適切かを考えるのが重要だということがわかりました。

SQLのパフォーマンス向上にはテーブル設計を見直す以外にも下記等の方法があるようです。

- そもそものSQLを見直す
- INDEXを作成する
- キャッシュ機構を用意する

この辺りの知識も別の機会にインプットしていけたらなと思います。

<br>
今回は自分なりにテーブルを作り、職場の先輩方のレビューをもとにテーブルの設計やパフォーマンスについてを考えられて、正規形についての理解が深まったと思います。

もっとこういう設計にした方が良いとかアドバイス等いただければ幸いです。

# 参照
1. [正規化とは - コトバンク](https://kotobank.jp/word/%E6%AD%A3%E8%A6%8F%E5%8C%96-5092)
2. [DBを正規化するということ](https://qiita.com/yukihirasawa1206/items/c2dedcc51c01c1bd8ccc)
3. [【関係データベースの正規化】第1正規形・第2正規形・第3正規形について紹介](https://breezegroup.co.jp/202005/database-normalization/)
4. [データベースの基礎【正規化(非正規形を第３正規形にする)】](https://poppingcarp.com/database_normalization/)