ジェイウォーク(信号無視)
あなたはバグ管理アプリケーションの新規機能を開発しています、
製品の連絡窓口として、ユーザーを1人指定する機能です。
ところが案の定、後になって1つの製品に複数のユーザーを連絡先として登録できるようにして欲しいという要望が出てきました。
###アンチパターン:カンマ区切りフォーマットのリストを格納する
| product_id | product_name | accouont_id |
|---|---|---|
| SERIAL PRIMARY KEY | VARCHAR(1000) | VARCHAR(100)--カンマ区切りのリスト |
account_id列をVARCHAR型の列として再定義し、複数アカウントのIDをカンマ区切りで連結して格納する
###アンチパターンな理由①
特定のアカウントに関連する製品の検索
全ての外部キーが文字連結されて1つのフィールドに格納されていると、クエリを作成することが難しい。
等価性による比較ができなくなるため、何らかの文字列パターンに対するパターンマッチが必要になる。
パターンマッチが良くない理由
・式の書き方によっては意図しない一致結果が返される可能性がある
・インデックスを使うメリットが得られない
・パターンマッチ構文は各種データベース製品において異なるため、SQLはベンダー中立のものにならない
###アンチパターンな理由②
特定の製品に関するアカウントの検索
同じく、カンマ区切りのリストの値を使って参照先のテーブルと結合(JOIN)するのは手間がかかり効率的でない。
SELECT * FROM Products AS p INNER JOIN Accounts AS a ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]' WHERE p.product_id = 123
このような式で2つのテーブルを結合すると、インデックスを使う機会が失われる。
このクエリは、両方のテーブルを全てスキャンして、クロス積を生成し、全ての行の組み合わせについて正規表現を評価している。
###アンチパターンな理由③
特定の製品に関連するアカウントの更新
文字列連結によってリストおん末尾に新しいIDを追加できるが、この方法ではリストのソート順を維持でいなくなる場合がある。
IDをリストから削除するには
・古いリストの取得
・更新したリストの保存
上記2つのSQLを発行しなければならなくなる
###アンチパターンな理由④
製品IDの妥当性検証
基本は数字のみだが、VARCHARなのでユーザーが"banana"と入力できてしまう。
また、整数値のリストではなく、文字列のリストを格納する場合、ユーザーに区切り文字(カンマなど)を入力されてしまう。
入力された場合、区切り文字の判別ができなくなる。
###アンチパターンな理由⑤
リストの長さの制限
データ型VARCHAR(30)の列に格納できるエントリの数は、各エントリの長さによって変わってしまう。
例:'10,11,40,23,54,33,32,44,21'
'33938372,38383737,83737373'