なぜ設計するのか
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 行を書き換えなければいけません。
UPDATE sales_flat SET store_name = '渋谷本店' WHERE store_name = '渋谷店';1回で終わったように見えます。ところが、過去に入力ミスで「渋谷 店」と空白の入った行が1件あったらどうでしょうか。その行は条件に合わないので、書き換わりません。
結果として、店舗 id が 1 なのに店名が2種類あるデータができあがります。どちらが正しいのか、データベースを見ても分かりません。これが更新不整合です。
直せない理由
「気をつけて書き換えればいい」では解決しません。人は間違えるからです。
本当の原因は、同じ事実を複数の場所に置いてしまった構造のほうにあります。店名を1か所にしか書いていなければ、書き換えは1行で済み、割れようがありません。
設計とは、この「1か所にしか書かない」状態を、事故が起きる前に作っておく作業です。
手を動かす
実際に事故を起こしてみます。読んで納得するより、自分のクエリでデータを割ってしまうほうが早く身につきます。
テーブル構造
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 |