- Day20 前半
- 「テーブルを“ちゃんと分ける”ことで、あとから自分を助けるのが正規化」
- まずは「悪い例」を見てみる
- 1つのテーブルに“全部詰め込む”と何が起きるか
- 正規化とは何か
- 「同じ情報を何度も書かないように、テーブルを分解するルール」
- 第1正規形（1NF）
- 「1つのマスには“1つの値”だけを入れる」
- 第1正規形に直してみる
- 「繰り返しの列を“行”として分解する」
- 第2正規形（2NF）
- 「主キーの“一部にだけ依存する列”を別テーブルに出す」
- 第2正規形に直してみる
- 「商品情報は products テーブルに分離する」
- 第3正規形（3NF）
- 「キー以外の列同士が“勝手に依存し合っている”のをやめさせる」
- 第3正規形に直してみる
- 「郵便番号マスタを作り、users からは zip_code だけを持つ」
- セキュリティの視点から見る正規化
- 「矛盾しないことは、そのまま“改ざん検知のしやすさ”につながる」
- Day20 前半のまとめ
Day20 前半
「テーブルを“ちゃんと分ける”ことで、あとから自分を助けるのが正規化」
ここまで、1つのテーブルを前提に
SELECT / JOIN / UPDATE / DELETE / INDEX を見てきました。
でも、現実のアプリでは、
テーブルは1個では終わりません。
ユーザー
注文
商品
ログイン履歴
…と、どんどん増えていきます。
ここで大事になるのが、
「テーブルをどう分けるか」＝テーブル設計 です。
今日のテーマ 正規化（第1〜第3正規形） は、
「テーブルをどう分ければ、“あとから困らない形”になるか」
を考えるための、古くて強い考え方です。
難しそうな名前ですが、
やっていることは一言でいうと、
「同じことを何度も書かないように、テーブルを整理する」
です。
まずは「悪い例」を見てみる
1つのテーブルに“全部詰め込む”と何が起きるか
いきなり正規化の定義に行く前に、
わざと「悪い設計」を見てみます。
例えば、こんな orders テーブルを考えてください。
order_id | user_name | user_email | item1_name | item1_price | item2_name | item2_price
---------+-----------+-------------------+------------+-------------+------------+------------
1 | 山田太郎 | taro@example.com | りんご | 100 | バナナ | 150
2 | 山田太郎 | taro@example.com | みかん | 200 | NULL | NULL
一見すると、「動きそう」ですよね。
でも、よく見ると問題だらけです。
同じユーザーの名前・メールアドレスが、注文ごとに何度も出てくる
商品が2つまでしか持てない（item3 が必要になったらどうする？）
商品名と価格が、列として横に増えていく
このままアプリが大きくなると、
「山田太郎のメールアドレスが変わったから全部の行を UPDATE」
「3つ目の商品を追加したいから、item3_name 列を追加」
みたいな、“地獄のメンテナンス” が始まります。
正規化は、この地獄を避けるための考え方です。
正規化とは何か
「同じ情報を何度も書かないように、テーブルを分解するルール」
正規化（normalization）をざっくり言うと、
「同じ意味の情報を、複数の場所に重複させないようにテーブルを整理すること」
です。
同じユーザー情報を、あちこちのテーブルにバラバラに持たない
同じ商品情報を、注文ごとにコピペしない
こうすることで、
更新漏れが起きない
矛盾したデータが生まれない
テーブルが“素直な形”になって、SQL も書きやすくなる
というメリットがあります。
正規化には「第1正規形」「第2正規形」「第3正規形」…と段階がありますが、
Day20 ではまず 第1〜第3正規形 のイメージを掴むことがゴールです。
第1正規形（1NF）
「1つのマスには“1つの値”だけを入れる」
一番最初のルールが 第1正規形（1NF） です。
これはシンプルで、
「1つの列（1つのセル）に、複数の値を詰め込まない」
というルールです。
さっきの悪い例を思い出してください。
item1_name | item1_price | item2_name | item2_price
これはまだ「列が複数あるだけ」なのでギリギリ許容範囲に見えますが、
もっとひどいパターンはこうです。
items | prices
-------------+----------------
りんご,バナナ | 100,150
1つのセルに「りんご,バナナ」とカンマ区切りで入れている。
これは完全に 1NF 違反です。
なぜダメかというと、
SQL から「2つ目の商品だけ取り出す」ことができない
「バナナだけ値上げしたい」ときに、文字列を分解しないといけない
つまり、「テーブルの中に“さらにテーブルっぽいもの”を埋め込んでいる」 状態だからです。
第1正規形のゴールは、
1行 × 1列 = 1つの値
繰り返しや配列やカンマ区切りを、1セルに押し込まない
という、“表として素直な形” にすることです。
第1正規形に直してみる
「繰り返しの列を“行”として分解する」
さっきの orders の商品部分だけを、1NF に直してみます。
元の形（悪い例）：
order_id | item1_name | item1_price | item2_name | item2_price
---------+------------+-------------+------------+------------
1 | りんご | 100 | バナナ | 150
2 | みかん | 200 | NULL | NULL
これを、「1行に1商品」に分解します。
order_id | item_name | item_price
---------+-----------+-----------
1 | りんご | 100
1 | バナナ | 150
2 | みかん | 200
こうすると、
「注文1の全商品を取りたい」
→ WHERE order_id = 1 で OK
「バナナだけ値上げしたい」
→ WHERE item_name = 'バナナ' で UPDATE できる
という、SQL で素直に扱える形 になります。
これが第1正規形の基本的な変換です。
第2正規形（2NF）
「主キーの“一部にだけ依存する列”を別テーブルに出す」
第2正規形は、
第1正規形を満たしていることを前提に、
「主キーの一部にだけ依存する列を、別テーブルに分ける」
というルールです。
いきなり定義だけ聞くと分かりにくいので、
さっきの「注文商品」テーブルを少し拡張してみます。
order_id | item_id | item_name | item_price | quantity
---------+---------+-----------+------------+---------
1 | 1 | りんご | 100 | 2
1 | 2 | バナナ | 150 | 1
2 | 2 | バナナ | 150 | 3
ここで、主キー（PRIMARY KEY）を(order_id, item_id) の複合キーだとします。
このとき、列ごとの「依存関係」を考えます。
quantity（数量）は、「どの注文の、どの商品か」によって決まる
→ (order_id, item_id) の両方に依存している
一方で、
item_name, item_price は、「商品そのもの」によって決まる
→ item_id だけに依存している
つまり、
item_name, item_price は、主キーの一部（item_id）にだけ依存している
という状態です。
第2正規形では、
こういう「主キーの一部にだけ依存する列」を
別テーブル（商品マスタ）に切り出します。
第2正規形に直してみる
「商品情報は products テーブルに分離する」
さっきのテーブルを、第2正規形を意識して分解します。
まず、「注文ごとの商品行」だけにします。
order_items
order_id | item_id | quantity
---------+---------+---------
1 | 1 | 2
1 | 2 | 1
2 | 2 | 3
そして、「商品そのものの情報」は別テーブルにします。
products
item_id | item_name | item_price
--------+-----------+-----------
1 | りんご | 100
2 | バナナ | 150
こうすると、
バナナの値段を 200 に変えたい
→ products だけ UPDATE すればよい
という状態になります。
元の設計のままだと、
order_id | item_id | item_name | item_price | quantity
---------+---------+-----------+------------+---------
1 | 2 | バナナ | 150 | 1
2 | 2 | バナナ | 150 | 3
のように、
バナナの価格が複数行に重複してしまい、
「全部 UPDATE しないと矛盾が出る」 状態でした。
第2正規形のゴールは、
「主キーの一部にだけ依存する情報」を切り出して、
“その情報の本体”を1か所にまとめる
ことです。
第3正規形（3NF）
「キー以外の列同士が“勝手に依存し合っている”のをやめさせる」
第3正規形は、
第2正規形を満たしていることを前提に、
「キー以外の列が、別のキー以外の列に依存しているなら分ける」
というルールです。
これも例で見た方が早いです。
例えば、ユーザーの住所をこう持っているとします。
users
user_id | zip_code | prefecture | city
--------+----------+------------+---------
1 | 100-0001 | 東京都 | 千代田区
2 | 150-0001 | 東京都 | 渋谷区
ここで、依存関係を考えます。
user_id → zip_code, prefecture, city
というのは自然です。
でも実際には、
zip_code → prefecture, city
という関係もありますよね。
郵便番号が決まれば、都道府県・市区は決まる。
つまり、
prefecture, city は、user_id ではなく zip_code に依存している
という状態です。
第3正規形では、
こういう「キー以外の列同士の依存」を
別テーブルに切り出します。
第3正規形に直してみる
「郵便番号マスタを作り、users からは zip_code だけを持つ」
さっきの例を、第3正規形を意識して分解します。
users は、郵便番号だけを持つようにします。
users
user_id | zip_code
--------+----------
1 | 100-0001
2 | 150-0001
そして、郵便番号に紐づく住所情報は別テーブルにします。
zip_codes
zip_code | prefecture | city
---------+------------+---------
100-0001 | 東京都 | 千代田区
150-0001 | 東京都 | 渋谷区
こうすると、
郵便番号 100-0001 の市区名が変わった
→ zip_codes だけ UPDATE すればよい
という状態になります。
元の設計のままだと、
同じ郵便番号が複数ユーザーに出てきたときに、
prefecture, city を全部直さないと矛盾が出ます。
第3正規形のゴールは、
「キー以外の列同士が勝手に依存し合っている状態」をやめさせて、
“その情報の本体”を1か所にまとめること
です。
セキュリティの視点から見る正規化
「矛盾しないことは、そのまま“改ざん検知のしやすさ”につながる」
正規化は一見「設計のきれいさ」の話ですが、
セキュリティの観点でも効いてきます。
同じ情報があちこちに重複していると、
一部だけ改ざんされても気づきにくい
どれが正しい値なのか分からなくなる
という問題が起きます。
逆に、
ユーザー情報は users に集約
商品情報は products に集約
郵便番号情報は zip_codes に集約
という正規化された構造だと、
「このテーブルさえ見れば、その情報の“正”が分かる」
状態になります。
これは、
監査ログと突き合わせる
改ざんの有無をチェックする
といったときに、とても重要です。
Day20 前半のまとめ
正規化は、「同じ意味の情報を何度も書かないようにテーブルを整理する」ための考え方。
第1正規形は「1つのセルに複数の値を詰め込まない（繰り返し・カンマ区切りをやめて、行として分解する）」。
第2正規形は「主キーの一部にだけ依存する情報（商品名・価格など）を、別テーブルに切り出して“本体”を1か所にまとめる」。
第3正規形は「キー以外の列同士が依存している場合（郵便番号→住所など）に、その依存先を別テーブルとして切り出す」。
こうして正規化されたテーブルは、更新漏れや矛盾が起きにくく、セキュリティ的にも“どこが真実か”が分かりやすい構造になる。
後半では、
実際に「ダメな1枚テーブル」から「第3正規形っぽい構造」へ分解していくステップ、
正規化しすぎると逆に辛くなるケース（あえて崩す“非正規化”）の話、
アプリ開発で「どこまで正規化するか」を決める現実的なライン
まで踏み込んで、正規化を“机上の理屈”から“使える設計の感覚”に変えていきます。