SQLite | ゼロからはじめるSQL、30日で習得するSQLite：実践 － Day26 設計改善

Day26 後半のゴール

「“第1〜第3正規形”を、用語より“感覚”で理解する」

前半では、
同じ情報を何度も書かない
意味の違う情報を同じ場所に押し込まない

という、正規化の根っこを話しました。

後半では、よく出てくる

第1正規形
第2正規形
第3正規形

を、「名前を覚える」のではなく
「こういう状態はヤバい／こう直すと気持ちいい」という感覚でつかんでもらいます。

第1正規形

「1つのマスには“1つの値だけ”を入れる」

第1正規形は、いちばん基本的なルールです。

1つのカラム（1つのマス）に、複数の値を詰め込まない
繰り返しの項目を、横にズラズラ並べない

さっきの「タグをカンマ区切りで入れる」は、典型的な第1正規形違反でした。

CREATE TABLE customers_bad2 (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  tags  TEXT      -- 'VIP,メルマガ,キャンペーンA'
);

CREATE TABLE customers_bad2 (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  tags  TEXT      -- 'VIP,メルマガ,キャンペーンA'
);

ここでは、tags に「VIP」「メルマガ」「キャンペーンA」という
3つの値が詰め込まれています。

これを第1正規形に近づけると、こうなります。

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

CREATE TABLE customer_tags (
  customer_id INTEGER NOT NULL,
  tag_id      INTEGER NOT NULL,
  PRIMARY KEY (customer_id, tag_id)
);

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

CREATE TABLE customer_tags (
  customer_id INTEGER NOT NULL,
  tag_id      INTEGER NOT NULL,
  PRIMARY KEY (customer_id, tag_id)
);

顧客とタグの関係を「行」で表現することで、
1つのマスに1つの値、という状態に戻しています。

第1正規形は、
「カンマ区切り・スラッシュ区切り・JSON文字列でごまかさない」
というチェックポイントとして覚えておくといいです。

第2正規形

「主キーの“片方だけ”に依存するカラムを追い出す」

第2正規形は、
「複合主キーを使っているときに出てくる問題」を整理するルールです。

少しだけ踏み込んだ例を出します。

