読者です 読者をやめる 読者になる 読者になる

blog.toxn

あしあと

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

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

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

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

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)
);

いつ起こるのか

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

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

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

何故ダメなのか

  • 上限を超えたときに詰む

言わずもがな

SELECT * 
FROM Customer
WHERE address1 = 'toxn12@gmail.com'
   OR address2 = 'toxn12@gmail.com'
   OR address3 = 'toxn12@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 = 'toxn12@gmail.com'

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