第6章 正規化とテーブル設計の実践

第2章でER図と「表を分ける」発想に触れ、第3章で company.db のテーブルを作りました。あのとき部署を departments テーブルに分けたのは、実はこの章で学ぶ正規化を先取りしたものです。この章では「なぜ分けるのか」「どこまで分けるのか」を理屈から積み上げます。

6.1 悪いテーブルの典型例 — 全部入り「注文一覧」

ある会社の注文管理が、こんな1枚のテーブルで作られていたとします。Excelの表をそのままCREATE TABLEした、現場で本当によく見る形です。

一見便利そうですが、同じ情報があちこちに重複しています。山田商店の住所が3回、コピー用紙の情報が3回。この重複が、次の3つの「異常(アノマリー)」を引き起こします。

さらによく見ると、注文1004のコピー用紙の単価が480円になっています。値引きなのか入力ミスなのか、表からは判別できません。「同じ事実は1か所にだけ書く」——これが守られていないテーブルは、時間とともに必ず矛盾を蓄積します。正規化とは、この原則を段階的に実現する手続きです。

6.2 第1正規形 — 繰り返しをなくす

最初の関門は「1つのセルには1つの値だけ」というルールです。次のような表を見たことはないでしょうか。

1つのセルに商品名がカンマ区切りで3つ。Excelなら読めますが、データベースでは致命的です。「トナー黒を含む注文を数える」には文字列の部分一致で探すしかなく、遅いうえに「トナー黒EX」も誤ってヒットします。集計も結合もまともにできません。

解決は行に分けることです。繰り返しの単位(注文の明細1件)を1行にします。

すべてのセルが単一の値になった状態を第1正規形(1NF)と呼びます。SQLの WHERE 商品名 = 'トナー黒' や GROUP BY 商品名 が正しく機能するのは、この形になって初めてです。SQLは「1セル1値」を前提に設計された言語なのです。

6.3 第2正規形 — 部分関数従属をなくす

先ほどの明細テーブルに単価と顧客名を戻して考えます。この表の行を一意に特定するには「注文ID+商品名」の複合キー(2列セットの主キー)が必要です。

ここで各列が「キーの何によって決まるか」を観察します。ある列Aの値が決まると列Bの値が1つに決まる関係を関数従属と呼びます。

• 数量 — 注文IDと商品名の両方が決まって初めて決まる(キー全体に従属)→ ここにいてよい
• 単価 — 商品名だけで決まる(コピー用紙A4なら常に500円)→ キーの一部にしか従属していない
• 顧客名 — 注文IDだけで決まる → 同じくキーの一部にしか従属していない

このように複合キーの一部にだけ依存することを部分関数従属と呼びます。単価が商品ごとの事実なのに明細の行数だけ重複コピーされる——6.1節の異常の原因そのものです。解決は、依存先ごとにテーブルを分けることです。

-- 商品の事実は商品テーブルへ、注文の事実は注文テーブルへ
products(商品ID, 商品名, 単価)
orders(注文ID, 顧客名)
order_items(注文ID, 商品ID, 数量)   -- キー全体に従属する数量だけが残る

すべての非キー列がキー全体に従属する状態が第2正規形(2NF)です。「その事実は、何が決まれば決まるのか?」と自問するのがコツです。

6.4 第3正規形 — 推移的関数従属をなくす

ordersテーブルに顧客情報を足して見てみます。主キーは注文IDひとつなので、第2正規形は満たしています。

顧客名と顧客住所は、注文IDから直接決まっているのではなく、「注文ID → 顧客ID → 顧客名・住所」と2段階(玉突き)で決まっています。これを推移的関数従属と呼びます。顧客が引っ越せば注文の行を全部直す羽目になる——またしても同じ病気です。

処方箋も同じで、「顧客IDが決まれば決まるもの」を顧客テーブルへ分離します。

customers(顧客ID, 顧客名, 顧客住所)
orders(注文ID, 顧客ID)   -- 顧客のことは顧客IDという参照だけを持つ

非キー列同士の従属がなくなった状態が第3正規形(3NF)です。実務のテーブル設計は、ほとんどの場合この第3正規形を目標にすれば十分です。ここまでの3段階は、結局ひとつの言葉に要約できます——「すべての列は、キーに、キー全体に、そしてキーだけに従属せよ」。

6.5 正規化の実益と、あえて崩す判断

正規化の対価も正直に見ておきましょう。テーブルが分かれるほど、元の「全部入りの表」を見るにはJOIN(第4章)が必要になります。テーブルが5つ6つと絡む集計クエリは書くのも実行するのも重くなりがちです。

