【SQL】アンチパターン１章：ジェイウォーク（信号無視）

概要

自分がわかるように解説記事を書いていきます！間違いがあれば遠慮なく指摘ください

目的：複数の値を持つ属性値を保存したい

• １対１で紐付くProductテーブルとAccountテーブルがあったとします。
• そこで追加で依頼が来ました。「プロダクトに登録社員を追加したい」

そこで、「カンマ区切りでとりあえず文字列で保存するか...」としました。

アンチパターン: 区切り文字でリストを保存する

ジェイウォークjaywalkとは、交通規則を無視して道路を横断するという意味

ツッコミどころ

どのアカウントがどのプロダクト持ってるの？

SELECT文で検索することが難しいですよね

Accountテーブルと結合したいのですが

inner joinを書けないですよね

集約クエリかけますか？

COUNT, SUM, AVGどうやって書きましょう

データ更新どうしましょう

select文でデータを取得したのちに、updateでそれを更新するんですか・・・？

バリデーションどうしますか？

文字列で良いなら、bananaとか入っちゃいません？

区切り文字どうしますか？

スペース、カンマ、セミコロン・・・

登録限界数は・・・？

文字列の定義以内です・・・？

解決策：交差テーブルを作ろう！

どのアカウントがどのプロダクト持ってるの？

SELECT p.product_id, p.product_name
FROM Product As p 
    INNER JOIN Account_Product as ap 
        ON p.product_id = ap.product_id
WHERE ap.account_id = 2;

集約クエリ

製品ごとのアカウント数を返す

SELECT ap.product_id, COUNT(*) AS accounts_per_product
FROM Account_Product AS ap
GROUP BY product_id;

アカウント毎の製品数

SELECT ap.account_id, COUNT(*) AS products_per_account
FROM Account_Product AS ap
GROUP BY account_id;

最も関連アカウント数の多い製品

SELECT c.product_id, c.accounts_per_product
FROM(
    SELECT ap.product_id, COUNT(*) AS accounts_per_product
    FROM Account_Product AS ap
    GROUP BY product_id;
) AS c
HAVING c.accounts_per_product = MAX(c.accounts_per_product)

製品のアカウント追加

INSERT INTO Product_Account (
    product_id, account_id
) VALUES (
    456, 34
);

DELETE FROM Product_Account 
WHERE product_id = 456
    AND account_id = 34;

• MysqlならCASCADEを設定すれば交差テーブルのデータは自動的に削除することができます

バリデーションどうしますか？

• varcharでなく、intなどを使えば不正な文字は入れることができないです
• 外部参照を入れることで不正なデータを入れることができなくなります。

区切り文字？

使ってないです！

リストの長さ制限？

リストは存在しません！

最後に

• 明らかにまずい設計だということはわかると思います
• ただ、ここまでツッコミを入れることができるか？というと自信がないです
• ツッコミ力を養いたい