エスプリフォートでは、プロのエンジニアとして日々アンテナを張りながら技術力を高め、日々のシステム開発に生かしていっています。
今回は「 システムの中核を担うデータベース 」における設計のアンチパターンを一つご紹介いたします。
テーブル設計のアンチパターンとは
テーブルを設計するにあたり「 これは避けるべき 」といったパターンをアンチパターンと言います。
今回は、そのパターンの一つである「 ジェイウォーク（信号無視） 」について、実例を挙げてご紹介したいと思います。
ジェイウォーク（信号無視）とは
『 一つの列に、,（カンマ）等で複数の値（001,002,003...）を登録する 』パターンです。
最初からこのようなテーブル設計にすることもありえます。
特に変更が入ったときに安易にしやすいパターンであるため、避けるべきパターンとなります。
例えば、以下のような場合です。
- ある学校に所属する生徒のクラブ活動について、考えてみます。
- この学校では、生徒は、必ず一つのクラブに所属しなければなりません。
- そのため、生徒ID、氏名、所属クラブIDからなる「生徒テーブル」と、クラブID、クラブ名からなる「クラブテーブル」を、以下のように考えました。
生徒テーブル
| 生徒ID | 氏名 | 所属クラブID |
|---|---|---|
| 00001 | 山田 太郎 | 001 |
| 00002 | 高田 花子 | 002 |
| 00003 | 鈴木 次郎 | 001 |
| ... |
クラブテーブル
| クラブID | クラブ名 |
|---|---|
| 001 | 野球部 |
| 002 | 硬式テニス部 |
| 003 | サッカー部 |
| 004 | 映画研究会 |
| 005 | 落研 |
| ... |
それからしばらくたった後、このクラブ活動について、大きな変更が入ることになりました。
- 生徒は、２つ以上のクラブに、同時にいくつでも所属してよい。
この変更に対応するために、「生徒テーブル」の所属クラブIDに、「,（カンマ区切り）」で値を入力できるようにしました。
生徒テーブル（変更後）
| 生徒ID | 氏名 | 所属クラブID |
|---|---|---|
| 00001 | 山田 太郎 | 001,004 |
| 00002 | 高田 花子 | 002,004 |
| 00003 | 鈴木 次郎 | 001,003,005 |
| ... |
...と、このように変更してしまうと一見、テーブル構造の変更もなく楽なのですが、これは避けるべき変更です。
なぜ、避けるべきか
例えば、以下のデメリットが考えられます。
１．検索や集約処理を行う場合にパターンマッチや区切り文字の除去を行わなければならなくなり、クエリが煩雑になる。
例えば、004（映画研究会）に所属している生徒の一覧を取得する場合のSQLは、以下のようになります。
select id, name from students
where club_Id REGEXP '[[:<:]]004[[:>:]]'
order by id;
２．更新も煩雑になる
例えば、「山田 太郎」さんが、001（野球部）から退部する場合、以下の処理が必要になります。
- 「山田 太郎」さんの所属クラブIDを取得する
- 所属クラブIDから、001を探して、取り除く（カンマも考慮する）
- 取り除いた所属クラブで更新する
３．長さが項目長に制限されてしまう
例えば、生徒テーブル（更新後）の項目「所属クラブID」の項目長が12バイトだった場合、所属クラブは、（カンマを除けば）3つまでしか入りません（所属できません）。
これにより、意図しない制約が発生してしまっています。
４．他にこんなデメリットがあります。
-
クエリが煩雑になるため、メンテナンス性、パフォーマンスが悪化する。
-
値の妥当性検証ができない。
無効な値(例：'hoge')でもデータベース側でエラーにならなず、入力できてしまうので値の妥当性検証ができない。 -
区切り文字が不明確になる。
カンマを区切り文字にしている場合では、入力値としてのカンマなのか、区切り文字としてのカンマなのか不明確になる。
ではどうするか
生徒テーブルを変更して一つの項目に複数の値を許容するのではなく、生徒と所属クラブを紐づけるテーブルを作成します（このようなテーブルの事を、中間テーブル、または交差テーブル、と呼びます）。
所属クラブテーブル
| 生徒ID | 所属クラブID |
|---|---|
| 00001 | 001 |
| 00001 | 004 |
| 00002 | 002 |
| 00002 | 004 |
| 00003 | 001 |
| 00003 | 003 |
| 00003 | 005 |
| ... |
このようにすれば、先ほどのデメリットも解消されます。
- クエリが煩雑 => シンプルに
例えば、004（映画研究会）に所属している生徒の一覧を取得する場合のSQLは、以下のようになります。
select students.id, students.name from students
inner join shozoku_club
on students.id = shozoku_club.id
and students.club_id = shozoku_club.club_id
and shozoku_club.club_id = '004'
order by students.id;
- 更新も煩雑になる => シンプルに
例えば、「山田 太郎」さんが、001（野球部）から退部する場合、以下のようになります（SQL一つで済みます）。
delete from shozoku_club
where id = '00001' and club_id = '001';
- 長さが項目長に制限されてしまう => 制限されない
例えば、生徒テーブルの項目「所属クラブID」の項目長が12バイトだった場合であっても、所属クラブテーブルにはなんら影響を与えず、また、所属できるクラブ数にも制約は発生しません。
どうしても利用したい場合は？
基本的には避けるべきですが、それでも適用を検討してもよいのは、以下の場合のみです。
- テーブルの非正規化が必要な場合
- 検索が不要な項目の場合
まとめ
他にも多くのアンチパターンがあります。
- データベース論理設計のアンチパターン
- ナイーブツリー（素朴な木）
- IDリクワイアド（とりあえずID）
- キーレスエントリ（外部キー嫌い）
- EAV（エンティティ・アトリビュート・バリュー）
- ポリモーフィック関連
- マルチカラムアトリビュート（複数列属性）
- メタデータトリブル（メタデータ大増殖）
- データベース物理設計のアンチパターン
- ラウンディングエラー（丸め誤差）
- サーティーワンのフレーバー（31のフレーバー）
- ファントムファイル（幻のファイル）
- インデックスショットガン（闇雲インデックス）
これらアンチパターンを理解し、システムの中核となるデータベース構成を、品質の高く、メンテナンス性が良いシステムとなるように、システム寿命が長くなるようなデータベース設計をできるようになりましょう！
書籍紹介
今回挙げたアンチパターンですが、以下の本にその詳細が載ってます。
テーブルの設計に悩んだら、アンチパターンを避けるためにも、是非目を通してみることをおススメします。
SQLアンチパターン