テーブル設計のアンチパターンシリーズ！「ジェイウォーク（信号無視）」

![図1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2633992/aa38e92d-e7d5-7573-9d1a-975ef26792f6.jpeg)

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

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

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

# ジェイウォーク（信号無視）とは
『 一つの列に、,（カンマ）等で複数の値（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は、以下のようになります。

```sql:selectStudents.sql
select id, name from students
where club_Id REGEXP '[[:<:]]004[[:>:]]'
order by id;
```

#### ２．更新も煩雑になる
例えば、「山田　太郎」さんが、001（野球部）から退部する場合、以下の処理が必要になります。
1. 「山田　太郎」さんの所属クラブIDを取得する
1. 所属クラブIDから、001を探して、取り除く（カンマも考慮する）
1. 取り除いた所属クラブで更新する


#### ３．長さが項目長に制限されてしまう
例えば、生徒テーブル（更新後）の項目「所属クラブID」の項目長が12バイトだった場合、所属クラブは、（カンマを除けば）3つまでしか入りません（所属できません）。
これにより、意図しない制約が発生してしまっています。

#### ４．他にこんなデメリットがあります。

* クエリが煩雑になるため、メンテナンス性、パフォーマンスが悪化する。

* 値の妥当性検証ができない。
無効な値(例：'hoge')でもデータベース側でエラーにならなず、入力できてしまうので値の妥当性検証ができない。

* 区切り文字が不明確になる。
カンマを区切り文字にしている場合では、入力値としてのカンマなのか、区切り文字としてのカンマなのか不明確になる。

# ではどうするか
生徒テーブルを変更して一つの項目に複数の値を許容するのではなく、生徒と所属クラブを紐づけるテーブルを作成します（このようなテーブルの事を、中間テーブル、または交差テーブル、と呼びます）。

所属クラブテーブル
| 生徒ID | 所属クラブID |
|:-------|:-----------|
| 00001  | 001 |
| 00001  | 004 |
| 00002  | 002 |
| 00002  | 004 |
| 00003  | 001 |
| 00003  | 003 |
| 00003  | 005 |
...

このようにすれば、先ほどのデメリットも解消されます。
* クエリが煩雑 => シンプルに
例えば、004（映画研究会）に所属している生徒の一覧を取得する場合のSQLは、以下のようになります。

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

```sql:newDeleteShozoku_club.sql
delete from shozoku_club
where id = '00001' and club_id = '001';
```

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

# どうしても利用したい場合は？
基本的には避けるべきですが、それでも適用を検討してもよいのは、以下の場合のみです。
* テーブルの非正規化が必要な場合
* 検索が不要な項目の場合

# まとめ

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

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

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

# 書籍紹介
今回挙げたアンチパターンですが、以下の本にその詳細が載ってます。
テーブルの設計に悩んだら、アンチパターンを避けるためにも、是非目を通してみることをおススメします。
[SQLアンチパターン](https://www.amazon.co.jp/dp/4873115892/)

https://www.esprit-fort.co.jp/

https://www.esprit-fort.co.jp/recruit/