エスプリフォートでは、プロのエンジニアとして日々アンテナを張りながら技術力を高め、日々のシステム開発に生かしていっています。
今回は「システムの中核を担うデータベース」における設計のアンチパターンを一つご紹介いたします。

テーブル設計のアンチパターンとは

テーブルを設計するにあたり「これは避けるべき」といったパターンをアンチパターンと言います。

今回は、そのパターンの一つである「ジェイウォーク（信号無視）」について、実例を挙げてご紹介したいと思います。

ジェイウォーク（信号無視）とは

『一つの列に、,（カンマ）等で複数の値（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は、以下のようになります。

selectStudents.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は、以下のようになります。

newSelectStudents.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一つで済みます）。

newDeleteShozoku_club.sql

delete from shozoku_club
where id = '00001' and club_id = '001';

長さが項目長に制限されてしまう => 制限されない
例えば、生徒テーブルの項目「所属クラブID」の項目長が12バイトだった場合であっても、所属クラブテーブルにはなんら影響を与えず、また、所属できるクラブ数にも制約は発生しません。

どうしても利用したい場合は？

基本的には避けるべきですが、それでも適用を検討してもよいのは、以下の場合のみです。

テーブルの非正規化が必要な場合
検索が不要な項目の場合

まとめ

他にも多くのアンチパターンがあります。

データベース論理設計のアンチパターン
- ナイーブツリー（素朴な木）
- IDリクワイアド（とりあえずID）
- キーレスエントリ（外部キー嫌い）
- EAV（エンティティ・アトリビュート・バリュー）
- ポリモーフィック関連
- マルチカラムアトリビュート（複数列属性）
- メタデータトリブル（メタデータ大増殖）
データベース物理設計のアンチパターン
- ラウンディングエラー（丸め誤差）
- サーティーワンのフレーバー（31のフレーバー）
- ファントムファイル（幻のファイル）
- インデックスショットガン（闇雲インデックス）

これらアンチパターンを理解し、システムの中核となるデータベース構成を、品質の高く、メンテナンス性が良いシステムとなるように、システム寿命が長くなるようなデータベース設計をできるようになりましょう！

書籍紹介

今回挙げたアンチパターンですが、以下の本にその詳細が載ってます。
テーブルの設計に悩んだら、アンチパターンを避けるためにも、是非目を通してみることをおススメします。
SQLアンチパターン