PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：設計とパフォーマンス － Day21 正規化と設計

Day21 前半のゴール

「“教科書の正規化”から“現場の設計”に橋をかける」

Day21 はいよいよ「実務レベルの設計」です。
これまで、正規化・インデックス・トランザクション・MVCC・ロック…と、部品は一通り見てきました。
ここでのゴールは、「それらをどう組み合わせて“現場で使えるテーブル設計”にするか」をイメージできるようになることです。

前半では、まず次の3つを狙います。
教科書的な正規化（第1〜3正規形）を、ざっくり言葉で説明できる。
「正規化しすぎ」と「正規化しなさすぎ」の両方が危険だと理解する。
実務でよく出る“ユーザー・注文・商品”モデルを題材に、「どこまで正規化するか」を考えられる。

正規化のざっくりおさらい

「“同じ情報を何度も持たない”が出発点」

まず、教科書的な正規化をざっくり言葉にしておきます。
細かい定義より、「何を避けたいか」が大事です。

第1正規形
「1つのカラムに“複数の値”を詰め込まない」
例：tags に「A,B,C」という文字列を入れるより、別テーブルに分ける。

第2正規形
「主キーの一部にだけ依存する属性を、別テーブルに分ける」
例：複合主キー（user_id, shop_id）に対して、shopの住所を持ってしまうなど。

第3正規形
「主キー以外の属性同士が依存しないようにする」
例：郵便番号から住所が決まるのに、両方を同じテーブルに持って更新ズレを起こすなど。

ざっくりまとめると、「同じ情報を何度も持たない」「1つのテーブルに“関係ない責務”を詰め込まない」ということです。

でも、実務では「全部きれいに正規化すればOK」ではありません。
そこが今日のポイントです。

実務での“正規化しすぎ”と“正規化しなさすぎ”

「読みやすさ・書きやすさ・パフォーマンスのバランスを取る」

現場でよくある失敗を、両極端から見てみます。

正規化しすぎの例
ユーザー情報を、名前テーブル・住所テーブル・電話番号テーブル…と細かく分けすぎる。
1画面を表示するだけで、5〜6テーブルをJOINしないといけない。
クエリが複雑になり、インデックス設計も難しくなる。

正規化しなさすぎの例
ユーザー・注文・商品情報を、1つの巨大テーブルに全部詰め込む。
同じ商品名や価格が、何千行にも重複して保存される。
商品名変更時に、過去注文まで全部書き換えてしまい、履歴が壊れる。

どちらも「正しいとは言えない」状態です。

実務レベル設計で大事なのは、「正規化の原則を守りつつ、読み書きのしやすさとパフォーマンスを両立する」こと。
つまり、「どこまで分けるか」「どこからはまとめて持つか」を、ケースごとに判断する力です。

例題：ユーザー・商品・注文の基本モデル

「“3テーブル構成”を軸に考える」

一番よく出るモデルを題材にします。
ユーザー、商品、注文。この3つです。

まず、素直な正規化をするとこうなります。

users テーブル

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

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

products テーブル

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

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

orders テーブル

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  ordered_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),
  ordered_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

order_items テーブル

CREATE TABLE order_items (
  id         BIGSERIAL PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL
);

CREATE TABLE order_items (
  id         BIGSERIAL PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL
);

ここまでは、教科書的な「第3正規形」に近い形です。
ユーザーはユーザー、商品は商品、注文は注文＋明細に分ける。
同じ商品名・価格を、注文ごとに重複して持たない。

このモデルをベースに、「実務でどうアレンジするか」を考えていきます。

実務で出てくる“履歴の問題”

「商品価格が変わったとき、過去の注文をどう扱うか」

このモデルで、よくぶつかるのが「履歴」の問題です。

例えば、商品Aの価格が 1000円 → 1200円 に変わったとします。
このとき、過去の注文の金額をどう扱うべきでしょうか。

正規化だけを考えると、

order_items は product_id と quantity だけ持っている。
価格は products.price を参照して計算する。

