SQLアンチパターンという本を読み始めた。
読んでる間は「なるほど」と思っていても、後で思い出そうとしてみると忘れている部分がけっこうある。
なので各章の要点を自分の言葉で書いてみて、理解と記憶を深めたい。

テンプレート

以下の内容を書く。

何をしてはいけないか。
どういう理由でしてはいけないか。後で読み直したときにすぐ理解できるように具体例も挙げる。
代わりにどうするべきか。

1章ジェイウォーク

何をしてはいけないか

カンマ区切りで複数の値を入力する列を作ってはいけない。

してはいけない理由

理由1. カラム内の要素を検索する時、正規表現が必要になる。

以下のようなテーブルがあるとき、「アカウントyamamotoの人がどれだけのプロジェクトに関わっているか知りたい」というときに正規表現が必要になる。

プロジェクト名	メンバー
勤怠修正プロジェクト	yamamoto,suzuki
社内ネットワークプロジェクト	yamamoto
評価管理プロジェクト	okamoto,takahashi

理由2. カラム内の要素でテーブルを結合できない（できても嬉しくない）。

以下のような2つのテーブルがあるとする。

アカウント名	名前
yamamoto	山本　十三
suzuki	鈴木　光一
sonoda	園田　義弘

プロジェクト名	メンバー
勤怠修正プロジェクト	yamamoto,suzuki

この2つのテーブルをアカウントIDでつなげようとすると、JOINするときに正規表現が必要になる。
SQL書くとするとこんな感じ。

Jaywalking/anti/regexp.sql

SELECT * FROM Projects INNER JOIN Accounts
  ON Projects.メンバー REGEXP '[[:<:]]' || Accounts.アカウント名 || '[[:>:]]'
WHERE プロジェクト名="勤怠修正プロジェクト";

結合したテーブルは

プロジェクト名	メンバー	名前
勤怠修正プロジェクト	yamamoto,suzuki	山本　十三
勤怠修正プロジェクト	yamamoto,suzuki	鈴木　光一

となってナニコレ状態になるので、できたとしてもあんまりうれしくない。

理由3. カラム内の要素を数えるのがシンプルじゃない。

SQLだけでやろうとすると、以下のようになる。(Oracleの場合)

Jaywalking/anti/count.sql

SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS contacts_per_product
FROM Products;

要素の数が「カンマの数+1」になるので、SQLでカラム内のカンマ数を数える処理をして要素数を算出している。
LENGTH関数やREPLACE関数は、関数名がDB製品によって異なるので、DB依存のSQLになる。

理由4. カラムの更新が大変。

値を更新するとき、先頭や末尾に追加する程度であればUPDATE句だけでもできるが、途中に追加するといったことや、名前順となるように追加するといったことをするときに、アプリケーション側で頑張る必要がある。
SQLも、SELECTとUPDATEの2つが必要になる。

理由5. 存在するアカウントだけ入れること、みたいなことが保証できない。

外部キー制約を使うことができないので、入力する値を間違えた場合でも入れることができてしまう。

アカウント名	名前
yamamoto	山本　十三
suzuki	鈴木　光一
sonoda	園田　義弘

というときに、

プロジェクト名	メンバー
勤怠修正プロジェクト	yamamoto,suzuki

に対して「okamoto」というアカウントを追加しようとしたら、メンバーへokamotoが追加できてしまう。
（存在しないアカウントなので追加できないようにするべき）

理由6. 入力できる数に上限がある。

カンマ区切りということはその列の型は文字列型となる。文字列型の列は格納できる文字数の制限があるため、入力できる値の数に上限ができることになる。しかも、入力する内容によっては上限がバラバラになる。
例えばプロジェクトのことを表す以下のようなテーブルがあるとする。

プロジェクト名	リーダー	メンバー
勤怠システム改修プロジェクト	山本　十三	鈴木　光一,園田　義弘,岡本　俊介,アンドリュー　ストレングス

メンバーを入れる列を用意して、メンバーの名前をカンマ区切りで入れる場合、名前が短い人が多ければたくさん入るし、名前が長い人が多ければあんまり入らない。日本人だけなら文字数そこまで長くないけど、海外の人のようにカタカナ表記の人がいる場合は致命的。

理由7. 入力する値に区切り文字が使えない。

例えば区切り文字に","を使っている場合、","を値として使うことができなくなってしまう。使われない文字を区切り文字にしても、その文字を値として保存する必要が出てきたら全部のカラムの区切り文字を変えなければいけなくなる。

代わりにどうするべきか。

交差テーブルを作る。

アカウント名	名前
yamamoto	山本　十三
suzuki	鈴木　光一
sonoda	園田　義弘

プロジェクト名
勤怠修正プロジェクト
評価管理プロジェクト

というテーブルがあったら

プロジェクト名	アカウント名
勤怠修正プロジェクト	yamamoto
勤怠修正プロジェクト	suzuki
評価管理プロジェクト	yamamoto
評価管理プロジェクト	sonoda

というテーブルを作る。こういうテーブルを「交差テーブル」という。

とあるアカウントが関わっているプロジェクトの一覧を出すならWHERE句でアカウント名を指定するだけでいいし
とあるプロジェクトのメンバーの一覧を出すならWHERE句でプロジェクト名を指定するだけでいい。
集計もCOUNT句が使えるので楽。
それぞれの列に外部キー制約をかけておけば存在しない値が入ることがない。

ということでカラムに複数の値を入れてるのを見かけたら、交差テーブルを使うようにする。