【SQLアンチパターン】ジェイウォークがなぜ悪いのか

多対多の関連をつくるとき、中間テーブルをつくるということをプログラミングスクールなどで習った人も多いかと思いますが、じゃあなぜ中間テーブルを作らなければならないのかと聞かれて答えられる人は中々いないんじゃないでしょうか。今回はそれについてまとめていきたいと思います。

アンチパターンのジェイウォークとは
なにが悪いのか
さいごに

アンチパターンのジェイウォークとは

ジェイウォークとは例えばテーブルのカラムにコンマ区切りのフォーマットのリストを格納するパターンをいいます。

例えば、productsテーブルに複数のアカウントが紐付いていてこれをproductsテーブルのaccout_idカラムにコンマ区切りで値を入れていくという方法をとったとします。これはジェイウォークというアンチパターンになります。

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

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

なにが悪いのか

ここからはジェイウォークをやってしまうと何が悪いのか挙げていきます。

パフォーマンスが低下する

まず、account_idが12のアカウントを取得する際に次のようなSQLを発行することになります。

SELECT * FROM Products SHERE account_id REGEXP '[[:<:]]12[[:>:]]';

一々正規表現を評価しないといけないので、複雑になるかつインデックスを使えないため、すべての行に対して正規表現を評価することになり、パフォーマンスが低下します。

COUNT, SUM, AVGが使えない

COUNT, SUM, AVGなどの関数は行に対して行うものでカンマ区切りリストには使えないため、集約クエリを作成するのに手間がかかってしまいます。

値の更新が簡単でない

値を更新するのにまずIDからリストを取得してデータ構造を崩さないようにして更新しないといけないので、コードが長くなりバグを生むリスクが高くなります。

値を入れられる数に限りがある

カンマ区切りリストで値を格納していくと、文字数の限界を迎えてこれ以上値を入れられないといった状況が来ます。

アカウントIDの妥当性が保証できない

カラムのデータ型はVARCHARで文字列にしてあるので、次のような入力が来てもデータベースはそれを許容してしまいます。

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

さらに、当然ながら外部キーをはれないので、存在しないaccount_idがきても保存できてしまい、参照整合性を保証できません。

さいごに

有名なアンチパターンなだけにデメリットを理解して、交差テーブルをつくるようにしたいところです。
ここまで読んでくださりありがとうございました。

参考

SQLアンチパターン

sql アンチパターン