しかし、これだと「過去の注文を見たときに、当時の価格ではなく“今の価格”で計算されてしまう」ことになります。
これは多くの業務ではNGです。

そこで実務では、こういうアレンジをよくします。

order_items に「購入時の価格」を持たせる。

ALTER TABLE order_items
ADD COLUMN unit_price INTEGER NOT NULL;

ALTER TABLE order_items
ADD COLUMN unit_price INTEGER NOT NULL;

注文時に、

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (..., ..., ..., current_product_price);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (..., ..., ..., current_product_price);

と、「その時点の価格」をコピーして保存します。

これは、「同じ情報を複数箇所に持つ」という意味では正規化の原則からは外れます。
しかし、「履歴を正しく残す」という実務要件を満たすために、あえて冗長性を許容しているわけです。

ここが、「教科書の正規化」と「実務レベル設計」の一番大きな差です。

“冗長に持つ”ときのルール

「“どちらが真実か”を決めておく」

冗長に持つこと自体は悪ではありません。
問題は、「どちらが真実か分からなくなる」ことです。

さきほどの例なら、

products.price
order_items.unit_price

という2つの価格が存在します。

ここで大事なのは、「それぞれの意味をはっきり分ける」ことです。

products.price
→ 現在の販売価格。今から注文する人に適用される値。

order_items.unit_price
→ 注文時点の価格。履歴として固定される値。

このように、「どちらが“今の真実”で、どちらが“過去の真実”か」を明確にしておくと、
冗長性は「バグの種」ではなく「業務要件を満たすための設計」になります。

セキュリティ・監査の観点でも、「履歴の価格は変わらない」「現在の価格は変わりうる」という区別は重要です。
どの値を監査対象にするか、どの値をユーザーに見せるかが変わってきます。

正規化とパフォーマンスの関係

「JOINのしやすさとインデックス設計をセットで考える」

正規化すると、テーブルは増えます。
テーブルが増えると、JOIN が増えます。
JOIN が増えると、パフォーマンスの話が必ずついてきます。

さきほどのモデルで、「ユーザーの注文履歴一覧」を出すクエリを考えてみます。

SELECT
  o.id,
  o.ordered_at,
  SUM(oi.quantity * oi.unit_price) AS order_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 123
GROUP BY o.id, o.ordered_at
ORDER BY o.ordered_at DESC
LIMIT 20;

SELECT
  o.id,
  o.ordered_at,
  SUM(oi.quantity * oi.unit_price) AS order_amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 123
GROUP BY o.id, o.ordered_at
ORDER BY o.ordered_at DESC
LIMIT 20;

このクエリを速くするには、

orders.user_id にインデックス
order_items.order_id にインデックス

がほぼ必須です。

つまり、「正規化してテーブルを分けるなら、そのJOINを支えるインデックス設計もセットで考える」必要があります。

逆に言うと、

インデックスを張る気がない
JOIN をちゃんと書く自信がない

なら、正規化しすぎるとつらくなります。

Day21 前半で押さえておきたいのは、

正規化＝テーブルを分ける
テーブルを分ける＝JOINが増える
JOINが増える＝インデックス設計が重要になる

という連鎖です。

Day21 前半のまとめ

教科書的な正規化は「同じ情報を何度も持たない」「1テーブルに関係ない責務を詰め込まない」という原則で、ユーザー・商品・注文・注文明細を users / products / orders / order_items に分けるような第3正規形のモデルが基本になる。
しかし実務では、「商品価格が変わっても過去の注文の金額は変えたくない」といった履歴要件があり、そのために order_items.unit_price のような“購入時の価格”を冗長に持たせるなど、あえて正規化から一歩外れる設計をすることがあるが、その際は「products.price＝現在の価格」「order_items.unit_price＝当時の価格」というように“どちらがどの真実か”を明確にしておくことが重要になる。
さらに、正規化によってテーブルとJOINが増える以上、「JOINを支えるインデックス設計（orders.user_id、order_items.order_id など）」をセットで考えることが、実務レベル設計の前半の着地点になる。