マルチカラムアトリビュート
ジェイウォークと同様に、一つのテーブルに属するべき値属性が複数存在する場合のアンチパターン。
以前記事にしたジェイウォークでは、一つの列に複数の値を格納していたのに対し、今回のマルチカラムアトリビュートではあらかじめ複数列を用意しておき値があった場合にその列に値を格納し、値がない場合はNULLのままにする。
ジェイウォークと比べ、マルチカラムアトリビュートでは1対多の関係を表すために用いられることが多い。
簡単な例として、以下のようなユーザごとに電話番号を格納するテーブルがあったとする。
ユーザごとに携帯番号、固定電話の有無、FAXの有無などにより不特定多数の値が格納される場合がある。
値が幾つになるかわからないためあらかじめ「tel」列を複数作成しておき、値がある分だけ登録する。
phone_number
| user_id | name | tel1 | tel2 | tel3 | tel4 |
|---|---|---|---|---|---|
| 1 | USER１ | 111-2222-3333 | 222-3333-4444 | 333-4444-5555 | NULL |
| 2 | USER２ | 444-5555-6666 | NULL | NULL | NULL |
| 3 | USER３ | 555-6666-7777 | 666-7777-8888 | NULL | NULL |
マルチカラムアトリビュートの問題点
- 値の検索
- 値の追加と削除
- 一意性の保証
- 増加する値の処理
値の検索
特定のペットを飼っているユーザを検索する際「pet」列のどこにでも格納される可能性があります。
そのため、全ての列を検索してくる必要があります。
pet
| user_id | name | pet1 | pet2 | pet3 | pet4 |
|---|---|---|---|---|---|
| 1 | USER１ | dog | cat | snake | fish |
| 2 | USER２ | cat | fish | NULL | NULL |
| 3 | USER３ | snake | lizard | iguana | NULL |
| 4 | USER４ | NULL | NULL | NULL | NULL |
例１
「dog」を飼っているユーザを検索する
SELECT
*
FROM
pet
WHERE
pet1 = 'dog'
OR
pet2 = 'dog'
OR
pet3 = 'dog'
OR
pet4 = 'dog';
「pet」列のいずれかに「dog」を含む値がある行を検索する場合、上記のクエリのように全列に対して条件をつけて検索する必要がある。
例2
「snake」「iguana」「lizard」を全て飼っているユーザを検索する
SELECT
*
FROM
pet
WHERE
(tag1 = 'snake' OR tag2 = 'snake' OR tag3 = 'snake' OR tag4 = 'snake')
AND
(tag1 = 'iguana' OR tag2 = 'iguana' OR tag3 = 'iguana' OR tag4 = 'iguana')
AND
(tag1 = 'lizard' OR tag2 = 'lizard' OR tag3 = 'lizard' OR tag4 = 'lizard');
かなり冗長なクエリになった。
IN句を用いることで構文はコンパクトにすることが可能。
値の追加と削除
UPDATEにて値の追加をしたいのですが、「pet」列の何番目が開いているのかわからないため安全に値を追加できません。
そのため、一度対象の行を取得したのちに更新をする必要があります。
pet
| user_id | name | pet1 | pet2 | pet3 | pet4 |
|---|---|---|---|---|---|
| 1 | USER１ | dog | cat | snake | fish |
| 2 | USER２ | cat | fish | NULL | NULL |
| 3 | USER３ | snake | lizard | iguana | NULL |
| 4 | USER４ | NULL | NULL | NULL | NULL |
例１
「USER2」が新しく「parrot」を購入します。
SELECT
*
FROM
pet
WHERE
user_id = 2;
「user_id」が2のデータを取得して値が「NULL」の「pat」を取得する。
上記のクエリで「pet3」が「NULL」となっているためこの列に値を追加する。
UPDATE
pet
SET
pet3 = 'parrot'
WHERE
user_id = 2;
削除の場合も同様に、一度削除したいレコードがどの列に存在するのか確認してから『NULL』に更新する必要がある。
値を更新する際の懸念点として他のクライアントが値を更新してしまう可能性があるため、ロックした状態で競合や上書きを防ぐ必要がある。
一意性の保証
複数ある列に重複した値は登録したくない場合、マルチカラムアトリビュートでは防ぐことができない。
例１
INSERT INTO
pet (user_id, neme, pet1, pet2, pet3, pet4)
VALUES
(5, 'USER5', 'monkey', 'monkey', 'monkey', 'monkey');
増加する値の処理
pet
| user_id | name | pet1 | pet2 | pet3 | pet4 |
|---|---|---|---|---|---|
| 1 | USER１ | dog | cat | snake | fish |
| 2 | USER２ | cat | fish | NULL | NULL |
| 3 | USER３ | snake | lizard | iguana | NULL |
| 4 | USER４ | NULL | NULL | NULL | NULL |
マルチカラムアトリビュートでは列の増加に対応できない。
例えば「USER1」が新たに「flog」を買うことになった場合、すでに列に値が全て格納されているため値を格納できない。
列数は最大値と同様の数必要になるため、不特定多数の値を格納する場合はその列数を予測してあらかじめ用意する必要がある。
しかしそれは難しい。
対処法としてはあらかじめ適度な数の列を用意して、あとから列数を拡張していく方法。
しかしこの方法では新たに以下の問題点が発生する。
- テーブルの構成を変更する際、他のクライアントからのアクセスを防ぐためテーブル全体のロックが必要になることもある
- 使用しているデータベースによっては新規テーブルを用意し、既存テーブルからコピーする必要がある。
- テーブルの構成を変えた場合、アプリケーションで使用されているクエリを変更する必要がある。
上記のような問題が発生し、データ不整合やバグが発生するリスクが高まる。
解決策
値の並び順に意味がある場合や列数が確定していて増加の可能性がない場合を除き以下の解決策にてテーブルの構成を変更した方が良い。
属性を格納する列を1つ持つ従属テーブルを作成し、従属テーブルで外部キーを定義し親テーブルの行に値を関連付ける。
そうすることで値を「列」ではなく「行」に格納できる。
user
| user_id | name |
|---|---|
| 1 | USER１ |
| 2 | USER２ |
| 3 | USER３ |
| 4 | USER４ |
pet
| user_id | pet |
|---|---|
| 1 | dog |
| 1 | cat |
| 1 | snake |
| 1 | fish |
| 2 | cat |
| 2 | fish |
| ... | ... |
まとめ
同じ意味を持つ値は、1つの列に格納しましょう。
次回
メタデータトリブル
参考文献
