1章 Jaywalking(信号無視)

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

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

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

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

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

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

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 アンチパターン: カンマ区切りフォーマットのリストを格納する

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が担当している製品をすべて取得したい

Jaywalking/anti/regexp.sql

SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]';

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

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

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;

http://makopi23.blog.fc2.com/blog-entry-65.html

WHERE句のp.product_id列は主キーなので、インデックスが自動で貼られているはず。 そうなると、Productsテーブルは主キー検索で1件に絞られるはず。 だとすると、ON句では1対Nのスキャンになるはず。

原著のミス!!

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

1.2.3 集約クエリの作成

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

Jaywalking/anti/count.sql

SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS contacts_per_product
FROM Products;

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

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

リストへの追加・削除

Jaywalking/anti/update.sql

UPDATE Products
SET account_id = account_id || ',' || 56 WHERE product_id = 123;

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” のような無効な入力を行ってしまうことを、どうやったら防げるでしょうか。

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 リストの長さの制限

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

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に多対多の関係を作る

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);

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

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 集約クエリの作成

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 製品の連絡先の更新

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だと外部キーを設定しても自動でインデックスは貼られない

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

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