CREATE TABLE order_items_bad (
  order_id    INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  product_name TEXT   NOT NULL,
  unit_price   INTEGER NOT NULL,
  quantity     INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items_bad (
  order_id    INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  product_name TEXT   NOT NULL,
  unit_price   INTEGER NOT NULL,
  quantity     INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

ここでは、order_id と product_id の組み合わせが主キーです。
問題は、product_name です。

product_name は、本質的には product_id だけで決まる情報です。
order_id には関係ありません。

つまり、

主キー（order_id, product_id）の「一部（product_id）」にだけ依存しているカラム

が混ざっている状態です。

これが、第2正規形の観点から見た「よくない状態」です。

改善するには、
product_name を products テーブルに追い出します。

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  order_id    INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  unit_price  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  order_id    INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  unit_price  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

product_name は products.name に集約され、
order_items には「注文ごとに変わりうる情報」だけが残ります。

第2正規形は、
「複合キーの“片方だけ”で決まる情報を、そのテーブルに置きっぱなしにしない」
という感覚で捉えると分かりやすいです。

第3正規形

「“A で B が決まり、B で C が決まる”なら、C は別テーブルへ」

第3正規形は、
「間接的に決まってしまう情報を同じテーブルに置かない」
というルールです。

よくある例が、社員テーブルです。

CREATE TABLE employees_bad (
  id           INTEGER PRIMARY KEY,
  name         TEXT NOT NULL,
  department_id   INTEGER NOT NULL,
  department_name TEXT    NOT NULL
);

CREATE TABLE employees_bad (
  id           INTEGER PRIMARY KEY,
  name         TEXT NOT NULL,
  department_id   INTEGER NOT NULL,
  department_name TEXT    NOT NULL
);

ここで、

id が決まれば department_id が決まる
department_id が決まれば department_name が決まる

という関係になっています。

つまり、

id → department_id → department_name

という「決まり方のチェーン」がある状態です。

このとき、department_name は
「主キー（id）に直接依存している」のではなく、
「department_id に依存している」だけです。

これが、第3正規形の観点から見た「よくない状態」です。

改善するには、部署を別テーブルにします。

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

CREATE TABLE employees (
  id             INTEGER PRIMARY KEY,
  name           TEXT NOT NULL,
  department_id  INTEGER NOT NULL
);

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

CREATE TABLE employees (
  id             INTEGER PRIMARY KEY,
  name           TEXT NOT NULL,
  department_id  INTEGER NOT NULL
);

こうすると、

部署名を変えたいときは departments だけ直せばよい
社員テーブルには「社員ごとに違う情報」だけが残る

という状態になります。

第3正規形は、
「このカラム、本当に主キーから“直接”決まっている？」
「それとも、別のカラムを経由して決まっていない？」

と自問自答するためのチェックポイントです。

「悪い設計 → 改善後」を並べて見る

「違いを“目で見て”覚える」

ここまでの話を、ざっくり並べてみます。

第1正規形の悪い例
1カラムにカンマ区切りでタグを詰め込む
改善後：タグを別テーブルにし、中間テーブルで多対多を表現

第2正規形の悪い例
複合主キーの片方だけで決まる情報（product_name）を、明細テーブルに持たせる
改善後：商品情報は products に集約し、明細には「注文ごとに変わる情報」だけを残す

第3正規形の悪い例
社員テーブルに部署IDと部署名を両方持たせる
改善後：部署は departments に切り出し、社員テーブルには department_id だけを持たせる

ここで大事なのは、
「第何正規形か」を暗記することではなく、

同じ情報をあちこちに書いていないか
主キーと関係ない情報が紛れ込んでいないか
別のカラムを経由しないと決まらない情報が混ざっていないか

という“違和感センサー”を育てることです。

正規化しすぎ？という不安との付き合い方

「まず“意味的にきれい”を優先し、必要なら“読み取り用”を足す」

よくある悩みがこれです。

「ここまで分けると、JOIN が増えてつらくないですか？」

答えは、

書くクエリの種類による

です。

アプリの中で頻繁に使う画面で、
毎回 4〜5 テーブル JOIN するのが重いなら、
ビューや集計テーブルを用意するのはアリです。

例えば、

社員×部署名を JOIN したビュー
顧客×都道府県名を JOIN したビュー

を作っておけば、
アプリ側はシンプルな SELECT だけで済みます。

ここで重要なのは、

正規化された“元データ”はきれいに保つ
読み取り用の“ビュー／集計テーブル”は、用途に応じて足す

という役割分担です。

元データまで崩してしまうと、
あとからの変更・集計・監査が一気に苦しくなります。

セキュリティ・監査の視点から見た正規化の“副作用”

「誰がどこに触れるかを、テーブル単位でコントロールしやすくなる」

もう一度、セキュリティの話に戻します。

情報が正規化されていると、

認証情報だけを持つテーブル
権限だけを持つテーブル
個人情報だけを持つテーブル
売上だけを持つテーブル

のように、役割ごとにテーブルが分かれます。

これはそのまま、

「このアプリは売上テーブルだけ参照」
「このバッチは顧客プロフィールだけ参照」

といった形で、アクセス権限をテーブル単位で切り分けやすい、
という意味につながります。

逆に、
1つのテーブルに何でもかんでも詰め込んでいると、

「このテーブルにアクセスできる人は、全部見えてしまう」

という状態になり、
権限設計が一気に難しくなります。

正規化は、
パフォーマンスのためだけでも、
理論のためだけでもなく、

「どこに何があるかを明確にし、
どこまで見せるかを制御しやすくする」

という意味で、セキュリティの土台にもなっています。

Day26 後半のまとめ

第1正規形は「1つのマスに1つの値」。カンマ区切りや複数値詰め込みは危険信号で、別テーブル＋中間テーブルに分けるのが筋。
第2正規形は「複合主キーの片方だけで決まる情報を、そのテーブルに置かない」。商品名のような情報は products に集約し、明細には“注文ごとに変わる情報”だけを残す。
第3正規形は「主キー → A → B のような間接的な依存を同じテーブルに置かない」。部署名のような情報は部署マスタに切り出し、社員テーブルには部署IDだけを持たせる。
正規化は「まず意味的にきれいに分ける」ためのもので、パフォーマンスが必要な箇所はビューや集計テーブルなど“読み取り専用のまとめ”で補う、という役割分担で考えるとバランスが取りやすい。
テーブルを意味ごとに分けておくと、「どこに何の情報があるか」が明確になり、アクセス権限をテーブル単位で切り分けやすくなるため、セキュリティ・監査の観点でも強い設計になる。

ここまで来たあなたは、
「とりあえずテーブルを作れる人」から、
「テーブル設計を“育てていける人”」に足を踏み入れています。
この感覚があると、現場のデータベースと長く付き合えるようになります。