それでも原則は「まず正規化」です。JOINが増えるのは「書く手間と計算コスト」の問題ですが、正規化しない場合に起きるのは「データそのものが信用できなくなる」問題で、重さが違います。遅いクエリは第7章の技術で速くできますが、矛盾したデータは技術では直せません。

そのうえで、実務にはあえて崩す(非正規化する)定番パターンがあります。

• 集計用テーブルを別に持つ — 「日次の売上サマリー」など、夜間バッチでJOINと集計を済ませた結果を専用テーブルに書いておき、朝の報告画面はそれを読むだけにする。正本(正規化されたテーブル)は崩さず、コピーの側だけ崩すのが要点です。
• 履歴には値を焼き付ける — 注文明細に「注文時点の単価」を持たせるのは重複ではなく正解です。商品マスタの単価は将来変わりますが、過去の注文金額は変わってはいけないからです。「現在の事実」と「その時点の記録」は別物、と覚えてください。

6.6 設計演習 — 社内問い合わせ管理システム

仕上げに、要件文からテーブル設計まで通しで歩いてみます。情報システム部にこんな依頼が来たとしましょう。

「社員からのIT問い合わせをチケットとして記録したい。チケットには件名・本文・起票者・担当者・状態(未対応/対応中/完了)がある。チケットには複数のコメントがぶら下がり、誰がいつ書いたかを残したい。」

設計の手順はいつも同じです。①名詞を拾って「モノ」の候補にする → ②モノごとの属性を列にする → ③モノ同士の関係(1対多)を外部キーで結ぶ。

• 名詞を拾う: 社員(users)、チケット(tickets)、コメント(comments)、状態(statuses)
• 関係を考える: 1人の社員は多くのチケットを起票する(1対多)。1つのチケットに多くのコメントが付く(1対多)。状態は「未対応/対応中/完了」の選択肢マスタにする——チケットに文字列で直接書くと表記ゆれ(「完了」「済」「クローズ」)が起きるからです。まさに正規化の発想です。

CREATE TABLE一式はこうなります。

CREATE TABLE users (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE statuses (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE          -- 未対応 / 対応中 / 完了
);

CREATE TABLE tickets (
  id          INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  body        TEXT NOT NULL,
  created_by  INTEGER NOT NULL REFERENCES users(id),     -- 起票者
  assigned_to INTEGER REFERENCES users(id),              -- 担当者(未定ならNULL)
  status_id   INTEGER NOT NULL REFERENCES statuses(id),
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE comments (
  id         INTEGER PRIMARY KEY,
  ticket_id  INTEGER NOT NULL REFERENCES tickets(id),
  author_id  INTEGER NOT NULL REFERENCES users(id),
  body       TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

チェックしてみましょう。1セル1値(1NF)、主キーは単独のidなので部分従属なし(2NF)、「起票者の名前」のような玉突きの列はなく参照はすべてIDだけ(3NF)。担当者が異動で改名しても直すのはusersの1行だけです。要件の名詞がほぼそのままテーブルになる——正規化された設計は、現実の構造の素直な写し絵になります。

まとめ

• 重複を放置したテーブルは、更新不整合・挿入異常・削除異常という3つの病気を必ず発症する
• 第1正規形: 1セル1値。カンマ区切りは行(または別テーブル)に分ける
• 第2正規形: 複合キーの一部にだけ従属する列(部分関数従属)を、その依存先のテーブルへ分離する
• 第3正規形: 「キー以外の列から玉突きで決まる列」(推移的関数従属)を別テーブルへ分離する。合言葉は「キーに、キー全体に、キーだけに」
• 原則は第3正規形。非正規化は計測で必要性を確認してから、正本ではなくコピー側(集計用テーブルなど)で行う
• 個人情報のテーブル分離はアクセス権の分離を可能にし、漏えい時の被害を限定する防御になる

練習問題

部署(部署ID, 部署名)
社員(社員ID, 社員名, 部署ID)          -- 部署IDは部署への外部キー
研修(研修ID, 研修名, 研修時間)
受講記録(社員ID, 研修ID, 受講日)      -- 主キーは(社員ID, 研修ID)

CREATE TABLE tags (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE ticket_tags (
  ticket_id INTEGER NOT NULL REFERENCES tickets(id),
  tag_id    INTEGER NOT NULL REFERENCES tags(id),
  PRIMARY KEY (ticket_id, tag_id)   -- 同じタグの二重付与を防ぐ
);