勉強会
SQLアンチパターン

7章 マルチカラムアトリビュート(複数列属性)

More than 3 years have passed since last update.


7章 マルチカラムアトリビュート(複数列属性)

連絡先情報をテーブルに格納する方法を考える場合、姓、名、継承、住所、会社名、まではすぐに思いつくが、電話番号を格納する方法が難しいです。それは、一人の人物が複数の電話番号をもつことがあるからです。

更に、それぞれの電話番号が違った意味を持つ場合が多いです。自宅、職場、ファックス、携帯電話、まであればほとんどのパターンに対応できるでしょうが、携帯電話を2台持っていたり、職場の電話番号を複数登録するとなると破綻します。一体いくつカラムを用意すれば良いのでしょうか?


7.1 目的:複数の値を持つ属性を格納する

以下の例では、バグデータベースに、バグを分類するタグ付け機能を追加する方法を考えます。バグも種類によっていろいろあり、クラッシュするバグには crash、処理速度低下を報告するバグには performance、ユーザーインターフェイスの色遣いの問題などは cosmetic といったタグを付けたいとします。

又、1つのバグに対して複数のタグを付けられるようにする必要があります。それぞれのタグは排他的ではなく、目的も違うからです。


7.2 アンチパターン:複数の列を定義する


Multi-Column/anti/create-table.sql

CREATE TABLE Bugs (

bug_id SERIAL PRIMARY KEY,
description VARCHAR(1000),
tag1 VARCHAR(20),
tag2 VARCHAR(20),
tag3 VARCHAR(20)
);

最初に考えたのはこのような設計です。タグのためのカラムを3つ追加しました。実際にタグを追加する場合は以下のような SQL を使います。


Multi-Column/anti/update.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 でつなぐ必要があります。


Multi-Column/anti/search.sql

SELECT * FROM Bugs

WHERE tag1 = 'performance'
OR tag2 = 'performance'
OR tag3 = 'performance';

複数のタグが付いたバグを検索するには、この連続した OR 句を、更に AND でつなぐ必要がります。


Multi-Column/anti/search-two-tags.sql

SELECT * FROM Bugs

WHERE (tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance')
AND (tag1 = 'printing' OR tag2 = 'printing' OR tag3 = 'printing');

IN 句を用いると少しは単純に書けます。これでもだいぶ冗長ですが。(というか複数カラムに対して IN 句が指定できたんですね。シランカッタ)


Multi-Column/anti/search-two-tags.sql

SELECT * FROM Bugs

WHERE 'performance' IN (tag1, tag2, tag3)
AND 'printing' IN (tag1, tag2, tag3);


7.2.2 値の追加と削除

タグを既存のバグに追加するときは、どのカラムが開いているか事前に確認しなければなりません。まず、SELECT して……


Multi-Column/anti/add-tag-two-step.sql

SELECT * FROM Bugs WHERE bug_id = 3456;


tag2NULL だったとしましょう。ここに新しいタグを追加します。


Multi-Column/anti/add-tag-two-step.sql

UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 3456;


SELECT から UPDATE の間に、別のクライアントがタグを追加するかも知れません。テーブルをロックしたりして防ぐことも可能ですが、実装がますます複雑になります。

次にタグを削除する場合です。NULLIF 関数を使ってみます。


Multi-Column/anti/remove-tag.sql

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 呼び出す側のコードで何とかしたくなりますが、そしたら又テーブルロックだのなんだの、手間がかかります。


Multi-Column/anti/add-tag.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 一意性の保証

複数のタグを一つのバグに追加したくはありません……が、防ぐ手立てはありません。まあコーディングでどうにか城って話かもしれませんが、そしたら又ロック云々が……


Multi-Column/anti/insert-duplicate.sql

INSERT INTO Bugs (description, tag1, tag2, tag3)

VALUES ('印刷処理が遅い', 'printing', 'performance', 'performance');


7.2.4 増加する値の処理

やっと出てきました。最初にテーブル定義見た瞬間に気付きますよね。これじゃ最大で3つしかタグ追加できないじゃん!

対処法として、まず思いつくのは ALTER です。あの、恐怖のコマンドです。


Multi-Column/anti/alter-table.sql

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.sql

  SELECT * 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 解決策:従属テーブルを作成する


Multi-Column/soln/create-table.sql

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 ... などする必要はありません。

タグを指定してのバグ検索も、


MultiColumn/soln/find-by-single-tag.sql

SELECT * FROM `Bugs`

INNER JOIN `Tags` AS t1 USING (bug_id)
WHERE t1.tag = 'performance'
;

と平易ですし、複数タグでの検索も、


MultiColumn/soln/find-by-double-tags.sql

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

と、組み立て易く理解しやすい形で書くことが出来ます。


おまけ

タグマスタとバグ-タグの交差テーブルで管理する場合


Multi-Column/soln/create-table.sql

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


この場合だと、何にも紐付かないタグが作成出来ます。

逆に、そういったタグを許容してしまう構造でもあるので、そこは取捨選択ですかね。