Edited at

1章 Jaywalking(信号無視)


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

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


  1. ユーザーは一人のみ登録できればよかった

  2. 複数人登録できるよう改修


    1. 列にカンマ区切りでユーザーを格納



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


開発者はよく、「多対多」の関連を表現する交差テーブルの作成を避けるために、カンマ区切りのリストを使います。

私はこのアンチパターンを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のスキャンになるはず。

原著のミス!!

ONREGEXP使うとか正気の沙汰じゃない…(そもそもこんな構文で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テーブルを作って、ProductsAccountsに多対多の関係を作る


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

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


  • 連絡先を追加した日付

  • 連絡先の優先順位