SQLアンチパターン　第一章を読んで

アンチパターンのテーブル例

Books

book_idbook_nametag_id
1SQL-anti-pattern1, 2
2Readable Code2

Tags

tag_idtag_name
1SQL
2O'Reilly

このテーブルの関係では、本はタグを複数持っていてタグは複数の本につけられています。この多対多の関係を、中間の関係を示すテーブルなしで外部キー、つまりtag_idを一つのフィールドにカンマ区切りで格納しています。
これが今回のアンチパターン「信号無視」です。

デメリット

  1. 特定のタグをもつ本を探したくてもtag_id = 1 のような形で探せないため、1があるかを正規表現なんかでパターンマッチする必要があります。これだと式の組み方を間違えてしまう可能性も高く、そもそも式の組み方がMySQLやPostgreSQL等RDBMSで違ってきます。

  2. 特定の本についているタグを結合したくても、その本のtag_idフィールドでパターンマッチしなくてはならないので効率的ではありません。

  3. 集約クエリ（COUNT等）は複数の行に対して行うように設計されているので、例えば本についているタグの数を数えたい時なんかにそのまま使用ができません。

  4. tag_idをカンマ区切りのリストの末尾に追加するのは必ずしも番号順ではないためソート順が維持できなくなるかもしれません。一つタグを削除するのも古いtag_idをまるごと捨ててと新しいtag_idをまるっと入れねばなりません。

  5. tag_idにはVARCHARで文字列なら何でも入れられます。危険です。

  6. リストの長さがVARCHAR(30)等で簡単に限られてしまいます。

使ってもいい場合

なにかで区切られたデータが必要で、かつその個別の要素にアクセスが不要な場合はこの方法を用いても良い場合に挙げられています。
少しそんな場合を考えたのですが、多対多でそんなケースありえるんでしようか？　思いつきませんでした。

解決方

交差テーブルを作成する。いわく中間テーブルってやつですね。
BookTaggingsテーブルとかでしょうか。

book_idtag_id
11
12
22

こうすることでデメリットを以下のように解決できます。

  • 1及び2
    特定の本のタグを検索するときにはTagsテーブルを結合してbook_idで絞り込めばいいですし、その逆もしかりです。インデックスを有効に使えます。

  • 3
    行が別になっているので、例えば本についているタグの数を数えたい時はbook_idでGROUP BYしてそのまま数えることができます。

  • 4
    BookTaggingsテーブルに行を挿入、もしくは行を削除すればいいだけです。

  • 5
    フィールドの内容が同一なので、BookTaggingsテーブルのbook_idカラムにはBooksテーブルのbook_idしか入れられないという外部キー制約をかけることができます。

  • 6
    一つのテーブルに格納できる行数以外の制約はもうありません！

感想

中間テーブルって何で必要なんだろう？という疑問をSQLの視点から答えてくれる章でした。中間テーブル、大事です。まだ業務では作ったことないですが。