7章 マルチカラムアトリビュート（複数列属性）
連絡先情報をテーブルに格納する方法を考える場合、姓、名、継承、住所、会社名、まではすぐに思いつくが、電話番号を格納する方法が難しいです。それは、一人の人物が複数の電話番号をもつことがあるからです。
更に、それぞれの電話番号が違った意味を持つ場合が多いです。自宅、職場、ファックス、携帯電話、まであればほとんどのパターンに対応できるでしょうが、携帯電話を2台持っていたり、職場の電話番号を複数登録するとなると破綻します。一体いくつカラムを用意すれば良いのでしょうか？
7.1 目的：複数の値を持つ属性を格納する
以下の例では、バグデータベースに、バグを分類するタグ付け機能を追加する方法を考えます。バグも種類によっていろいろあり、クラッシュするバグには crash、処理速度低下を報告するバグには performance、ユーザーインターフェイスの色遣いの問題などは cosmetic といったタグを付けたいとします。
又、1つのバグに対して複数のタグを付けられるようにする必要があります。それぞれのタグは排他的ではなく、目的も違うからです。
7.2 アンチパターン：複数の列を定義する
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
description VARCHAR(1000),
tag1 VARCHAR(20),
tag2 VARCHAR(20),
tag3 VARCHAR(20)
);
最初に考えたのはこのような設計です。タグのためのカラムを3つ追加しました。実際にタグを追加する場合は以下のような SQL を使います。
UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 3456;
| bug_id | description | tag1 | tag2 | tag3 |
|---|---|---|---|---|
| 1234 | 保存処理でクラッシュする | crash | NULL | NULL |
| 3456 | パフォーマンスの向上 | printing | performance | NULL |
| 5678 | XML のサポート | NULL | NULL | NULL |
もうこの時点でいろいろ問題が出てきてますね。以下で詳しく見ていきます。
7.2.1 値の検索
あるタグの付いたバグを検索する場合は全てのカラムに対する条件を OR でつなぐ必要があります。
SELECT * FROM Bugs
WHERE tag1 = 'performance'
OR tag2 = 'performance'
OR tag3 = 'performance';
複数のタグが付いたバグを検索するには、この連続した OR 句を、更に AND でつなぐ必要がります。
SELECT * FROM Bugs
WHERE (tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance')
AND (tag1 = 'printing' OR tag2 = 'printing' OR tag3 = 'printing');
IN 句を用いると少しは単純に書けます。これでもだいぶ冗長ですが。（というか複数カラムに対して IN 句が指定できたんですね。ｼﾗﾝｶｯﾀ）
SELECT * FROM Bugs
WHERE 'performance' IN (tag1, tag2, tag3)
AND 'printing' IN (tag1, tag2, tag3);
7.2.2 値の追加と削除
タグを既存のバグに追加するときは、どのカラムが開いているか事前に確認しなければなりません。まず、SELECT して……
SELECT * FROM Bugs WHERE bug_id = 3456;
tag2 が NULL だったとしましょう。ここに新しいタグを追加します。
UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 3456;
SELECT から UPDATE の間に、別のクライアントがタグを追加するかも知れません。テーブルをロックしたりして防ぐことも可能ですが、実装がますます複雑になります。
次にタグを削除する場合です。NULLIF 関数を使ってみます。
UPDATE Bugs
SET tag1 = NULLIF(tag1, 'performance'),
tag2 = NULLIF(tag2, 'performance'),
tag3 = NULLIF(tag3, 'performance');
MySQL のマニュアルより抜粋
NULLIF(expr1,expr2)
expr1 = expr2 が TRUE の場合は NULL を返し、それ以外の場合は expr1 を返す。 これは CASE WHEN x = y THEN NULL ELSE x END と同じ。
mysql> SELECT NULLIF(1,1); -> NULL mysql> SELECT NULLIF(1,2); -> 1
現在 NULL が入っている最初のカラムに performance を追加するには次のようなカオスな SQL に成ります。こんなの自分で作成できる自信はありません。SQL 呼び出す側のコードで何とかしたくなりますが、そしたら又テーブルロックだのなんだの、手間がかかります。
UPDATE Bugs
SET tag1 = CASE
WHEN 'performance' IN (tag2, tag3) THEN tag1
ELSE COALESCE(tag1, 'performance') END,
tag2 = CASE
WHEN 'performance' IN (tag1, tag3) THEN tag2
ELSE COALESCE(tag2, 'performance') END,
tag3 = CASE
WHEN 'performance' IN (tag1, tag2) THEN tag3
ELSE COALESCE(tag3, 'performance') END
WHERE bug_id = 3456;
ちなみに、最近よく出てくる COALESCE もマニュアルから引っ張ってみました。
MySQL のマニュアルより抜粋
COALESCE(value,...)
リストの最初の非 NULL 値を戻すか、非 NULL 値がない場合は NULL を戻します。
mysql> SELECT COALESCE(NULL,1); -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL
ついでに辞書でも引いてみました。
co‧a‧lesce /kòuəlés/ [動] [自]
(フォーマル) 合体する, 一体化する, 連合する
coalesce into something
合体して＜…＞になる
coalesce with somebody/something
＜…＞と合体する
—coalescence [名] [U]
合体, 連合, 一体化ロングマン英和辞典 (C) 2006 株式会社ピアソン・エデュケーション 発売元：桐原書店
えーと、「コウアレス」？ しかも後ろにアクセント？ 超読みにくい……
7.2.3 一意性の保証
複数のタグを一つのバグに追加したくはありません……が、防ぐ手立てはありません。まあコーディングでどうにか城って話かもしれませんが、そしたら又ロック云々が……
INSERT INTO Bugs (description, tag1, tag2, tag3)
VALUES ('印刷処理が遅い', 'printing', 'performance', 'performance');
7.2.4 増加する値の処理
やっと出てきました。最初にテーブル定義見た瞬間に気付きますよね。これじゃ最大で3つしかタグ追加できないじゃん！
対処法として、まず思いつくのは ALTER です。あの、恐怖のコマンドです。
ALTER TABLE Bugs ADD COLUMN tag4 VARCHAR(20);
これにより、3つの問題が生じます。
-
ALTER文を実行するためには同時に他のクライアントから接続されていないことを保証する必要があります。テーブル全体をロックするといいかもしれません。 -
少なくとも MySQL では、
ALTER TABLEを実行するとまず一時テーブルに全体がコピーされ、修正が適用され、元のテーブルが削除された後、一時テーブルがリネームされます。これは非常に重い処理です。MySQL のマニュアルより抜粋
ほとんどの場合、 ALTER TABLE は元テーブルのテンポラリ コピーを作成する事で起動します。そのコピー上で変更が行われ、その後元テーブルが削除されて新しいテーブルがリネームされます。ALTER TABLE が実行している間、他のクライアントが元テーブルを読む事ができます。新しいテーブルの準備ができるまで更新と書き込みは止められ、その後更新に失敗する事なく新しいテーブルに自動的にリダイレクトされます。
-
4番目のカラムを追加したことで、今まで作ってきた3つのカラムのための SQL 全てを作り直す必要があります。カラム追加の度にこれを行うのは億劫です。
Multi-Column/anti/search-four-columns.sqlSELECT * FROM Bugs WHERE tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance' OR tag4 = 'performance'; -- このステートメントを追加する必要がある
7.3 アンチパターンの見つけ方
ドキュメントの中に、複数の値を採りうるものの、その最大数が決められているものがあるとするなら黄信号です。
- 「サポートすべきタグの最大数はいくつ？」
- 「SQL で同時に複数の列を検索する方法は？」
7.3.1 アンチパターンに共通するパターン
今回の主題、「マルチカラムアトリビュート（複数列属性）第1章で見た「ジェイウォーク（信号無視）アンチパターン」はよく似ています。これらはどちらも、複数の値を持つ可能性がある属性を格納することを目的としています。まあなんですかね。正規化しろってことですかね。
7.4 アンチパターンを用いても良い場合
関連づけられる属性値の種類が限定できる場合はこのパターンを用いても問題が少ないです。Bugs エンティティに対して関連づけられるアカウントととして次の3種を用意したい場合などが考えられます。
- バグを報告したユーザー。
- バグの修理を割り当てられたプログラマー。
- 修正の確認を割り当てられた品質管理エンジニア。
もっとも、この場合は account1, account2, ... といったカラム名よりかは、reported_by, assigned_to, verified_by のような、具体的な名称を付けた方がいいでしょう。
もう一つの解決法は、Bugs テーブルから Accounts テーブルへの関連を扱う従属テーブルを作ることです。
| bug_id | account_id | attribute_key | attribute_value |
|---|---|---|---|
| 1234 | 2345 | type | reporter |
| 1234 | 3456 | type | programmer |
| 1234 | 4567 | type | engineer |
これは第5章で見た、「EAV（エンティティ・アトリビュート・バリュー）」そのものです。もう一つのアンチパターンに陥る恐れがあります。
7.5 解決策：従属テーブルを作成する
CREATE TABLE `Tags` (
bug_id BIGINT UNSIGNED NOT NULL,
tag VARCHAR(20),
PRIMARY KEY (bug_id, tag),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
この方法なら、値の一意性も担保出来、タグが増加したとしても ALTER TABLE ... などする必要はありません。
タグを指定してのバグ検索も、
SELECT * FROM `Bugs`
INNER JOIN `Tags` AS t1 USING (bug_id)
WHERE t1.tag = 'performance'
;
と平易ですし、複数タグでの検索も、
SELECT * FROM `Bugs`
INNER JOIN `Tags` AS t1 USING (bug_id)
INNER JOIN `Tags` AS t2 USING (bug_id)
WHERE t1.tag = 'printing'
AND t2.tag = 'performance'
;
と、組み立て易く理解しやすい形で書くことが出来ます。
###おまけ
タグマスタとバグ-タグの交差テーブルで管理する場合
CREATE TABLE `Tags` (
tag_id BIGINT UNSIGNED NOT NULL,
tag VARCHAR(20),
PRIMARY KEY (tag_id),
UNIQUE (tag)
);
CREATE TABLE `Bugs_Tags` (
bug_id BIGINT UNSIGNED NOT NULL,
tag_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (bug_id, tag_id),
FOREIGN KEY (tag_id) REFERENCES Tags(tag_id),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
この場合だと、何にも紐付かないタグが作成出来ます。
逆に、そういったタグを許容してしまう構造でもあるので、そこは取捨選択ですかね。