テーブル設計と正規化（3 層スキーマ）

本記事で使うデータ — orders_flat（非正規の例）

正規化（normalization、データの重複と矛盾を減らすようにテーブルを分割する設計手法）は、1 つの巨大なテーブルに何でも詰め込む設計を、整合性を保ちやすい複数のテーブルへ分ける考え方です。

本記事では、繰り返し列を持つ非正規テーブルで起きる問題を確認し、第 1〜第 3 正規形と 3 層スキーマの考え方を、設計演習を通して身につけます。

題材は注文データを 1 枚に詰め込んだorders_flat（2 行）です。

顧客名・電話・商品 1・商品 2 が 1 行に並んだ典型的な非正規テーブルで、これをcustomer / order_record / order_lineの 3 テーブルに正規化していきます。

演習に入る前に、orders_flatテーブルの列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(orders_flat);で列名・型を確認してください。商品がitem1 / item2のように繰り返し列になっている点に注目してください。

② SELECT * FROM orders_flat;で全行をプレビューしてください。item2に空文字や NULL が入っている行（商品が 1 つしかない注文）の入り方も観察してください。

非正規テーブルで起きる 3 つの異常

orders_flatのように 1 枚に詰め込んだ設計では、データの追加・変更・削除のときに矛盾が起きやすくなります。

代表的なものが更新異常 / 挿入異常 / 削除異常の 3 つです。

更新異常は、複数行にデータが重複して入っており、たとえば電話番号を変えたい場合、全行を漏れなく更新しないといけなくなること。

挿入異常は、まだ注文していない顧客を登録したくても「注文」の行が無いと顧客情報を入れられないこと。

削除異常は、ある注文を削除すると、その顧客の電話番号など消したくない情報まで一緒に消えてしまうことです。

これらは「1 つの事実を 1 か所に持つ」設計にしていないために起こります。

-- 削除異常の例: 注文 2 を消すと Bob の連絡先も消える
-- (捨てテーブル flat_demo で挙動を観察・本体 orders_flat には触れない)
DROP TABLE IF EXISTS flat_demo;
CREATE TABLE flat_demo(
  order_id INTEGER, customer TEXT, customer_tel TEXT, product TEXT
);
INSERT INTO flat_demo VALUES
  (1,'Alice','03-1111','Pen'),
  (2,'Bob','03-2222','Clip');

-- Bob の注文は 1 件だけ。これを消すと Bob の電話番号 03-2222 も失われる
DELETE FROM flat_demo WHERE order_id = 2;
SELECT * FROM flat_demo;
-- -> Bob の連絡先がデータベースから消えてしまった (削除異常)

「Alice の電話番号が変わったので更新したいが、同じ電話番号が複数の行に重複して入っている」状況を、捨てテーブルで再現します。本演習は単独で動くよう、専用テーブルflat_anomalyを作って完結させます。（正しく実行できれば解説が表示されます）

① flat_anomalyをDROP TABLE IF EXISTSしてから、order_id / customer / customer_tel / productの 4 列でCREATEしてください。

② Alice の注文を 2 行（同じ電話番号03-1111を両方に持つ。商品は Pen と Note）投入してください。

③ Alice の電話番号を03-9999に更新するとき、1 行だけ更新するUPDATE（WHERE order_id = 1のように一方の行だけに当たる条件）を実行し、その後SELECT * FROM flat_anomaly;で 2 行の電話番号が食い違っていることを確認してください。

第 1〜第 3 正規形 — 段階的に分割する

テーブルは段階的に正規化をして整理していきます。

第 1 正規形 — 繰り返し列をやめる

item1 / item2のような番号付きの繰り返し列をやめます。

Before — item1 / item2 の繰り返し列

After — 商品ごとに 1 行に展開・主キーは `(order_id, product)` の複合キー

第 2 正規形 — 主キーの一部だけで決まる列を別テーブルへ

前段で得た表は主キーが (order_id, product) の複合キーです。ただ、よく見ると customer と tel は `order_id` だけで決まります（同じ order_id は同じ customer）。

このように主キーの一部だけで決まる列（部分従属）を別テーブルへ切り出すのが第 2 正規形です。注文ヘッダ order_header と注文明細 order_line に分けます。

Before — customer と tel が行ごとに重複

After — 2 テーブルに分割

order_header（1 行 1 注文）

order_line（複合主キー (order_id, product)、order_id は order_header への外部キー）

第 3 正規形 — 主キー以外の列から決まる列をさらに別テーブルへ

前段で得た order_header をよく見ると、tel は order_id ではなく `customer` から決まっています（同じ Alice なら必ず 03-1111）。

主キーから直接ではなく別の非キー列を介して決まる依存（推移的従属：order_id → customer → tel）をなくすのが第 3 正規形です。

顧客マスタ customer を切り出し、order_record は顧客への外部キーだけを持つ形にします。

