SQLアンチパターンその７　マルチカラムアトリビュート

６章がちょっと書きにくいので、とばします！

マルチカラムアトリビュートとは

「複数列の属性」の名前の通り、１つのレコードの中で「２つ目の住所」「３つ目のメールアドレス」のような複数存在する可能性がある属性のこと。テーブルの列数は可変にできないけれど、一体いくつ用意しておけばいいのか予測不能！

CREATE TABLE Customer (
  customer_id      INT UNSIGNED NOT NULL,
  name            VARCHAR(1000),
  address1        VARCHAR(20),
  address2        VARCHAR(20),
  address3        VARCHAR(20),
  ...
  PRIMARY KEY (customer_id)
);

いつ起こるのか

テーブルを設計するときに、属性として「複数」登録できそうな列があったとき。いくつまで用意しておけばいいのかと思ったら怪しい。

何をしてはいけないのか（アンチパターン）

複数の列を定義すること。

何故ダメなのか

上限を超えたときに詰む

言わずもがな

マルチカラムアトリビュートの中から特定の値を検索するときのSQLが不便

SELECT * 
FROM Customer
WHERE address1 = 'sample@gmail.com'
   OR address2 = 'sample@gmail.com'
   OR address3 = 'sample@gmail.com'

属性に値を追加・更新・削除するときにはもっと面倒なことになる
address1, address2, address3に空きがあるか？
空いている列に値を入れようとしている間に別のユーザが更新している可能性は？
追加・更新された値がレコード内で一意になっているか？

SQLが複雑になること必至

いい解決策は

従属テーブルを作る

CREATE TABLE Customer (
  customer_id      INT NOT NULL,
  name            VARCHAR(1000),
  address1        VARCHAR(20),
  address2        VARCHAR(20),
  address3        VARCHAR(20),
  ...
  PRIMARY KEY (customer_id)
);


CREATE TABLE Customer_Address (
  customer_id     INT NOT NULL,
  address        VARCHAR(20) NOT NULL,
  PRIMARY KEY (customer_id, address),
  FOREIGN KEY ( customer_id ) REFERENCES Customer(customer_id) ON DELETE CASCADE ON UPDATE CASCADE,
);

これで上限気にせずに追加ができるし、検索も楽

SELECT *
FROM Customer AS c
INNER JOIN Customer_Address AS ca ON c.customer_id = ca.customer_id
WHERE c.address = 'sample@gmail.com'

「列増やすなら行増やせ」

blog.toxn

あしあと