SQLアンチパターン：ジェイウォーク（信号無視）

SQLアンチパターンを読み始めたので、1つ１つ書いてのメモです

目的

• 複数の値を持つ属性を格納する
  • 1つのカラムに、「,」区切りなどで複数の値をいれる
  • やった記憶がある

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR(1000),
  account_id VARCHAR(100)
);

INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', '12,34’);

CREATE TABLE Accounts (
  account_id SERIAL PRIMARY KEY,
  account_name VARCHAR(1000)
);
INSERT INTO Accounts (account_id, account_name) VALUES (12, 'Visual C++');
INSERT INTO Accounts (account_id, account_name) VALUES (34, 'Visual Java');

SELECT * FROM Products AS p INNER JOIN Accounts AS a ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]' WHERE p.product_id = 1;

アンチパターン

• 等価による比較ができない
• パターンマッチで見つける
  • SQLもパターンマッチが使えたのか...
• インデックスのメリットなし
• join も手間がかかる
• 集約関数（sum、count）とかが使えない
• カラム内のソートの問題
• 追加する情報の妥当性
• 区切り文字の問題（「,」が使われる値の入力）
• リストの長さの制限

解決策

• 交差テーブルを作成する
• joinが使えて、インデックスも使える

感想

• 以前は見たことがあったけれど、最近見ていなかったパターン
  • ソーシャルゲームだと join が怖くて使えなかったからかな？
• Rails を使うようになってからは、中間（交差）テーブルの扱いが比較的楽になったと感じているので、使いやすくなった
• 中間テーブル？交差テーブル？については、t_wadaさんが答えてた
  • https://twitter.com/t_wada/status/413497591314120704

@osa522 呼び方がいろいろあるので悩みました。脚注に "交差テーブルは、関連テーブル、結合テーブル、多対多テーブル、マッピングテーブルなどと呼ばれることもありますが" と書きましたが、そういえば「中間テーブル」という呼び名もありましたね…… #sqlap

— Takuto Wada (@t_wada) December 19, 2013