Before — tel が customer に従属している

After — 3 テーブルに分割

customer（顧客マスタ）

order_record（注文ヘッダ：顧客を外部キーで参照）

order_line（注文明細：前段と同じ）

ここまで分割すると、1 つの事実（顧客の電話番号、注文と顧客の関係、注文と商品の数量）がそれぞれ 1 か所だけに収まります。これで更新・挿入・削除の 3 つの異常が解消します。

3 層スキーマと完成形 — customer / order_record / order_line

正規化したスキーマは、役割の違う 3 つの層として整理できます。

• マスタ層: customerのように更新頻度が低く、参照される側の実体
• トランザクション層: order_recordのように業務イベントを記録する側
• 明細層: order_lineのように 1 イベントの内訳を行で持つ側

マスタを参照側から外部キーで指すことで、1 つの事実（顧客の電話番号など）が 1 か所に収まります。

完成形は次の 3 テーブルです。

customer(customer_id 主キー, customer_name, customer_tel)、order_record(order_id 主キー, customer_id → customer)、order_line(order_id → order_record, product, qty, 複合主キー(order_id, product))。

電話番号はcustomerの 1 行だけにあるので更新は 1 回、注文の無い顧客もcustomerに入れられ（挿入異常の解消）、注文を消しても顧客はcustomerに残ります（削除異常の解消）。

-- 正規化の利点（読むだけ）: 顧客の電話番号は customer に 1 行だけ
-- orders_flat 形式では Alice の全注文行を直す必要があった = 更新異常
UPDATE customer SET customer_tel = '03-9999'
WHERE customer_name = 'Alice';

-- 1 箇所直すだけで、結合すれば全注文に新しい番号が反映される
SELECT o.order_id, c.customer_name, c.customer_tel
FROM order_record o
JOIN customer c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

「orders_flatを、顧客・注文・注文明細の 3 テーブルに正規化したい」という要件を想定します。外部キーで関連付けた完成形のスキーマを設計し、データを移してから結合で元の一覧を再現します。

① 外部キーチェックを有効化し、order_line → order_record → customerの順でDROP TABLE IF EXISTSしてください（子側から先に消すと安全です）。

② customer（customer_id INTEGER 主キー / customer_name TEXT NOT NULL / customer_tel TEXT）をCREATEしてください。

③ order_record（order_id INTEGER 主キー / customer_id INTEGER NOT NULL・customerへの外部キー）をCREATEしてください。

④ order_line（order_id INTEGER・order_recordへの外部キー / product TEXT NOT NULL / qty INTEGER NOT NULL / (order_id, product)複合主キー）をCREATEしてください。

⑤ customerに Alice(1, 03-1111) と Bob(2, 03-2222)、order_recordに注文 1→顧客 1・注文 2→顧客 2、order_lineに Pen(注文1,2) / Note(注文1,1) / Clip(注文2,5) を投入してください。

⑥ 3 テーブルをorder_idとcustomer_idで結合し、order_id / customer_name / customer_tel / product / qtyをorder_id・productの昇順で取り出して、元の一覧が再現できることを確認してください。

実践 2 で作ったcustomer / order_record / order_lineの 3 テーブルに対して、非正規テーブルで起きていた更新・挿入・削除の 3 つの異常がもう起きないことを順に確かめます。本記事の最後の演習です。

① 更新異常の解消: Alice の電話番号を'03-9999'に変更してください。customerテーブルで 1 行 UPDATE するだけで済むことを確認してください。

② 挿入異常の解消: まだ注文の無い新顧客 Charlie（customer_id 3 / customer_name 'Charlie' / customer_tel '03-3333'）をcustomerに INSERT してください。注文が無くても顧客を登録できることを確認します。

③ 削除異常の解消: 注文 1 を削除します。子側のorder_lineから先に消し、続けてorder_recordから消してください。SELECT * FROM customer ORDER BY customer_id;でAlice の行は customer に残っていることを確認してください。

Tips — 各テーブルには必ず主キーを置く

正規化したかどうかに関わらず、どのテーブルにも主キー (`PRIMARY KEY`) を必ず置く設計にします。主キーがあると次の 3 つが得られます。

- 行を一意に特定できる: 同じ値の行が複数できないため、UPDATE / DELETE の対象を WHERE pk = ... で確実に 1 行に絞れる

- 他テーブルから参照できる: 外部キーで参照されるには相手側に主キー (または UNIQUE) が必要。正規化で分割した先のテーブルを結びつける土台になる

- 自動でインデックスが張られる: 主キー列には B-Tree インデックスが自動作成され、等値検索・JOIN が高速になる

単一列で一意になる列が無いときは、PRIMARY KEY (order_id, product) のような複合主キーにします。「とりあえず後で付ければよい」と省略すると、行が重複したときに区別できず、上の 3 つの利点をすべて失います。