はじめに
SQL アンチパターンの備忘録です。ほぼ自分用。
SQL アンチパターン
1. 複数の値を持つ属性を格納する
目的：複数アカウントのIDをテーブルに格納する必要がある。
アンチパターン：カンマ区切りフォーマットのリストを格納する。
→検索、集約クエリの作成、更新、妥当性の検証において問題が発生する可能性がある。
解決策：交差テーブルを作成する。product_idとaccount_idを格納し、多対多の関係を持たせる。
2. 階層構造を格納し、クエリを実行する。
目的：スレッドのコメント欄のような再帰的な関連を持つツリー構造を実現する。
アンチパターン：常に親のみに依存する。
→子コメントの取得、コメントの削除において問題が発生する可能性がある。
解決策：
隣接リストに格納された階層構造をサポートするSQL拡張機能を利用する。
pathカラムを追加してパンくずリストを作成する。
閉包テーブルを作成し、親idと子idをそれぞれ格納する。
3. ID Required
目的：主キーの規約を確立する。
アンチパターン：全てのテーブルにid列を追加する。
→冗長なキー、重複行を許可してしまう、キーの意味が分からなくなるといった問題がある。
解決策：わかりやすい列名にする。規約に縛られない。自然キーと複合キーの活用。
4. キーレスエントリ（外部キー嫌い）
目的：データベースのアーキテクチャを単純化する。参照整合性を保つために外部キーを使用する。
アンチパターン：外部キー制約を使用しない。
→整合性が保てない。
解決策：外部キー制約を宣言する。
5. EAV (Entity Attribute Value)
目的：可変属性をサポートする。共通の属性を持つ二つのテーブルを定義する。
アンチパターン：汎用的な属性テーブルを使用する。
→属性の取得クエリが煩雑化する、データ整合性を保てなくなる、行を再拘置する必要がある、といった問題がある。
解決策：
一つのテーブルに全て格納する。
二つの別々のテーブルを作成する。
クラステーブル継承
半構造化データ（XMLやJSONといった形式で属性名と値を格納）
6. ポリモーフィック関連
目的：複数の親テーブルを参照する。
アンチパターン：二重目的の外部キーを使用する。
→外部キーはテーブルを一つのみ指定しなければいけない。
解決策：参照を逆にする。もしくは共通の親テーブルを作成する。
7. （複数列属性）Multi Column Attribute
目的：複数の値を持つ属性を格納する。（バグを分類するためのタグ付け機能を追加したい）
アンチパターン：複数の列を定義する。
→値の検索、追加と削除が困難になり、一意性が保証出来なくなる。
解決策：従属テーブルを作成し、属性値を複数の列ではなく、複数の行に格納する。
8. Meta Data Trible
目的：スケーラビリティを高める。（データが増加し続けるテーブルに対応できるようにデータベースの構造を設計する）
アンチパターン：テーブルや列をコピーする。
→テーブルが増殖する。データの整合性を管理する。データの同期。一意性の保証。テーブルをまたいだクエリの実行。メタデータの管理。参照整合性の管理。
解決策：パーティショニングか従属テーブルの導入を行う。
水平パーティショニング
垂直パーティショニング
従属テーブルの導入
9. Rounding Error
目的：小数値を使用し正確な計算を行う。
アンチパターン：Floatデータ型を使用する。
解決策：NumericまたはDecimalデータ型を使用する。
10. 31 Flavor
目的：列を特定の値に限定する。
アンチパターン：限定する値を列定義で指定する。
→有効なステータス値を調べるクエリを書かなくてはならない。有効値の追加の際、Check制約を追加するための構文はなく、新たな値セットで列を再定義する必要がある。また、値の一つを廃止する際に過去のデータに影響が出る可能性がある。
解決策：限定する値をデータで指定する。
11. Phantom File
目的：画像をはじめとする大容量メディアファイルを格納する。
アンチパターン：物理ファイルの使用。画像をファイルシステムに保存し、ファイルパスをVarcharとして格納する。
→ファイル削除時、トランザクションとロールバック時、バックアップ時、SQLアクセス権限使用時に問題が起こる。
解決策：必要に応じてBlob型を採用する。
12. Index Shotgun
目的：パフォーマンスを最適化する。(インデックスを効果的に使用する）
アンチパターン：闇雲にインデックスを使用する。もしくはインデックスを全く定義しない。
解決策：適切なインデックスを定義する。
13. Fear of the Unknown.
目的：かけている値を区別する。（ミドルネームを後から追加する）
アンチパターン：Nullを一般値として扱う。
→SQLはNullを0, false, から文字列とは異なる特殊な値として扱う。
解決策：Nullを一意な値として扱う。Nullの検索はis nullを使用する。
14. Ambiguous Group
目的：グループ内で最大値を持つ行を取得する。（その最大値が見つかった行の他の属性も取得するクエリを実行する）
アンチパターン：SELECT product_id, MAX(date_reported), bug_id
→SQLがクエリの意図を汲んでくれるとは限らない。
解決策：相関サブクエリを実行する。導出テーブルを使用する。
15. ランダムセクション
目的：サンプル行をチェックする。
アンチパターン：データをランダムにソートする。
→大量のデータを処理することになるためデータ量が大きくなるとパフォーマンスに問題が発生する。
解決策：１と最大値の間のランダムなキー値を選択する。全てのキー値のリストを受け取り、ランダムに１つを選択する。
16. プアマンズ・サーチエンジン
目的：全文検索を行う。
アンチパターン：パターンマッチ述語を使用する。
解決策：適切なツールを使用する。
MySQLのフルテキストインデックス
Oracleのテキストインデックス
PostgreSQLでのテキスト検索
サードパーティーのサーチエンジン
17. スパゲッティクエリ
目的；SQLクエリの数を減らす
アンチパターン：複雑な問題をワンステップで解決しようとする。
解決策：分割する。Unionを用いる。
18. インプリシットカラム
目的：タイプ数を減らす。
アンチパターン：ワイルドカードの使用。
→リファクタリングにおける問題。パフォーマンスの問題。
解決策：列名を明示的に指定する。
19. リーダブルパスワード
目的：パスワードのリカバリーとリセットを行う。
アンチパターン：パスワードを平文で格納する。
解決策：ソルトをつけてパスワードハッシュを格納する。
20. SQLインジェクション
目的：動的SQLを記述する。
アンチパターン：未検証の入力をコードとして実行する。
解決策：入力のフィルタリング、動的値のパラメーター化、動的値を引用付で囲む、ユーザーの入力をコードから隔離する。
21. シュードキーニートフレーク
目的：欠番を埋める。
アンチパターン：欠番を割り当てる。既存行に番号を振り直す。
→データが不一致となる。
解決策：疑似キーの欠番は埋めない。
22. See No Evil
目的：簡潔なコードを書く
アンチパターン：診断せずに判断する。
解決策：リズムを維持する。
23. Diplomatic Immunity
目的：ベストプラクティスを採用する。
アンチパターン：SQLを特別扱いし、ベストプラクティを受け入れない。
解決策：包括的に品質問題に取り組む。具体的には文書化、バージョン管理、テスティング、複数のブランチを扱うなど。
24. マジックビーンズ
目的：MVCのMを単純化する。
アンチパターン：モデルがアクティブレコードそのもの。
解決策：モデルがアクティブレコードを持つようにする。（データベースの操作を分離する）
25. 砂の城
目的：サービスの安定稼働。
アンチパターン：想定不足。
解決策：ベンチマーク、テスト環境の構築、バックアップ、高可用性。