SQLアンチパターン今更ながら読みました。
もっと早く読んでおくべきだった。ということでいくつかの記事に分けてアウトプットしていきたいと思います。
主に下記記事などを参考にしました。
SQLアンチパターン勉強会 - Qiita
「SQLアンチパターン」を避けるためのチェックリスト①（DB論理設計編） - log4ketancho
チェックリスト
- 一つのカラムにカンマ区切りで値を入れていない？
- 階層があるデータ構造を扱う場合に、直接の親子関係だけで定義していない？
- 主キー名を
idという名前にしていない？ - 必要なテーブルに外部キー設定してる？
- 複数の異なるテーブルを紐づける場合は、
_type(ポリモーフィック関連)で管理していないか？ - テーブルや列を増やす前提の設計になっていないか？
一つのカラムにカンマ区切りで値を入れていない？
あるテーブルに comment_ids みたいなカラムがあることを想定します。
そして1,2,3 のように string でカンマ区切りの値が入る場合が今回のケースになります。
解決策
中間(交差)テーブルを使いましょう。
階層があるデータ構造を扱う場合に、直接の親子関係だけで定義していない？
よくあるパターン
隣接リスト
階層構造が1つや2つの場合は、非常にシンプルでSQLも面倒にはなりません。
解決策
閉包テーブル
下記の記事を参考にしました！
閉包テーブル（Closure Table）を試してみた - enomotodev’s blog
閉包テーブル - Qiita
閉包テーブル (Closure Table) に順序を持たせソートした結果を取得する - Qiita
-
ancestor(先祖)とdescendant(子孫)をもつテーブルを別で作成 - 上記2つは参照元の
idを指すように設計
具体的には、下記の通りです。
| ancestor | descendant |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 3 | 3 |
主キー名を id という名前にしていない？
id という名前にしているテーブルをよく見ますが、この id に明確な意味はありません。
account_id や user_id などのように明確にしましょう。
必要なテーブルに外部キー設定してる？
下記記事のコメントでも書かれている通り、なんでもかんでも外部キーを貼ることこそがアンチパターンなのでしょう。
デメリットのところが非常にわかりやすかったので、そのまま引用させていただきます
外部キーを貼るデメリット
- シャーディングや別DBで利用できない
- ON UPDATE CASCADE 制約を詳しく無い人がさわって想定外のレコードが変更される可能性がある
- 上記、SELECT 文走らせまくるとか、ER図が無いと辛い
- そもそも DELETE が推奨されておらず論理削除がメインなので問題がおきづらい
- テストデータの作成にコストがかかる
- フレームワークで外部機キー制約っぽいものを制約しているものがある
- 行を挿入する前に、親の行の存在確認をしないといけない。=> コード量が増える
- 制約があっても確認するはず
- insert の失敗時に制約エラーの内容からハンドリングするほうがコード量増えそう
第四章 キーレスエントリ（外部キー嫌い） - Qiita コメント箇所より引用
外部キーの概要と制約を使うことのメリット・デメリット - Qiita
外部キーの制約について
MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita
外部キー制約について - Qiita
CASCADE
CASCADEを指定すると，被参照表の主キーに変更があった場合，外部キーも同じように変更されます。
参照制約の定義
ON DELETE SET NULL・・・参照先deleteすると参照元がnullにする
ON UPDATE CASCADE・・・
下記記事でも検証してくれていますが、ON UPDATE CASCADE を指定すると、参照先をupdateした際、参照元も同じupdateがされます。
MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita
複数の異なるテーブルを紐づける場合は、_type (ポリモーフィック関連)で管理していないか？
第6章のポリモーフィック関連の箇所です。
ポリモーフィック関連の問題点
SQL を実行するときに、id だけを取得するだけでなく、type の値も同時に確認する必要が出てきます。
なので、typeが2つ、3つ存在するのであれば、そのうち1つしか外部結合ができなくなってしまいます。
解決策
- 中間テーブルの作成
- 共通の親テーブルの作成
SQLアンチパターンを読んで （ポリモーフィック関連について） | MotiMoti++
Railsのポリモーフィック関連とはなんなのか - Qiita
MySQL
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17.2 外部キー制約の使用
テーブルや列を増やす前提の設計になっていないか？
8章のメタデータトリブルの箇所です。
8章 Metadata Tribble(メタデータ大増殖) - Qiita
年ごとに Bugs_2018 Bugs_2019 Bugs_2020 と増やしていく設計を考えている場合は、水平パーティショニング、垂直パーティショニングを検討しましょう！！
データパーティショニングで巨大DBも楽々管理