『SQLアンチパターン』　ジェイウォーク

# はじめに
最近1年ぶりくらいに *Bill Karwin* さん著の『**SQLアンチパターン**』を読み返しています。
改めて学ぶことがたくさんありました。

せっかくなので学んだことを一つずつ記事にして書き記しておこうと思い、今回この記事を書きました。
初回は第1章の「**ジェイウォーク**」です。

# ジェイウォークとは
ジェイウォークとは、複数の値を持つカラムを格納する際に、新たにテーブルを作らずに既存のテーブルのカラムにカンマ(,)区切りなどで複数の値を格納することを指します。

本書では、多対多の関係を持つ*products*テーブルと*accounts*テーブルを関連づけるためのカラムを例に出しています。
テーブルの内容はこんな感じ。
（※ 本書とは異なる部分あり）

```
mysql> DESC accounts;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| name  | text | NO   |     | NULL    |                |
+-------+------+------+-----+---------+----------------+

mysql> SELECT * FROM accounts;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
+----+-------+

mysql> DESC products;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| name       | varchar(1000) | NO   |     | NULL    |                |
| account_id | varchar(100)  | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+

mysql> SELECT * FROM products;
+----+-------+------------+
| id | name  | account_id |
+----+-------+------------+
|  1 | apple | 1          |
|  2 | book  | 1,2        |
+----+-------+------------+
```

最初は一つの*Product*に対して一つの*Account*しか関連づけていなかったものを、後から仕様が変わって一つの*Product*に複数の*Account*が関連づけられるようになった場合に、間違ってこういうスキーマにしてしまうことがあるそうです。（マジかいな）

# 問題点
アンチパターンというからには問題点があります。しかも結構たくさん...
一つずつ説明していきましょう。

## 特定の*Account*に関連する*Product*の検索
例えば`id = 1`の*Account*に関連する全ての*Product*を取得する場合、クエリでパターンマッチを使ったり、アプリケーション側で "," で分割したりする必要が出てきます。
これではインデックス使用することもできずクエリのパフォーマンスも良くないと考えられますし、正規表現を考えたりする手間を増えるので、好ましくありません。

ちなみに特定の*Product*に関連する*Accont*の検索も同様の理由でやりづらいです。

## 集約クエリの作成
例えば*Product*ごとに関連する*Account*の数を取得したい場合、以下のようなクエリを発行する必要があります。

```
SELECT id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS account_per_product FROM products;
```

「`account_id`カラムの "," の数＋1」がアカウントの数になっているためですね。

しかし、これでは直感的に分かりづらくなってしまいます。また、工夫しても実現が難しい集約クエリがあるかもしれません。

## 特定の*Product*に関連する*Account*の更新
例えば `id = 1` の*Product*から `id = 2` の*Account*を削除したい場合、以下のような手順が必要です。

1. `id = 1` の*Product*のレコードの`account_id`カラムを取得する
1. 取得した`account_id`を "," で分割する。
1. 分割した中から "2" の要素を除外する。
1. 除外し終わったリストを "," で再結合する。
1. `id = 1` の*Product*のレコードを、4. で作った文字列で更新する。

クエリを2回発行しなきゃいけないし、アプリケーション側で処理を書かなきゃいけない。非常に面倒くさいですね。

## Account IDの妥当性検証
*products*テーブルの`account_id`カラムは制約のないVARCHAR型なので、そこに "banana" のような無効な値が入ってしまうことを防ぐことが難しいです。（著者はバナナが嫌いなんでしょうか？笑）

## 区切り文字の選択
もし整数値のリストではなく文字列のリストを格納する場合、その文字列のリストに "," が含まれていたら区切り文字として "," を使うことはできません。これは "," に限らず、あらゆる文字列はデータとして入力される可能性がないとは言い切れないので（ケースによりますが）、区切り文字として使っていた文字を入力文字の中に使いたくなった場合、非常に困ることになります。

