なぜ正規化が必要か
社名を変えたのに、1 件だけ旧社名の請求書が出た
取引先が社名を変えました。データベースには、その会社の名前が過去の注文 200 行に書き込まれています。全部を書き換えるつもりで UPDATE を流しました。
半年後、請求書が 1 件だけ旧社名で出ます。条件の書き方をひとつ間違えて、3 行が範囲から外れていたのでした。
SQL クエリ
CREATE TABLE orders_flat (
order_id INT,
customer_id INT,
customer_name VARCHAR(100),
customer_email VARCHAR(255),
product_id INT,
product_name VARCHAR(100),
unit_price INT,
quantity INT
);責めるべきは UPDATE の書き間違いではありません。1 つの事実が 200 か所に書いてあることです。書く場所が 200 あれば、いつか必ずどれかが漏れます。漏れた瞬間、その会社の正しい名前はデータベースの中から失われます。
消えるのと、入らないのも同じ原因から起きる
1 枚に詰め込んだ表では、ほかに 2 つのことが起きます。
まだ 1 件も注文していない取引先を登録できません。この表の 1 行は注文 1 件を表すので、注文が無い会社には置く行がありません。空の注文行を作って埋めれば登録はできますが、今度は注文件数の集計がずれます。
そして、ある取引先の最後の注文を取り消すと、その会社の名前もメールアドレスも一緒に消えます。注文と取引先は本来別の事実なのに、同じ行に同居させたせいで運命を共にします。
順に、更新異常、挿入異常、削除異常と呼びます。3 つとも、原因は「同じ事実を何行にも書いた」の一点です。
事実の置き場所を 1 か所に決める
会社の情報は会社の表へ、商品の情報は商品の表へ移し、明細の表には「その注文で何をいくつ買ったか」だけを残します。
SQL クエリ
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255)
);
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
unit_price INT
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);社名を変えるときに書き換える行は 1 行です。書く場所が 1 か所しかないので、漏らしようがありません。読むときは JOIN で組み立て直す手間が増えますが、組み立てた結果は常に、その時点で唯一正しい社名です。
正規化とは、1 つの事実が 1 か所だけに存在する形へ表を分けていく手続きのことです。
テーブル構造
CREATE TABLE orders_flat (
order_id INT,
customer_id INT,
customer_name VARCHAR(100),
customer_email VARCHAR(255),
product_id INT,
product_name VARCHAR(100),
unit_price INT,
quantity INT
);
INSERT INTO orders_flat VALUES
(1001, 1, '田中', 'tanaka@example.com', 10, 'ノート', 300, 2),
(1001, 1, '田中', 'tanaka@example.com', 11, 'ペン', 150, 4),
(1002, 2, '佐藤', 'sato@example.com', 10, 'ノート', 300, 1),
(1003, 1, '田中', 'tanaka_NEW@example.com', 12, '消しゴム', 100, 3),
(1004, 3, '鈴木', 'suzuki@example.com', 11, 'ペン', 150, 5);期待される出力
| customer_id | distinct_email_count |
|---|---|
| 1 | 2 |