なぜ設計するのか
1枚の表に全部入れると、どうなるか
このコースで作るのは、多店舗EC「マイショップ・チェーン」のデータベースです。渋谷店・梅田店・博多店といった店舗があり、それぞれが商品を売り、顧客が注文します。
最初に触るのは、その売上を1枚の表に全部入れた sales_flat です。
| id | store_id | store_name | store_pref | product_name | unit_price | quantity | customer_email |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 渋谷店 | 東京都 | ノートPC | 128000 | 1 | akari@example.com |
| 2 | 1 | 渋谷店 | 東京都 | マウス | 3200 | 2 | akari@example.com |
見た目は分かりやすいはずです。1行を見れば、どの店で誰が何を買ったかが全部わかります。表計算ソフトで売上を管理したことがある人には、むしろ自然に見えるでしょう。
同じ事実が、何度も書かれている
問題は、同じ事実が何度も書かれていることです。
渋谷店が東京都にあるという事実は、渋谷店の売上が 100 件あれば 100 回書かれます。1回書けば済むことを 100 回書いている。この状態を冗長と呼びます。
冗長そのものは、置き場所を無駄に使うだけの話に見えます。実際に痛いのはその次です。
書き換えると、事実が割れる
渋谷店が店名を「渋谷本店」に変えたとします。100 行を書き換えなければいけません。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
UPDATE borrowing_flat SET branch_name = '中央図書館' WHERE branch_name = '西館';1回で終わったように見えます。ところが、過去に入力ミスで「渋谷 店」と空白の入った行が1件あったらどうでしょうか。その行は条件に合わないので、書き換わりません。
結果として、店舗 id が 1 なのに店名が2種類あるデータができあがります。どちらが正しいのか、データベースを見ても分かりません。これが更新不整合です。
直せない理由
「気をつけて書き換えればいい」では解決しません。人は間違えるからです。
本当の原因は、同じ事実を複数の場所に置いてしまった構造のほうにあります。店名を1か所にしか書いていなければ、書き換えは1行で済み、割れようがありません。
設計とは、この「1か所にしか書かない」状態を、事故が起きる前に作っておく作業です。
更新と集計を読む小さな例
図書館の貸出記録 borrowings を考えます。UPDATE は表を変更し、SET で新しい値、WHERE で対象を指定します。= は完全に一致する値だけを選びます。
SQL クエリ
UPDATE borrowings SET branch_name = '北図書館' WHERE branch_name = '北館';更新後に同じ名前が何行あるかを見るのが次の集計です。
SQL クエリ
SELECT branch_name, count(*) AS records
FROM borrowings
WHERE branch_id = 7
GROUP BY branch_name
ORDER BY records DESC, branch_name;SELECT は表示する列、FROM は読む表、WHERE は選ぶ行です。GROUP BY は同じ名前の行をまとめ、count(*) は各まとまりの行数を数えます。AS records は結果の列名です。ORDER BY は並び順で、DESC は大きい順です。同数なら2つ目の列で並べます。演習ではこの集計部分を用意してあるので、更新の条件に集中してください。
手を動かす
実際に事故を起こしてみます。読んで納得するより、自分のクエリでデータを割ってしまうほうが早く身につきます。
渋谷店が店名を「渋谷本店」に変えました。store_name が '渋谷店' の行を '渋谷本店' に UPDATE してください。
そのあと、店舗 id が 1 の行について store_id、store_name、その組み合わせの行数を cnt という名前で、行数の多い順に取り出してください。
1つの店舗のはずなのに、店名が何種類出てくるかを見てください。
今回書くのは店名を更新する文だけです。集計はひな形を実行して、表記が異なる行が残ることを観察します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 1 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE sales_flat (
id integer PRIMARY KEY,
store_id integer NOT NULL,
store_name text NOT NULL,
store_pref text NOT NULL,
product_name text NOT NULL,
unit_price integer NOT NULL,
quantity integer NOT NULL,
customer_email text NOT NULL
);
INSERT INTO sales_flat VALUES
(1, 1, '渋谷店', '東京都', 'ノートPC', 128000, 1, 'akari@example.com'),
(2, 1, '渋谷店', '東京都', 'マウス', 3200, 2, 'akari@example.com'),
(3, 1, '渋谷 店', '東京都', 'キーボード', 8900, 1, 'kenji@example.com'),
(4, 2, '梅田店', '大阪府', 'ノートPC', 128000, 1, 'mika@example.com'),
(5, 2, '梅田店', '大阪府', 'モニタ', 24800, 2, 'kenji@example.com'),
(6, 3, '博多店', '福岡県', 'マウス', 3200, 3, 'sora@example.com');
期待される出力
| store_id | store_name | cnt |
|---|---|---|
| 1 | 渋谷本店 | 2 |
| 1 | 渋谷 店 | 1 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い