1章 Jaywalking(信号無視)

## バグ管理アプリケーション

製品毎の担当者(ユーザー)を登録する機能を追加

 1. ユーザーは一人のみ登録できればよかった
 2. 複数人登録できるよう改修
  3. 列にカンマ区切りでユーザーを格納

カラム文字数制限にひかかり、カンマ区切りのリストに保持できる連絡先に限界が発生

> 開発者はよく、「多対多」の関連を表現する交差テーブルの作成を避けるために、カンマ区切りのリストを使います。
> 私はこのアンチパターンをJaywalkingと名づけました。どちらも、"intersection"を避けようとする行為だからです。

# 1.1 目的: 複数の値を持つ属性を格納する

```sql:Jaywalking/obj/create.sql
REATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(1000),
account_id BIGINT UNSIGNED,
-- 他の列 . . .
FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', 12);
);
```

製品`Products`からアカウント`account_id`に対する1対多の関連もサポートする必要がでてきた

# 1.2 アンチパターン: カンマ区切りフォーマットのリストを格納する

```sql:Jaywalking/anti/create.sql
CREATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(1000),
account_id VARCHAR(100), -- カンマ区切りのリスト -- 他の列 . . .
);
INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', '12,34');
```

> テーブルや列を新たに追加しなくても済んだので、うまくいっているように思えます。
> 変更したのは、1つの列のデータ型のみです。

しかしながら、要件が変化すると対応できなくなってくる…

## 1.2.1 特定のアカウントに関連する製品の検索

account_id=12が担当している製品をすべて取得したい

```sql:Jaywalking/anti/regexp.sql
SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]';
```

* インデックスがきかない
* 構文によってベンダーロックされる

## 1.2.2 特定の製品に関連するアカウントの検索

```sql:Jaywalking/anti/regexp.sql
SELECT * FROM Products AS p INNER JOIN Accounts AS a
ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE p.product_id = 123;
```

<dl>
<dt><a href="http://makopi23.blog.fc2.com/blog-entry-65.html">http://makopi23.blog.fc2.com/blog-entry-65.html</a></dt>
<dd>WHERE句のp.product_id列は主キーなので、インデックスが自動で貼られているはず。
そうなると、Productsテーブルは主キー検索で1件に絞られるはず。
だとすると、ON句では1対Nのスキャンになるはず。</dd>
</dl>

原著のミス!!

`ON`で`REGEXP`使うとか正気の沙汰じゃない…(そもそもこんな構文でSELECTできること自体知らない)

## 1.2.3 集約クエリの作成

製品毎に登録されているユーザ数

```sql:Jaywalking/anti/count.sql
SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS contacts_per_product
FROM Products;
```

なにをやりたいのかぱっと見よくわからん…

## 1.2.4 特定の製品に関連するアカウントの更新

リストへの追加・削除

```sql:Jaywalking/anti/update.sql
UPDATE Products
SET account_id = account_id || ',' || 56 WHERE product_id = 123;
```

```php:Jaywalking/anti/remove.php
<?php
$stmt = $pdo->query(
"SELECT account_id FROM Products WHERE product_id = 123");
$row = $stmt->fetch();
$contact_list = $row['account_id'];

// PHP コードでの list の変更
$value_to_remove = "34";
$contact_list = split(",", $contact_list);
$key_to_remove = array_search($value_to_remove, $contact_list);
unset($contact_list[$key_to_remove]);
$contact_list = join(",", $contact_list);

$stmt = $pdo->prepare(
"UPDATE Products SET account_id = ?
WHERE product_id = 123");
$stmt->execute(array($contact_list));
```

できないことないけど、勘弁してほしい…リストが増えれば増えるほど計算量が増えるし

## 1.2.5 製品IDの妥当性検証

たぶん、 __アカウントID__ の妥当性と思う。。。

> ユーザーが “banana” のような無効な入力を行ってしまうことを、どうやったら防げるでしょうか。

```sql:Jaywalking/anti/banana.sql
INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder', '12,34,banana');
```

アプリでvalidationすると思うけど、Schemaレベルでも弾けた方が安心だよねー

## 1.2.6 区切り文字の選択

`account_id`のような整数値のリストではなく、文字列値のリストを格納する場合は、区切り文字のエスケープ考えないといけないし大変。

アプリでCSVライブラリ使えばうまくやってくれるけど、手でSQL実行することもあるしねー

## 1.2.7 リストの長さの制限

カラムのデータ長によって登録できるエントリ数に制限が発生してしまう…＞＜

```sql:Jaywalking/anti/length.sql
UPDATE Products SET account_id = '10,14,18,22,26,30,34,38,42,46' WHERE product_id = 123;
UPDATE Products SET account_id = '101418,222630,343842,467790' WHERE product_id = 123;
```

