第3正規形 (3NF)
異動が 1 件出るだけで、過去の注文 3,000 行を書き換える
注文の表に、担当営業の名前と所属部署を持たせました。
SQL クエリ
CREATE TABLE orders_bad (
id INT PRIMARY KEY,
customer_id INT,
sales_rep_id INT,
sales_rep_name VARCHAR(100),
sales_rep_dept VARCHAR(100),
total_amount INT
);4 月に人事異動が出ます。1 人が営業 1 部から営業 2 部へ移りました。その人が過去に担当した注文は 3,000 件あります。
部署別の売上を出すたびに、この 3,000 行の sales_rep_dept がすべて正しいことが前提になります。書き換えを漏らせば、その注文だけ古い部署に計上されます。異動は毎年あり、対象者も毎回変わります。
主キーではない列が、別の列を決めていないか
主キーは id の 1 列なので、主キーの一部だけで決まる列という問題は起きません。それでも重複が出ています。今度は、主キーではない列どうしの関係を見ます。
sales_rep_dept を決めているのは、注文の id でしょうか。違います。決めているのは sales_rep_id です。同じ担当者の注文なら、部署は必ず同じ値になります。
つまり id → sales_rep_id → sales_rep_dept という連鎖があり、id は部署を sales_rep_id 経由で間接的に決めています。これを推移的関数従属と呼びます。決められている側の値は担当者の人数ぶんあれば足りるのに、注文の件数ぶん複製されているわけです。sales_rep_name もまったく同じ立場です。
見つけ方は単純で、列を 1 本ずつ取り上げ「この値を決めているのはどの列か」と聞きます。答えが主キー以外の列になったら、そこが切り出す場所です。
決めている側を、独立した表にする
sales_rep_id が決めている列は、sales_rep_id が主キーになる表へ移します。
SQL クエリ
CREATE TABLE sales_reps (
id INT PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
sales_rep_id INT,
total_amount INT
);異動の反映は sales_reps の 1 行更新で終わります。orders は 1 行も触りません。部署別の売上は、担当者の表を見に行く形で計算されるので、いつ集計しても最新の所属で揃います。
第 3 正規形とは、主キー以外の列によって決まる列が残っていない状態のことです。この先にはボイスコッド正規形や第 4 正規形もありますが、業務システムでは第 3 正規形まで到達していれば十分な場面がほとんどです。
テーブル構造
CREATE TABLE sales_reps (
id INT PRIMARY KEY,
name VARCHAR(100),
dept VARCHAR(100)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
sales_rep_id INT,
total_amount INT
);
INSERT INTO sales_reps VALUES
(1, '田中', '営業1部'),
(2, '佐藤', '営業1部'),
(3, '鈴木', '営業2部'),
(4, '高橋', '営業3部');
INSERT INTO orders VALUES
(1001, 1, 1, 5000),
(1002, 2, 1, 8000),
(1003, 3, 2, 3000),
(1004, 1, 3, 12000),
(1005, 4, 3, 7000),
(1006, 2, 4, 4000),
(1007, 3, 1, 6000);期待される出力
| dept | dept_sales |
|---|---|
| 営業1部 | 22000 |
| 営業2部 | 19000 |
| 営業3部 | 4000 |