第3正規形
キー以外の列で決まる列
最後が第3正規形、3NF です。条件はこうです。
主キー以外の列で決まる列を、テーブルに置かない。
2NF は「主キーの一部で決まる列」を追い出しました。3NF は「そもそも主キーと関係ない列で決まる列」を追い出します。
店舗テーブルを見る
地域別の集計をしたい、という要件が来て region を足したとします。
| id | name | pref | region |
|---|---|---|---|
| 1 | 渋谷店 | 東京都 | 関東 |
| 2 | 新宿店 | 東京都 | 関東 |
| 3 | 梅田店 | 大阪府 | 関西 |
列ごとに「何で決まるか」を聞きます。
nameはidで決まる → 主キーで決まる。問題なしprefはidで決まる → 主キーで決まる。問題なしregionはprefで決まる → 主キーではなく、別の列で決まっている
東京都なら必ず関東です。店舗が何であろうと関係ありません。この「主キー → pref → region」と2段階で決まっている状態を推移的関数従属と呼びます。
何が起きるか
東京都の店舗が50店あれば、「東京都は関東」という事実が50回書かれます。またしても同じ構造です。
そして、ここには2NF のときには無かった嫌らしさがあります。間違った組み合わせを書けてしまうことです。
| 4 | 池袋店 | 東京都 | 関西 |
この行を止めるものは何もありません。データベースは pref と region の関係を知らないからです。人が入力する限り、いつか必ずこの行が生まれます。
直し方
決める側の列を主キーとする別のテーブルに移します。
region は pref で決まるので、pref を主キーとする prefectures テーブルを作って移します。
| prefectures | stores | ||||
|---|---|---|---|---|---|
| pref | region | id | name | pref | |
| 東京都 | 関東 | 1 | 渋谷店 | 東京都 | |
| 大阪府 | 関西 | 2 | 新宿店 | 東京都 |
こうなると「東京都は関東」は1行しかありません。池袋店を関西にすることは、構造的に不可能になります。
3つの正規形をまとめる
| 正規形 | 追い出すもの | 合言葉 |
|---|---|---|
| 1NF | 1マスの中の複数値 | 繰り返しは行にする |
| 2NF | 主キーの一部で決まる列 | 部分関数従属を消す |
| 3NF | 主キー以外で決まる列 | 推移的関数従属を消す |
実務では、この3つをまとめて 「すべての列は、キーによって、キー以外の何ものにもよらず決まる」 と覚えられています。3NF まで来れば、日常の設計はほぼ足ります。
手を動かす
店舗テーブルから地域を分離します。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL,
pref text NOT NULL,
region text NOT NULL
);
INSERT INTO stores VALUES
(1, '渋谷店', '東京都', '関東'),
(2, '新宿店', '東京都', '関東'),
(3, '梅田店', '大阪府', '関西'),
(4, '博多店', '福岡県', '九州');
期待される出力
| id | name | pref | region |
|---|---|---|---|
| 1 | 渋谷店 | 東京都 | 関東 |
| 2 | 新宿店 | 東京都 | 関東 |
| 3 | 梅田店 | 大阪府 | 関西 |
| 4 | 博多店 | 福岡県 | 九州 |