【SQL】アンチパターンや使えそうなパターンをまとめる ~論理設計~

SQLアンチパターン今更ながら読みました。
もっと早く読んでおくべきだった。ということでいくつかの記事に分けてアウトプットしていきたいと思います。

主に下記記事などを参考にしました。
[SQLアンチパターン勉強会 - Qiita](https://qiita.com/iwata@github/items/9a47ddababd8e0a82662)
[「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編） - log4ketancho](https://www.ketancho.net/entry/2018/03/07/080000)

## チェックリスト
- 一つのカラムにカンマ区切りで値を入れていない？
- 階層があるデータ構造を扱う場合に、直接の親子関係だけで定義していない？
- 主キー名を `id` という名前にしていない？
- 必要なテーブルに外部キー設定してる？
- 複数の異なるテーブルを紐づける場合は、`_type` (ポリモーフィック関連)で管理していないか？
- テーブルや列を増やす前提の設計になっていないか？

## 一つのカラムにカンマ区切りで値を入れていない？
あるテーブルに `comment_ids` みたいなカラムがあることを想定します。
**そして`1,2,3` のように `string` でカンマ区切りの値が入る場合が今回のケースになります。**

### 解決策
中間(交差)テーブルを使いましょう。


## 階層があるデータ構造を扱う場合に、直接の親子関係だけで定義していない？

### よくあるパターン
#### 隣接リスト
階層構造が1つや2つの場合は、非常にシンプルでSQLも面倒にはなりません。

### 解決策
#### 閉包テーブル
下記の記事を参考にしました！
[閉包テーブル（Closure Table）を試してみた - enomotodev’s blog](https://enomotodev.hatenablog.com/entry/2015/11/19/235300)
[閉包テーブル - Qiita](https://qiita.com/ymstshinichiro/items/b1825719c4fb274446cc)
[閉包テーブル (Closure Table) に順序を持たせソートした結果を取得する - Qiita](https://qiita.com/nkenbou/items/6a7098f20f543e1ad800)

- `ancestor(先祖)` と `descendant(子孫)` をもつテーブルを別で作成
- 上記2つは参照元の `id` を指すように設計

具体的には、下記の通りです。

| ancestor | descendant | 
|:---|:---|
| 1 | 1 | 
| 1 | 2 | 
| 1 | 3 | 
| 2 | 2 | 
| 2 | 3 | 
| 3 | 3 | 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148392/ad1d71b2-0032-d713-aea2-05e701916490.png)


## 主キー名を `id` という名前にしていない？
`id` という名前にしているテーブルをよく見ますが、この `id` に明確な意味はありません。
`account_id` や `user_id` などのように明確にしましょう。

## 必要なテーブルに外部キー設定してる？
下記記事のコメントでも書かれている通り、**なんでもかんでも外部キーを貼ることこそがアンチパターンなのでしょう。**
デメリットのところが非常にわかりやすかったので、そのまま引用させていただきます:bow_tone1:

>外部キーを貼るデメリット
- シャーディングや別DBで利用できない
- ON UPDATE CASCADE 制約を詳しく無い人がさわって想定外のレコードが変更される可能性がある
- 上記、SELECT 文走らせまくるとか、ER図が無いと辛い
- そもそも DELETE が推奨されておらず論理削除がメインなので問題がおきづらい
- テストデータの作成にコストがかかる
- フレームワークで外部機キー制約っぽいものを制約しているものがある
- 行を挿入する前に、親の行の存在確認をしないといけない。=> コード量が増える
- 制約があっても確認するはず
- insert の失敗時に制約エラーの内容からハンドリングするほうがコード量増えそう
[第四章 キーレスエントリ（外部キー嫌い） - Qiita](https://qiita.com/kpure/items/6b5676873f901a0ecc7b) コメント箇所より引用

[外部キーの概要と制約を使うことのメリット・デメリット - Qiita](https://qiita.com/kamillle/items/5ca2db470f199c1bc3ef)


### 外部キーの制約について
[MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita](https://qiita.com/suin/items/21fe6c5a78c1505b19cb)
[外部キー制約について - Qiita](https://qiita.com/SLEAZOIDS/items/d6fb9c2d131c3fdd1387)

#### CASCADE
>CASCADEを指定すると，被参照表の主キーに変更があった場合，外部キーも同じように変更されます。
[参照制約の定義](http://itdoc.hitachi.co.jp/manuals/3020/3020645240/W4520312.HTM#ID01180)

>ON DELETE SET NULL・・・参照先deleteすると参照元がnullにする
ON UPDATE CASCADE・・・

下記記事でも検証してくれていますが、`ON UPDATE CASCADE` を指定すると、**参照先をupdateした際、参照元も同じupdateがされます。**
[MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita](https://qiita.com/suin/items/21fe6c5a78c1505b19cb)


### 複数の異なるテーブルを紐づける場合は、`_type` (ポリモーフィック関連)で管理していないか？
第6章のポリモーフィック関連の箇所です。

#### ポリモーフィック関連の問題点
SQL を実行するときに、`id` だけを取得するだけでなく、`type` の値も同時に確認する必要が出てきます。
なので、`type`が2つ、3つ存在するのであれば、そのうち1つしか外部結合ができなくなってしまいます。

#### 解決策
- 中間テーブルの作成
- 共通の親テーブルの作成

[SQLアンチパターンを読んで （ポリモーフィック関連について） \| MotiMoti++](https://blog.motimotilab.com/?p=207#:~:text=%E3%83%9D%E3%83%AA%E3%83%A2%E3%83%BC%E3%83%95%E3%82%A3%E3%83%83%E3%82%AF%E9%96%A2%E9%80%A3%E3%81%A8%E3%81%AF,%E3%81%84%E3%82%8B%E3%82%88%E3%81%86%E3%81%AA%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3%E3%81%A7%E3%81%99%E3%80%82&text=%E5%9B%B3%E3%81%AB%E3%81%82%E3%82%8B%E3%82%88%E3%81%86%E3%81%AB,%E3%82%A3%E3%83%83%E3%82%AF%E9%96%A2%E9%80%A3%E3%81%AB%E3%81%AA%E3%82%8A%E3%81%BE%E3%81%99%E3%80%82)
[Railsのポリモーフィック関連とはなんなのか - Qiita](https://qiita.com/itkrt2y/items/32ad1512fce1bf90c20b)

#### MySQL
[MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.2 外部キー制約の使用](https://dev.mysql.com/doc/refman/5.6/ja/create-table-foreign-keys.html)


### テーブルや列を増やす前提の設計になっていないか？
8章のメタデータトリブルの箇所です。
[8章 Metadata Tribble(メタデータ大増殖) - Qiita](https://qiita.com/Aiita/items/1eda0a10a6aa305379b5)


年ごとに `Bugs_2018` `Bugs_2019` `Bugs_2020` と増やしていく設計を考えている場合は、**水平パーティショニング、垂直パーティショニング**を検討しましょう！！
[データパーティショニングで巨大DBも楽々管理](https://www.atmarkit.co.jp/ait/articles/0612/21/news118.html)