SQLアンチパターンのジェイウォークパターンのデメリットと解決方法

こちらの記事では、「SQLアンチパターン」に登場するジェイウォークについてまとめたものになります。

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

ジェイウォークパターンは、１つの列に複数の値を格納するアンチパターンです。たいていの場合、多対多の関連を表現する交差テーブルの作成を避けるために、カンマ区切りのリストを列に格納する方法です。

ジェイウォークパターンのデメリット

特定の値を持つ行の検索

外部キーにあたるデータを１つの列にまとめて格納すると、等価性による比較が行えません。そのため、何らかの文字列パターンに対するパターンマッチを行う必要があります。

SELECT * FROM Products WHERE account_id LIKE '%,12,%';

上記のSQLは、12という account_id を持つ行の検索を行いますが、12が真ん中に存在する必要があり、先頭や末尾に12という id が存在する場合は、検索に引っかからないという問題点があります。

また、インデックスが適用されないので検索も遅く、パターンマッチ構文はミドルウェアによって書き方が異なるので管理が大変になります。

紐づく値を持つテ－ブルとの結合

「特定の値を持つ行の検索」と同様にパターンマッチを使ってテーブルの結合を行わなければならないです。

インデックスを使用する機会が失われ、全ての行に対して正規表現を評価してしまいます。

集約クエリの作成

集約関数（COUNT, SUM, AVGなど）は、複数の行に対して利用される前提であるため、1つの列に複数の値が含まれている場合は利用できません。

特定の値を持つ列の更新

列の文字列を連結することで、新たな値を列の末尾に追加できますが、ソート順までは担保されません。

UPDATE Products SET account_id = account_id + ',56' WHERE product_id = '1';

値の削除を行う場合は、元の行の削除と新しい行の追加を行う必要があり、複雑なSQL文を使用することになり管理が大変になります。

挿入データの妥当性検証

複数の値を1列に格納する場合、基本的には文字列リストで格納することになります。そのため、列定義による文字制限ができません。行の挿入時・更新時に煩雑な制御を掛けるか、無効文字の混在を許容することになります。

例）account_idは正整数値しか扱わないのに、文字が紛れている

区切り文字の識別

文字列のリストを格納する時、個々の入力値の中に区切り文字が含まれる可能性があります。

例えば、区切り文字としてカンマを使用している場合、入力値にカンマが含まれてしまうと、区切り文字のカンマなのか文字列としてのカンマなのかがわからなくなってしまいます。

リストの長さ制限

例えば、テーブル定義の際に列の長さを文字列の30とします。入力文字が2文字だとするとカンマを含めて3文字なので、10個までのデータを格納することができます。

入力文字の文字数によっては、数が減ったりすることもあるのと入るデータの数が決まってしまい、枯渇した場合の対処ができなくなってしまいます。

ジェイウォークパターンの改善方法

解決方法として、交差テーブルを作る方法があります。account_id を Products テーブルに格納するのではなく、新たに Contacts テーブルを作成して管理します。

その結果、Products テーブルとの間に「多対多」の関連が生じるようになります。

交差テーブルを実現することで、account_id にインデックスを設定することができ、パフォーマンスの向上も見込めます。

また、追加の要素があれば列として追加できるので、カンマ区切りで管理していたテーブルと比べるとできることの幅が広がります。

まとめ

今回は、「SQLアンチパターン」に登場するジェイウォークパターンに関する解説を行いました。

カンマ区切りでの値管理は、パフォーマンスやメンテナンス面において不都合な点が多いので、交差テーブルを作ることをオススメします！