ユーザーの複数の保有資格のデータを扱う
こんなブログを読んでいる方はＳＥとかプログラマが多いのだと思うのだけど、ユーザー管理のプログラムを作っているときなど、こんな要望を受けたことはないだろうか？
「ユーザーの保有している資格を管理して欲しい。資格５種類あって、ユーザーはいくつかの資格を保有する予定。」
タイプＡ
単純にプログラムするならユーザーテーブルに資格A、資格Bと、持つという方法が一般的。
ユーザーマスタ
| ユーザid | 氏名 | … | 資格A | 資格B | 資格C | 資格D | 資格E |
|---|---|---|---|---|---|---|---|
| 1 | 山田 太郎 | … | true | false | true | false | false |
| 2 | 佐藤 一郎 | … | true | false | false | true | true |
ところがである。あるとき、「資格の個数が増る」だよね。って言われることがある。そうすると、途端にプログラムを書き換えないとならなくなります。
タイプＢ
こういうときは、ちゃんと資格をマスターテーブルにしてという方法があります。
ユーザーマスタ
| ユーザid | 氏名 | … | 資格1 | 資格2 | 資格3 | 資格4 | 資格5 |
|---|---|---|---|---|---|---|---|
| 1 | 山田 太郎 | … | 1 | 3 | 8 | ||
| 2 | 佐藤 一郎 | … | 1 | 4 | 5 |
資格マスタ
| 資格id | 名称 |
|---|---|
| 1 | 資格A |
| 2 | 資格A |
| … | … |
| 8 | 資格H |
| 9 | 資格I |
ところがこの方法には、弱点もあります。まず、一人当たりの保有する資格が増えるとテーブルの構造を変更しないとならなくこと。もうひとつは、資格を保有しているユーザーを検索する方法が面倒になることです。
たとえば、資格Iを保有しているユーザーを検索するには、ユーザーの資格欄ごとに検索しないとなりません。
SELECT * FROM [ユーザーマスタ]
WHERE [資格1] = 9 OR [資格2] = 9 OR [資格3] = 9 OR [資格4] = 9 OR [資格5] = 9 OR [資格2] = 9
タイプＣ
こんなときに取られる方法が、もっとも汎用性の高い、ユーザーマスタ、資格マスタ、ユーザー保有資格テーブルの３つ完全に分離する方法です。
ユーザーマスタ
| ユーザid | 氏名 | … |
|---|---|---|
| 1 | 山田 太郎 | … |
| 2 | 佐藤 一郎 | … |
資格マスタ(タイプＢと同じ)
ユーザー保有資格テーブル
| ユーザid | 資格ID |
|---|---|
| 1 | 1 |
| 1 | 3 |
| … | … |
この方法のメリットは、なんと言っても拡張性と柔軟性があることです。
SELECT U.* FROM [ユーザーマスタ] AS U
JOIN [ユーザー保有資格テーブル] AS C ON U.ユーザid = C.ユーザid
WHERE C.資格ID = 9
この方法はデータベースから見ると完璧な方法なのですが、プログラムするほうから見ると、ユーザーマスタを編集すると一緒にユーザー保有資格テーブルも編集しないとなりません。結構面倒なことになります。ユーザーマスタを削除すると一緒に、ユーザー保有資格テーブルも削除しないと不整合が生じたりと、プログラムの作成には注意が必要になります。
タイプＤ
最後にちょっとデータベース的にはトリッキーだけど、ちょっと変わった方法を説明します。
タイプＢでは、それぞれのユーザーが持つ資格を整数の項目にしましたが、この方法では文字列にします。保有している資格を文字列の形で適当な記号をセパレータにして記録します。ここでは、|を記号にしました。
ユーザーマスタ
| ユーザid | 氏名 | … | 資格 |
|---|---|---|---|
| 1 | 山田 太郎 | … | |1|3|8| |
| 2 | 佐藤 一郎 | … | |1|4|5| |
前後も同じ記号をつけることで検索もこんな風になります。タイプＣに比べ、検索のコスト的には高くなりますが、ユーザー件数が１００万件とかと膨大にならない限り十分に対応できます。
SELECT * FROM [ユーザーマスタ] WHERE [資格] LIKE ‘|3|’
タイプＥ
最後に、PostgreSQLで導入されたArray型を使う方法を説明します。資格を整数のArray型にすることで、簡単に表すことができます。
ユーザーマスタ
| ユーザid | 氏名 | … | 資格 |
|---|---|---|---|
| 1 | 山田 太郎 | … | 1,3,8 |
| 2 | 佐藤 一郎 | … | 1,4,5 |
SELECT * FROM [ユーザーマスタ] WHERE 3 IN [資格]
■まとめ
| タイプ | メリット | デメリット |
|---|---|---|
| タイプＡ | 単純でデータの扱いも簡単 | 拡張性が無く、拡張があると改修が必要 |
| タイプＢ | 拡張性はまずまず。 | 検索などのプログラムなどが面倒。一人当たりの保有資格数を増やすと改修が必要。速度的には少々疑問。 |
| タイプＣ | 将来的な拡張性がある。スタッフ、資格ともに多くなっても対応可能 | プログラムの作成が大変。データの取り扱いに注意を要する。 |
| タイプＤ | 将来的な拡張性が高い。速度はまずまず。 | データベース的にはトリッキーな方法です。 |
| タイプＥ | 将来的な拡張性、データ取扱も簡単。 | 対応するデータベースが少ない |
こういう将来に渡って使われるデータでは、将来性と開発の容易さを勘案してデータ構造を決めないとあとで大変な思いをすることになります。
yna
