第2正規形
複合キーの一部だけで決まる列
1NF になったら次は第2正規形、2NF です。条件はこうです。
主キー全体で決まらない列を、テーブルに置かない。
これが問題になるのは、主キーが複合キーのときだけです。主キーが1列なら、その1列で決まらない列というものが存在しないので、1NF ならそのまま 2NF です。
注文明細を見る
注文明細の主キーは (order_id, product_id) の複合キーでした。この表に商品名を入れてしまった状態を考えます。
| order_id | product_id | product_name | quantity |
|---|---|---|---|
| 1 | 1 | ノートPC | 1 |
| 1 | 2 | マウス | 2 |
| 2 | 1 | ノートPC | 3 |
列ごとに「何で決まるか」を聞いてみます。
quantityは、どの注文のどの商品かが決まらないと決まらない → 主キー全体で決まるproduct_nameは、product_idだけで決まる → 主キーの一部で決まる
この「主キーの一部だけで決まっている」状態を部分関数従属と呼びます。2NF はこれを禁止します。
何が起きるか
部分関数従属を放置すると、product_id = 1 の商品名が明細の件数だけ書かれます。ノートPCが100回売れたら、100回「ノートPC」と書かれます。
これは第1章で見た sales_flat と同じ構造です。商品名を変えるときに全部書き換える必要があり、書き換え漏れが不整合になります。
直し方
主キーの一部だけで決まる列を、その一部を主キーとする別のテーブルに移します。
product_name は product_id だけで決まるので、product_id を主キーとする products テーブルに移します。明細には product_id だけを残します。
| products | order_items | ||||
|---|---|---|---|---|---|
| id | name | order_id | product_id | quantity | |
| 1 | ノートPC | 1 | 1 | 1 | |
| 2 | マウス | 1 | 2 | 2 |
商品名は1か所にしかありません。100回売れても書かれるのは1回です。
見つけ方の手順
実務では、次の順で見ます。
- 主キーが複合キーのテーブルを探す
- その表の主キー以外の列を1つずつ取り上げる
- 「この列は、主キーの一部だけで決まらないか」と聞く
- 決まるなら、その一部を主キーとする表に移す
単価はどうでしょうか。 unit_price は「注文したときの値段」なので、商品が同じでも注文が違えば変わり得ます。だから主キー全体で決まり、明細に残して正解です。同じ列名でも意味が違えば判定が変わるので、名前ではなく意味で判断します。
手を動かす
注文明細から商品名を分離します。
テーブル構造
CREATE TABLE order_items_flat (
order_id integer NOT NULL,
product_id integer NOT NULL,
product_name text NOT NULL,
quantity integer NOT NULL,
unit_price integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO order_items_flat VALUES
(1, 1, 'ノートPC', 1, 128000),
(1, 2, 'マウス', 2, 3200),
(2, 1, 'ノートPC', 3, 128000),
(2, 3, 'キーボード', 1, 8900),
(3, 2, 'マウス', 1, 2980);
期待される出力
| id | name |
|---|---|
| 1 | ノートPC |
| 2 | マウス |
| 3 | キーボード |