- Day20 後半
- 「1枚テーブル」から「ちゃんと分かれたテーブル」へ分解してみる
- スタート地点：よくある「全部入り orders テーブル」
- 第1正規形への分解
- 「繰り返し列を“行”に落とす」
- 第2正規形への分解
- 「商品情報を products に切り出して、“本体”を1か所にする」
- 第3正規形への分解
- 「ユーザー情報を users に切り出し、住所などもさらに整理する」
- 正規化しすぎると何が起きるか
- 「JOIN だらけで、読むのも書くのもつらくなる」
- あえて崩す「非正規化」との付き合い方
- 「正規化を理解したうえで、意図的にルールを緩める」
- セキュリティの視点から見る「正規化と非正規化の線引き」
- 小さな練習イメージ
- Day20 後半のまとめ
Day20 後半
「1枚テーブル」から「ちゃんと分かれたテーブル」へ分解してみる
前半では、第1〜第3正規形を
それぞれバラバラの例で見ました。
後半では、あえて「よくあるダメな1枚テーブル」からスタートして、
それを第1→第2→第3正規形っぽい形に分解していく流れを、
通しでイメージしてみます。
そのうえで、
どこまで正規化するか
あえて崩す「非正規化」との付き合い方
セキュリティ的に「正規化しておくと得するポイント」
まで触れていきます。
スタート地点：よくある「全部入り orders テーブル」
まず、わざと悪い例を用意します。
order_id | order_date | user_name | user_email | item1_name | item1_price | item2_name | item2_price
---------+-------------+-----------+-------------------+------------+-------------+------------+------------
1 | 2025-05-01 | 山田太郎 | taro@example.com | りんご | 100 | バナナ | 150
2 | 2025-05-02 | 山田太郎 | taro@example.com | みかん | 200 | NULL | NULL
問題を整理すると、こうなります。
同じユーザー情報が注文ごとに重複している
商品が2つまでしか持てない構造になっている
商品名・価格が横に増えていく（item3, item4…地獄）
このまま機能を足していくと、
メールアドレス変更のたびに全行 UPDATE
3つ目の商品を扱うたびにスキーマ変更
「どこが正しい商品情報なのか」分からなくなる
という未来が見えます。
ここから、正規化のステップを踏んでいきます。
第1正規形への分解
「繰り返し列を“行”に落とす」
まずは第1正規形、
「1セルに複数の値を詰め込まない」「繰り返し列をやめる」です。
商品部分を「1行1商品」に分解して、
注文と商品を分けて考えます。
注文そのものはこうします。
orders
order_id | order_date | user_name | user_email
---------+-------------+-----------+-------------------
1 | 2025-05-01 | 山田太郎 | taro@example.com
2 | 2025-05-02 | 山田太郎 | taro@example.com
商品行はこうします。
order_items
order_id | item_name | item_price
---------+-----------+-----------
1 | りんご | 100
1 | バナナ | 150
2 | みかん | 200
これで、
「注文1の全商品」→ order_items で WHERE order_id = 1
「バナナだけ値上げ」→ item_name で UPDATE
といった操作が、SQL で素直に書けるようになります。
まだ重複は残っていますが、
「繰り返し列を行に落とす」という第1正規形のゴールは達成です。
第2正規形への分解
「商品情報を products に切り出して、“本体”を1か所にする」
次に第2正規形、
「主キーの一部にだけ依存する列を分ける」です。
order_items を少し拡張して、item_id を導入します。
order_items
order_id | item_id | item_name | item_price
---------+---------+-----------+-----------
1 | 1 | りんご | 100
1 | 2 | バナナ | 150
2 | 3 | みかん | 200
ここで主キーを (order_id, item_id) とすると、
quantity（もしあれば）は (order_id, item_id) に依存
item_name, item_price は item_id にだけ依存
という状態になります。
第2正規形では、
item_name, item_price を「商品マスタ」に切り出します。
products
item_id | item_name | item_price
--------+-----------+-----------
1 | りんご | 100
2 | バナナ | 150
3 | みかん | 200
order_items はこうなります。
order_items
order_id | item_id
---------+--------
1 | 1
1 | 2
2 | 3
これで、
バナナの価格変更 → products だけ UPDATE
注文ごとの商品一覧 → order_items と products を JOIN
という、「商品情報の本体は products に1か所」
という状態が作れました。
第3正規形への分解
「ユーザー情報を users に切り出し、住所などもさらに整理する」
次に第3正規形、
「キー以外の列同士の依存を分ける」です。
orders を少しリッチにしてみます。
orders
order_id | order_date | user_id | user_name | user_email
---------+-------------+---------+-----------+-------------------
1 | 2025-05-01 | 1 | 山田太郎 | taro@example.com
2 | 2025-05-02 | 1 | 山田太郎 | taro@example.com
ここで、
user_name, user_email は user_id に依存している
→ orders に重複して持つ必要はない
と分かります。
そこで、ユーザー情報を users に切り出します。
users
user_id | user_name | user_email
--------+-----------+-------------------
1 | 山田太郎 | taro@example.com
orders はこうシンプルになります。
orders
order_id | order_date | user_id
---------+-------------+--------
1 | 2025-05-01 | 1
2 | 2025-05-02 | 1
さらに、住所情報まで持つとします。
users
user_id | zip_code | prefecture | city
--------+----------+------------+---------
1 | 100-0001 | 東京都 | 千代田区
ここで、
prefecture, city は zip_code に依存している
→ 第3正規形的には zip_codes に切り出せる
という話が前半でした。
zip_codes
zip_code | prefecture | city
---------+------------+---------
100-0001 | 東京都 | 千代田区
users は zip_code だけを持つ形にできます。
こうして、
ユーザーの本体 → users
住所の本体 → zip_codes
商品情報の本体 → products
注文の本体 → orders
注文ごとの商品行 → order_items
という、「それぞれの“本体”が1か所にある」構造 ができあがります。
正規化しすぎると何が起きるか
「JOIN だらけで、読むのも書くのもつらくなる」
ここまで聞くと、
「じゃあ、とにかく細かく分ければいいんだな」
と思いがちですが、
現実はもう少しバランスが必要です。
正規化を突き詰めすぎると、
SELECT のたびに JOIN が3段、4段と増える
シンプルな画面表示でも複数テーブルをまたぐ
開発者がテーブル構造を追いきれなくなる
といった「運用のつらさ」が出てきます。
例えば、
ユーザー一覧画面で、
名前・メール・都道府県・市区を出したいだけなのに、
users
JOIN zip_codes
が必須になる、などです。
この「JOIN が増えすぎてつらい」問題を避けるために、
あえて少しだけ正規化を崩すことがあります。
これが 非正規化（denormalization） です。
あえて崩す「非正規化」との付き合い方
「正規化を理解したうえで、意図的にルールを緩める」
非正規化は、
正規化のルールを知らないまま雑に崩すこと
ではなく、
正規化のルールを理解したうえで、
「ここはあえて重複を許した方がトータルで得」と判断して崩すこと
です。
例えば、
users に prefecture_name だけを持たせてしまう
（zip_codes から JOIN してもいいが、よく使うのでコピーしておく）
orders に user_name を持たせておく
（履歴として「当時の名前」を残したい、JOIN なしで見たい）
などが現実的な例です。
このとき大事なのは、
どの列が「本体」で、どの列が「コピー」なのかを意識する
コピー側は「多少古くてもいい」「矛盾しても許容」と割り切る
という線引きです。
「全部を常に完全一致させる」ことを目指すと、
非正規化はほぼ不可能になります。
セキュリティの視点から見る「正規化と非正規化の線引き」
セキュリティ的に見ると、
「真実の値がどこにあるか」が明確であること
（＝正規化された“本体テーブル”があること）
はとても重要です。
監査や不正調査をするとき、
「このテーブルのこの列が正」と決められるかどうかで、
調査の難易度が大きく変わります。
一方で、非正規化されたコピー列は、
画面表示用のキャッシュ
履歴としての“当時の値”
など、用途を限定しておくと扱いやすいです。
セキュリティの観点からは、
本人確認や権限判定に使う値
ログイン・認証に関わる値
などは、できるだけ正規化された“本体”に集約し、
コピーを増やさない方が安全です。
逆に、
集計用のカウンタ
画面表示用の冗長な文字列
などは、非正規化してもリスクが低いことが多いです。
小さな練習イメージ
頭の中で、次のような1枚テーブルを想像してみてください。
ユーザー名・メール・住所・商品名・商品価格・数量・注文日が、全部1テーブルに入っている。
そこから、
ユーザーの本体はどこに切り出すか
商品情報の本体はどこに切り出すか
住所情報は郵便番号とどう分けるか
を順番に考えてみると、
第1〜第3正規形のステップが、だいぶ立体的に見えてくるはずです。
Day20 後半のまとめ
「全部入り1枚テーブル」から、第1→第2→第3正規形へ分解していくと、ユーザー・商品・住所・注文・注文商品が、それぞれ“本体テーブル”として整理される。
正規化のゴールは、「同じ意味の情報の本体を1か所にまとめ、更新漏れや矛盾を防ぐ」ことであり、これはそのまま改ざん検知や監査のしやすさにもつながる。
正規化しすぎると JOIN だらけになり、開発・運用がつらくなるため、あえて一部を崩す「非正規化」も現場ではよく使われる。
非正規化は、「どれが本体で、どれがコピーか」を意識し、コピー側は多少のズレを許容する前提で行うのがポイント。
本人確認や権限判定など“セキュリティ上の真実”に関わる情報は、できるだけ正規化された本体に集約し、コピーを増やさない設計が望ましい。
ここまで来たあなたは、
「SQL を書く人」から一歩進んで、
「データの形そのものを設計する人」の入り口に立っています。
この感覚があると、今後のテーブル設計・API 設計・ログ設計が、全部一段クリアに見えるようになります。