データ長どれだけ確保すればよいか判断不可能。
MySQLだと、データ長越えた分は有無をいわさず、チョン切るから質が悪い…

# 1.3 アンチパターンの見つけ方

* このリストでサポートしなくてはならない最大のエントリ数は?
 * VARCHARの最大長を気にしている
* SQLで単語境界を一致させる方法を知ってる?
* リストのエントリに絶対使われない文字って何だっけ?
 * 区切り文字を決めようとしている

# 1.4 アンチパターンを用いてもよい場合

リストをカンマ区切りの文字列として格納することは、非正規化の一例

> アプリケーションによっては、カンマ区切りフォーマットのデータが必要で、かつリスト内の各要素への 個別アクセスが不要な場合があります。カンマ区切りを用いていれば、他の場所からカンマ区切り形式のデータを受け取った場合に、そのリストをそのまま列に格納でき、その後もそのまま取得できます。値をバラバラにする必要はありません。

まずは正規化された構造を十分に検討してから、非正規化を考える

## 考察

これだけだといまいちなんで、もうちょっと考えてみる。
履歴系のテーブルなら、追加・削除とかないんで非正規化もありかも。

例えば、メール送信履歴テーブルがあったとして、送信先をカンマ区切りのリストでもつとか。
正規化して、account_idとのマッピングもつとしても、accountテーブルのもつメアドが更新されるようなことになると、正確な送信履歴をもてなくなるので、スナップショットとしてもちなくなったり。(でもメアドなんでencryptしないといけないってことも…)

# 1.5 解決策: 交差テーブルを作成する

`Contacts`テーブルを作って、`Products`と`Accounts`に多対多の関係を作る

```sql:Jaywalking/soln/create.sql
CREATE TABLE Contacts (
  product_id BIGINT UNSIGNED NOT NULL,
  account_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id),
  FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
INSERT INTO Contacts (product_id, account_id)
VALUES (123, 12), (123, 34), (345, 23), (567, 12), (567, 34);
```

![交差テーブルのER図](https://qiita-image-store.s3.amazonaws.com/0/1847/730f60a4-f8e2-7dc5-1a95-0b7bc05818e5.png)

## 1.5.1 特定のアカウントに関連する製品の検索/特定の製品に関連するアカウントの検索

```sql:￼￼Jaywalking/soln/join.sql
SELECT p.*
FROM Products AS p INNER JOIN Contacts AS c ON p.product_id = c.product_id WHERE c.account_id = 34;

SELECT a.*
FROM Accounts AS a INNER JOIN Contacts AS c ON a.account_id = c.account_id WHERE c.product_id = 123;
```

## 1.5.2 集約クエリの作成

```sql:Jaywalking/soln/group.sql
SELECT product_id, COUNT(*) AS accounts_per_product FROM Contacts
GROUP BY product_id;

SELECT account_id, COUNT(*) AS products_per_account FROM Contacts
GROUP BY account_id;

SELECT c.product_id, c.accounts_per_product FROM (
  SELECT product_id, COUNT(*) AS accounts_per_product FROM Contacts
  GROUP BY product_id
) AS c
HAVING c.accounts_per_product = MAX(c.accounts_per_product)
```

## 1.5.3 製品の連絡先の更新

```sql:Jaywalking/soln/remove.sql
INSERT INTO Contacts (product_id, account_id) VALUES (456, 34);
DELETE FROM Contacts WHERE product_id = 456 AND account_id = 34;
```

## 1.5.4 製品IDの妥当性検証

__外部キー制約__ によって存在しないIDを格納できなくなる

> SQLのデータ型によって入力内容を制限できます。例えば、リストの各要素の有効値を整数値や日付にしたい場合、列に対してINTEGERやDATEといったデータ型を宣言することで、すべてのエントリがこれらのデータ型の値であることを保証できます。

## l.5.5 区切り文字の選択

区切り文字は不要!

## 1.5.6 リストの長さの制限

* 制限としては、交差テーブルに物理的に格納できる行数のみ
* 関連するエントリ数に上限を設けたい場合は、エントリ数をCOUNTで制限するようアプリで対応する

## 1.5.7 交差テーブルの他のメリット

> Contacts.account_id上のインデックスを用いることで、カンマ区切りリストの部分文字列とマッチさせる方法よりも、パフォーマンスが向上します。多くのデータベース製品では、列を外部キーとして宣言することで、その列に暗黙のうちにインデックスが作成されます(ただし、念のため各データベース製品のドキュメントを確認してください)。

* Contactsテーブルのproduct_id列には外部キーを付与しているため、MySQLだと自動的にインデックスが貼られる
* Postgresqlだと外部キーを設定しても自動でインデックスは貼られない

交差テーブルに属性を追加できる

* 連絡先を追加した日付
* 連絡先の優先順位