## リストの長さの制限
`account_id`カラムがVARCHAR型の場合、格納できる*Account*のidの数には限界があります。
また、その限界はリストの各エントリの長さ（整数のidであれば桁数）によって異なってしまいます。

将来必要になるリストの長さを推測することは難しいと著者は語っています。

# 解決策
こんな問題ばかりのジェイウォークですが、どのようにすれば解決できるのでしょうか？

答えは簡単で、交差テーブルを作成すれば良いです。
具体的には、以下のような*contacts*テーブルを作ります。

```
mysql> DESC contacts;
+------------+------+------+-----+---------+-------+
| Field      | Type | Null | Key | Default | Extra |
+------------+------+------+-----+---------+-------+
| account_id | int  | NO   | PRI | NULL    |       |
| product_id | int  | NO   | PRI | NULL    |       |
+------------+------+------+-----+---------+-------+
```
そして、*accounts*テーブルと*products*テーブルをこの*contacts*テーブルを通して関連づけるようにします。

```
mysql> DESC accounts;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| name  | text | NO   |     | NULL    |                |
+-------+------+------+-----+---------+----------------+

mysql> DESC products;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| name       | varchar(1000) | NO   |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+
```

*contacts*テーブルの`account_id`と`product_id`は、それぞれ*accounts*テーブルの`id`と*products*テーブルの`id`を参照する外部キーになっています。

```
mysql> show create table contacts;
+----------+-----------------------------------------------------------------------------------+
| Table    | Create Table |
+----------+-----------------------------------------------------------------------------------+
| contacts | CREATE TABLE `contacts` (
  `account_id` int NOT NULL,
  `product_id` int NOT NULL,
  PRIMARY KEY (`account_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  CONSTRAINT `contacts_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------+
```

ちなみに、ジェイウォーク（信号無視）というパターン名の由来が、交差点（交差テーブルを避ける）というところから来ているらしいです。

それでは、先ほどあげた問題が解決されているか確認していきましょう。

## 特定のAccountに関連するProductの検索
以下のようなクエリで、特定の*Account*に関連する*Product*の一覧を取得できます。

```
mysql> SELECT p.* FROM products AS p INNER JOIN contacts AS c ON p.id = c.product_id WHERE c.account_id = 2;
+----+------+
| id | name |
+----+------+
|  2 | book |
+----+------+

```


##集約クエリの作成
*Product*ごとの紐づいている*Account*の数が以下のクエリで取得できます。

```
mysql> SELECT product_id, COUNT(*) AS accounts_per_product FROM contacts GROUP BY product_id;
+------------+----------------------+
| product_id | accounts_per_product |
+------------+----------------------+
|          1 |                    1 |
|          2 |                    2 |
+------------+----------------------+
```
## 特定のProductに関連するAccountの更新
`id = 1` のProductから `id = 2` のAccountを削除したい場合、*contacts*テーブルのレコードを削除するだけで対応できます。

## Account IDの妥当性検証
*contacts*テーブルの各カラムが外部キーになっているので、存在する*Account*や*Product*の`id`しか入らないようになっています。

## 区切り文字の選択
区切り文字については考える必要がなくなりました。

## リストの長さの制限
*Product*と*Account*の関連の追加は*contacts*テーブルのレコードの追加で対応できるので、リストの長さについては考えなくて良くなりました。

# 補足
交差テーブルを用いることで、上記で挙げた以外にも以下のようなメリットがあります。
- 交差テーブルの属性を追加することで、*Product*と*Account*が関連づけられた日時を記録したり、連絡先の優先順位をつけたりできる。

```
mysql> DESC contacts;
+------------+-------------------+------+-----+-------------------+-------------------+
| Field      | Type              | Null | Key | Default           | Extra             |
+------------+-------------------+------+-----+-------------------+-------------------+
| account_id | int               | NO   | PRI | NULL              |                   |
| product_id | int               | NO   | PRI | NULL              |                   |
| created_at | timestamp         | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| priority   | smallint unsigned | YES  |     | NULL              |                   |
+------------+-------------------+------+-----+-------------------+-------------------+
```