【DB設計アンチパターン】カンマ区切りリストの項目を定義する

DBのカラムにカンマ区切りのリストを定義することは以下のデメリットがあります。

カンマ区切りリストのデメリット

SQLが複雑になる

例えば、tableAはtableBのcodeの一覧であるcode_listというカラムを持つとします。
特定のcodeを保持しているtableAのレコードを取得するには、以下のようにパターンマッチを用いたクエリを記述する必要があります。

パターンマッチを用いた複雑なクエリ

SELECT *
FROM tableA a
WHERE a.code_list LIKE '%,' || '[検索したいコード]' ||  ',%'

実際には対象のコードが先頭、中間、末尾のどこにあるかで、カンマの有無が微妙に変わることも考慮しないといけません。
REGEXP_LIKEを用いればもう少しスマートにできるが、いずれにせよクエリが複雑であることに変わりはありません。
また、何よりパターンマッチのクエリではインデックスが効かないため、性能が出ないという問題があります。

同様の理由で結合のクエリも複雑になります。

複雑な結合クエリ

SELECT *
FROM tableA a
INNER JOIN tableB b
ON a.code_list LIKE '%,' || b.code ||  ',%'

こちらもインデックスが使用されないので、性能が出ません。

インデックスが効かない

前項と一部繰り返しの部分もありますが、検索、結合をするためにはパターンマッチを用いないといけないめ、インデックスが使用されません・
そのため、性能が出ません。

パターンマッチ構文がDBMS間で異なる

パターンマッチ構文はDBMS間で統一されていません。
そのため互換性がありません。

・Oracle

Oracleのパターンマッチ

REGEXP_LIKE ([string], [pattern])

・MySQL

MySQLのパターンマッチ

string REGEXP_LIKE [pattern]

集計クエリが複雑になる

例えばCountする時に、カンマ(区切り文字)の数を数えるなどしないといけません。
これは直感的でないし、データ形式の都合に引きずられたロジックです。
また、先頭や末尾に不要なカンマがあった場合、結果は誤ったものとなります。

リストの順番が保証されない

リストを追加する場合は、以下のようなクエリになります。

リストに追加するSQL

UPDATE tableA
SET code_list = code_list || ',' || '[追加したい子コード]'

これだとリストの順番は追加された順番に過ぎません。
順番が必要なロジックの場合、プログラム側でソートし直す必要があります。

削除クエリが複雑、かつ性能が悪くなる

リストから特定の値を削除する場合には、以下の手順を踏む必要があります。
①リストの値をSELECTし、取得する。
②プログラム内で削除したいコードを削除したリストを生成。
③UPDATE分で上記のリストに更新する。

手間が多い上に、①と③で二回クエリが発行されています。
トランザクションの考慮もしないといけません。

値の妥当性が保証されない

カンマ区切りのリストなので、データ型は可変長文字列型になります。
どんな文字でも入力可能でFKを貼ることもできません。
そのため、本当に正しいコードが入っているかはわかりません。
プログラム側でバリデートする必要が出てきます。

登録できる個数に制限がある

文字列の最大長は決まっています。
そのため、その制限までしかリストを追加することはできません。
交差テーブルを作成すれば、この制限はありません。

カンマ区切りリストを用いるメリット

カンマ区切りリストを分割して使用するのではなく、そのままカンマ区切りリストとして扱う場合はメリットがあります。

交差テーブルを用意しなくていいため、クエリのパフォーマンスが向上します。
プログラム上でカンマで分割して扱うなどの操作が不要になります。

ただし、上記以外の場合は、デメリットの方が大きくなるので基本的にはカンマ区切りリストではなく、交差テーブルを作成するようにします。