はじめに
本エントリーは某社内で実施するSQLアンチパターン勉強会向けの資料となります。
本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはSQL Serverに置き換えて解説します。
マルチカラムアトリビュートパターンとは
マルチカラムアトリビュート(複数列属性)パターンは、1つのテーブルに属するべきだと思われる属性に複数の値がある場合のアンチパターンです。「第一回 ジェイウォークパターン」と同じテーマの問題ですが、異なる点が2点あります。
- ジェイウォークパターンと比べ、マルチカラムアトリビュートパターンは1対多関連を表現するために用いられることが多い。
- ジェイウォークパターンは一つの列にカンマ区切りで値を格納していたが、マルチカラムアトリビュートパターンは、列を複数作成し値を格納する。
例)バグを分類するためにタグ付けを行うBugsテーブル
| bug_id | description | tag1 | tag2 | tag3 |
|---|---|---|---|---|
| 1234 | 保存処理でクラッシュする | crash | NULL | NULL |
| 3456 | パフォーマンスの向上 | printing | performance | NULL |
| 5678 | XMLのサポート | NULL | NULL | NULL |
データベース設計では、各列には値を1つ格納するべきです。それならば、上記例のように1つのタグを格納する列を複数作成するのが自然の選択に思えますが、この設計にはいくつかの問題があります。
#マルチカラムアトリビュートパターンの問題点
マルチカラムアトリビュートパターンを使用すると、以下の操作時に問題が発生します。
- 値の検索
- 値の追加と削除
- 一意性の保証
- 増加する値の処理
#値の検索
特定のタグが付けられたバグを検索したい場合、タグはtag1～tag3のどれにでも格納される可能性があるため3列すべてを取得する必要があります。
例1)「performance」タグが付いたバグを検索するsql
SELECT * FROM Bugs
WHERE tag1 = 'performance'
OR tag2 = 'performance'
OR tag3 = 'performance';
例2)「performance」と「printing」の両方のタグが付いたバグを検索するsql
SELECT * FROM Bugs
WHERE 'performance' IN (tag1, tag2, tag3)
AND 'printing' IN (tag1, tag2, tag3);
複数の列から1つの値を検索するという単純なsqlが長く手間がかかったものになってしまいます。
#値の追加と削除
UPDATEを用いて1列のみを対象とした変更を行おうとしても、tag1～tag3のどの列が空いているか確認できないため安全に変更出来るとは言えません。確認するためには、対象の行を一度取得する必要があります。
例)「performance」タグを追加するsql
SELECT * FROM Bugs WHERE bug_id = 1234;
--bug_idが1234のデータを取得
--値がNULLになっているtagを確認してUPDATE文を作成する
UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 1234;
またテーブルを検索してから更新するまでの間に、他のクライアントが同じ操作をする可能性があります。その結果更新の競合が起きたり上書きされてしまう危険性があります。これらを避けるためには複雑なsqlが必要になります。
#一意性の保証
複数の列に同じタグ値を格納したくありませんが、マルチカラムアトリビュートパターンでは防ぐことが出来ません。例えば以下のようなデータでも登録されてしまいます。
例)
INSERT INTO Bugs (bug_id, description, tag1, tag2, tag3)
VALUES (9876, '印刷処理が遅い', 'printing', 'performance', 'performance');
#増加する値の処理
バグに付けることが出来るタグの数が4つ以上になった場合対処が出来ません。1列に1つ値を格納する設計を続けるには、タグ数の最大値と同数の列を定義する必要がありますが、テーブル定義時にタグ数の最大値を予測することは難しいでしょう。対処法として、最初に適当な数の列を定義しておき必要に応じて列を追加する方法が考えられますが、その場合以下の3つの問題が生じてしまいます。
- 変更時にテーブル全体をロックしなくてはならない可能性がある。
- データベースによってはテーブル変更時に、新規テーブルを定義し既存テーブルからデータをコピーして、既存テーブルを削除するという処理を行う。データ量が多い場合データ転送に時間がかかってしまう。(MySQLではこの挙動をするようですが、SQLServerでは不明です)
- 列を追加するたびに既存のsqlを編集する必要がある。
#アンチパターンの見つけ方
複数の値を割り当てられる属性があった時、その属性に値の個数の上限が定められている場合アンチパターンが用いられていると考えられます。中には意図的に値の個数の上限を設定している属性もありますが、多くの属性は本来制限を持つ必要がないものです。属性の制限に正当な理由が見当たらない場合アンチパターンが用いられている可能性があります。
#アンチパターンを用いてもよい場合
正当な理由がある場合、属性値の上限を制限しても良いでしょう。正当な理由とは、例えば値の順番に重大な意味を持つ場合や列の意味が異なる場合などが挙げられます。
例)複数のユーザアカウントと関連付けられる、かつ列の役割が異なるテーブル
| bug_id | description | bugを報告したユーザ | 修正担当 | 品質管理担当 |
|---|---|---|---|---|
| 1234 | 保存処理でクラッシュする | 0001 | 0002 | 0003 |
| 3456 | パフォーマンスの向上 | 0003 | 0001 | 0002 |
| 5678 | XMLのサポート | 0004 | 0003 | NULL |
上記テーブルの場合、それぞれの列にはユーザのIDが入りますが各列の意味合いが異なるため、アンチパターンには当てはまりません。
#アンチパターンの解決策
最善の解決策は属性を格納する列を1つ持つ従属テーブルを作成することです。従属テーブルで外部キーを定義し親であるBugsテーブルの行に値を関連付けます。
例)TagsテーブルのCREATE文
CREATE TABLE Tags (
bug_id BIGINT NOT NULL,
tag VARCHAR(20),
PRIMARY KEY (bug_id, tag),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);
Bugsテーブル
| bug_id | description |
|---|---|
| 1234 | 保存処理でクラッシュする |
| 3456 | パフォーマンスの向上 |
| 5678 | XMLのサポート |
Tagsテーブル
| bug_id | tag |
|---|---|
| 1234 | crash |
| 3456 | printing |
| 3456 | performance |
個々のバグに付けられたタグはすべて従属テーブルの1つの列に格納されるので、バグの検索が簡単になります。
例)「performance」タグが付いているバグを検索するsql
SELECT * FROM Bugs INNER JOIN Tags
ON Bugs.bug_id = Tags.bug_id
WHERE tag = 'performance';
タグの追加や削除を行う際も、単純に従属テーブルへの操作になるため容易に操作が行えます。
例)タグの追加と削除のsql
INSERT INTO Tags (bug_id, tag) VALUES (1234, 'save');
DELETE FROM Tags WHERE bug_id = 1234 AND tag = 'crash';
PRIMARY KEY制約によって重複を禁止することも出来ます。
従属テーブルを用いることで、バグに対して必要な数だけタグを付けられるようになりました。
#まとめ
同じ意味を持つ値は、1つの列に格納しましょう。