SQLアンチパターンをパッと思い出すための一覧

SQLアンチパターン

SQLアンチパターン読みました。非常に面白かったです。アンチパターンについて事例を交えてわかりやすく書かれており、プログラマとして何年か仕事したことがある人なら、「あの案件のデータベースはこのアンチパターンだった」とか「このテーブル定義書いたことある」とか、過去の色々を思い出しながら楽しく読める本だと思います。

この本の良いところは、「アンチパターンを用いるのは絶対駄目」と言うのではなくて、アンチパターンを用いてもよい場合や、アンチパターンを見つけた場合の解決策のバリエーションにも十分なページ数を割いているところです。アンチパターンに遭遇したり、アンチパターンを使うしか無いと思える場面に遭遇したら、この本を読み返して考える、という使い方をするのが良さそうです。

あと、RDBMSではなくてNoSQLを使っていて正規化できないから、「どうしても本書のアンチパターン（EAVなど）を使わざるを得ない」といった場合にも、潜在的なデメリットを理解する助けになりそうだ、と読みながら思いました。

そこで、アンチパターンの概要をパッと思い出せるように、本書に載っているアンチパターンの一覧を作りました。概要や解決策は、基本的に本書の表現を踏襲しつつ、自分の思い出しやすいように表現を変えています。また、アンチパターン名は The Pragmatic Bookshelf | SQL Antipatterns に載っている英単語に直しました（どのみち本書も英単語をカタカナにしてるだけなので）。

Logical Database Design Antipatterns （データベース論理設計のアンチパターン）

アンチパターン名	概要	解決策
Jaywalking （信号無視）	交差(intersection)テーブルの作成を避けるために、カンマ区切りのリストを使う	交差テーブルを作成する
Naive Tree （素朴な木）	テーブルにparent_idカラムを追加して、素朴な隣接リスト(Adjacency List)にする	経路列挙モデル(Path Enumeration)、閉包テーブルモデル(Closure Table)などの代替ツリーモデルを使用する
ID Required （とりあえずID）	すべてのテーブルに「id」列を用いる	idではなく、bug_idのような分かりやすいカラム名を付ける、自然キーと複合キーを活用する（不要な擬似キーを作らない）
Keyless Entry （外部キー嫌い）	外部キー制約を使用しない	外部キー制約を宣言する
Entity-Attribute-Value (EAV)	サブタイプの属性の種類を可変にするために、汎用的な属性テーブルを用意する。そして属性名をカラム名にする代わりに、attr_nameカラムの値として格納する	サブタイプのモデリングを行い、EAVを用いない
Polymorphic Associations （ポリモーフィック関連）	親テーブルの種別およびIDを格納するカラム(xxxx_type, xxxx_id)を用意し、xxxx_typeの値によって参照する親テーブルを切り替える	親子の参照を逆にする、交差テーブルを作成する、または共通の親テーブル（クラステーブル継承の基底テーブル）を作成する
Multi-Column Attribute （複数列属性）	属性の数だけカラムを事前に定義する（tag1, tag2, tag3, ...）	従属テーブルを作成する
Metadata Tribbles （メタデータ大増殖）	増殖する性質を持つテーブルやカラムを定義する。例えば年ごとにバグのテーブルを分ける(Bugs_2008, Bugs_2009, ...)	パーティショニングと正規化を行う（水平パーティショニング、垂直パーティショニング、従属テーブルの導入など）

Physical Database Design Antipatterns （データベース物理設計のアンチパターン）

アンチパターン名	概要	解決策
Rounding Errors （丸め誤差）	FLOATデータ型を使用する	NUMERICデータ型を使用する
31 Flavors （31のフレーバー）	カラムに格納することを許可する値を、列定義で指定する	許可する値を、テーブルに格納したデータで指定する（例：Bugテーブルのstatusカラムに格納可能な値を、BugStatusテーブルのstatusカラムで指定する）
Phantom Files （幻のファイル）	ファイルをファイルシステムに格納し、そのファイルのファイルパスをデータベースに格納する	必要に応じてBLOB型を採用する
Index Shotgun （闇雲インデックス）	インデックスが多すぎる／少なすぎる、あるいはインデックスを活用しないクエリを実行する	「MENTOR」の原則に基づいて効果的なインデックス管理を行う（Measure, Explain, Nominate, Test, Optimize, Rebuild）

Query Antipatterns （クエリのアンチパターン）

アンチパターン名	概要	解決策
Fear of the Unknown （恐怖のunknown）	NULLを一般値として使う、または一般値をNULLとして使う（例：-1を「未指定」の意味で使う）	NULLを一意な値として使う
Ambiguous Groups （曖昧なグループ）	GROUP BYの使用時に、グループ化されていない列を参照する	曖昧でない列を使用する（相関サブクエリを使用、導出クエリを使用、JOINを使用、他の列にも集約関数を使用、など）
Random Selection （ランダムセレクション）	ランダムにソートを行い、最初の行を取得する。	特定の順番に依存しない（OFFSET句を用いてランダムに行を選択する、など）
Poor Man’s Search Engine （貧者のサーチエンジン）	パターンマッチ述語（LIKE、REGEXP）を使用する	適切なツールを使用する（専用の全文検索エンジンや、データベース製品独自の全文検索機能、自作の転置インデックスなど）
Spaghetti Query （スパゲッティクエリ）	複雑な問題をワンステップで解決しようとする	クエリを分割する、サブクエリの列に互換性があるならUNIONを用いる、CASE式とSUM関数を組み合わせる
Implicit Columns （暗黙の列）	SELECT文でワイルドカードを使う、あるいはINSERT文で列名の入力を省略する	必要な列の列名だけを明示的に指定する

Application Development Antipatterns （アプリケーション開発のアンチパターン）

アンチパターン名	概要	解決策
Readable Passwords （読み取り可能パスワード）	パスワードを平文で格納する	ソルトを付けてパスワードハッシュを格納する
SQL Injection （SQLインジェクション）	未検証の入力をコードとして実行する	誰も信用してはならない（入力をフィルタリングする、prepared statementを使う、コードレビューしてもらう、など）
Pseudokey Neat-Freak （擬似キー潔癖症）	欠番となった擬似キーを再利用する、あるいは擬似キーの値を再割当てすることで、連番になっている擬似キーの隙間を埋める	擬似キーの欠番は埋めない、擬似キーを行番号と混同しない
See No Evil （臭いものに蓋）	肝心な部分を見逃す（データベースAPIの戻り値を無視する、アプリケーションコード内に点在するSQLしか読まない）	エラーから優雅に回復する（戻り値と例外のチェックをする、構築後のSQLをログに出力する）
Diplomatic Immunity （外交特権）	SQLを特別扱いする（「アプリケーション開発のルールは、データベース開発には当てはまらない」と考える）	包括的に品質問題に取り組む（文書化、バージョン管理、テスティング）
Magic Beans （魔法の豆）	モデルがアクティブレコードそのもの	モデルとDAOの関係をis-aではなくhas-aにする、DAOを外部に公開しない、ドメインモデルを使用する
砂の城*1	想定不足（性能問題や障害が起きた時の対処についてのポリシーの策定漏れなど）	どのようなトラブルが起こりうるかということを可能な限り想定しておく

*1:奥野 幹也さんによる寄稿のため、英語名なし