第3正規形 (3NF)
第3正規形 とは
第3正規形について解説します。効率的なデータベース設計の基礎を学びます。本レッスンでは、第3正規形 の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。
3NF の役割
第3正規形 (Third Normal Form, 3NF) は、推移的関数従属 を取り除く段階です。2NF まで進んでも、非キー列がさらに別の非キー列を決めるような関係が残っていると、マスタ性の高い情報が業務テーブルに混じってしまいます。3NF を満たすと「主キー以外の列は、主キー以外には依存しない」状態になり、各テーブルが持つ責務が明確になります。
推移的関数従属
A → B、B → C という連鎖があるとき、A → C が 推移的関数従属 (transitive dependency) です。A (主キー) と C の間に B という非キー列が割り込んでいるため、C は A に直接ではなく B 経由で決まっています。B の側に C をまとめておけば情報の重複は消えます。
3NF 違反のテーブル
注文テーブルに「担当営業の所属部署」を直接持たせている例です。
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
);関数従属は id → sales_rep_id → sales_rep_name、id → sales_rep_id → sales_rep_dept という推移関係です。担当者が部署異動するたびに、その人が担当した過去の注文すべてを更新しなければなりません。
3NF 化の流れ
3NF 化したスキーマ
営業担当を sales_reps マスタ (master) に分離し、orders には 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 を一切触らなくても全注文の集計に正しく反映されます。
集計クエリ
部署ごとの売上合計を見たくなったら、JOIN すれば計算できます。
SQL クエリ
SELECT r.dept, SUM(o.total_amount) AS dept_sales
FROM orders o
JOIN sales_reps r ON r.id = o.sales_rep_id
GROUP BY r.dept
ORDER BY dept_sales DESC;履歴を残したい場合の注意
注意 3NF は「現在の事実」を表現する正規形です。注文時点の担当部署を履歴として保持したい場合は、わざと
orders.sales_rep_dept_at_order_timeのような列を残すこともあります。これは性能や運用上の理由で 3NF を一部崩す非正規化の判断であり、次のレッスンで扱います。
3NF の判定は「非キー列が別の非キー列を決めていないか」を順に見ていくことに尽きます。テーブルの列を眺め、
A → B → Cの連鎖を 1 つでも見つけたら、B 側に C を移すと考えればよいです。
3NF は実務でゴールに据えられることが多い正規形です。これ以上の BCNF / 4NF / 5NF は理論的な完成度を上げますが、業務システムでは 3NF が達成できていれば十分なケースがほとんどです。
3NF を満たさないと何が困るか
3NF 違反のテーブルでは、マスタ性の高い情報 (担当者名、部署名、住所など) が業務テーブルに散らばります。sales_reps_bad のメンテナンスは「担当者異動 → 過去の orders を一括更新」というオペレーションが必要になり、しかも更新範囲は数千行・数万行になり得ます。結果として、不整合の確率が時間とともに上がっていきます。
非キー列同士の依存を見抜くコツ
3NF 違反は、テーブル定義を眺めるだけだと見落としがちです。次のような視点で各列を点検すると見つけやすくなります。
- この列の値を変えるのに、他のどの列の値を見れば良いか
- その列は主キーか、それとも別の非キーか
- もし別の非キーだとすれば、それが別マスタの
候補キーではないか
3NF とビジネス要件のバランス
設計の現場では「人事マスタを共有マスタにすべきか、注文側に最低限の情報だけコピーすべきか」という議論がよく起こります。3NF が示すのは理論上のゴールで、実際にはデータの所有権・保守責任・性能を勘案して妥協点を決めます。
SQL クエリ
-- 共有マスタ前提なら 3NF
SELECT r.dept FROM sales_reps r WHERE r.id = ?;
-- もし sales_reps が外部システム所有なら、参照のために最低限の列だけコピーする選択肢もある
SELECT dept_name_snapshot FROM orders WHERE id = ?;まとめ
- 3NF は推移的関数従属を排除する
- 非キー列が別の非キー列を決めている構造をマスタテーブルに切り出す
- 1NF → 2NF → 3NF と順番に進めるのが基本
- 履歴目的の冗長は意図的な非正規化として別途扱う
次のレッスン
次は 非正規化の判断 です。非正規化のメリット・デメリットを理解し、適切な判断ができるようにSQLで考えます。
事前確認 — 進む前に次の 3 つができることを確認しましょう。
- 3NF の要点を自分の言葉で説明できる
- このレッスンの最小コード (または操作手順) を見ずに書ける
- 練習問題やクイズで間違えた箇所を読み直して理解した
理解度チェック (30 秒)
Q. 3NF とは何か、1 文で説明してください。
A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。
関連レッスン
テーブル構造
schema.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
);
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 |