📝

カンマ区切りで格納されいるDB設計について考えてみた

2024/09/22に公開

検索しやすいようにタグを設定して記事を投稿できるサービス。
「タグ名を変更したが、変更前の名前のタグがついた記事がある」と問い合わせがあったのでDB設計を調査してみた。

現状のテーブル構造

tagsにtagA,tagB,tagCのようにカンマ区切りでタグの情報が格納されていた。

複数タグが設定されている投稿の更新がうまくいっていないようだ。
調べたところ、複数の値を持つ属性をカンマ区切りのリストで管理することは「ジェイウォーク(信号無視)」と呼ばれるDB設計のアンチパターンであることがわかった。

問題点

この設計には以下のような問題が発生してしまう。

タグの変更が複雑になる
タグ名を変更する場合、カンマ区切りの文字列から該当タグを抽出して置き換える必要があるために複雑なクエリになってしまう。

UPDATE Post
SET tags = REGEXP_REPLACE(tags, '(^|,)tagA(,|$)', '\\1newTagA\\2')
WHERE tags REGEXP '(^|,)tagA(,|$)';

不正な値や重複、データ不整合が発生しやすい
カンマ区切りのリストにはFK(外部キー)制約を設定できないので、DB側で不正なデータのチェックができず重複やデータ不整合が発生しやすくなってしまう。
検索が非効率
タグを使って検索や結合（JOIN）する際、カンマ区切りの値を使うことでクエリが複雑化し、パフォーマンスが低下してしまう。

SELECT * 
FROM Post AS p INNER JOIN Tags t
    ON p.tags REGEXP CONCAT('(^|,)', t.tag_name, '(,|$)')
WHERE p.id = 'P000001'

インデックスが効かない
tagsに対してインデックスが適用できず、特定のタグを検索する際にはLIKE句を使うことになってしまい、パフォーマンスが大幅に低下。

SELECT * 
FROM Post 
WHERE tags LIKE '%tagA%';

どのように解決するか

Postからtagsを正規化して別テーブルに分ける。
タグと投稿は多対多の関係になるため、これを管理するために中間テーブルを作成。

正規化により、タグの検索や結合クエリがシンプルになってインデックスも適用できるため、パフォーマンスが向上する。

SELECT * 
FROM Post AS p INNER JOIN PostTags pt
    ON p.post_id = pt.post_id
WHERE p.post_id = 'P000001'

カンマ区切りで格納されいるDB設計について考えてみた

現状のテーブル構造

問題点

どのように解決するか

参考文献

Discussion