正規化
正規化とは？
- データの重複や矛盾を防ぐための分析ルール
- データの繰り返しをなくす(助長の排除)
なぜ正規化するのか？
| 正規化しないと | 発生する問題 |
|---|---|
| データが重複する | 更新漏れ・整合性崩壊 |
| 一部だけ変更しにくい | データの一貫性が保てない |
| 無駄に巨大なテーブルになる | パフォーマンス悪化、保守性低下 |
正規化ステップ(概要)
| 正規形 | 要点 | 例 |
|---|---|---|
| 第1正規形(1NF) | 繰り返しの排除 | 著者が複数 ⇨著者テーブルに分離 |
| 第2正規形(1NF) | 主キーの部分従属排除 | 複合キーの一部に依存 ⇨著者テーブルに分離 |
| 第3正規形(1NF) | 推移的従属の排除 | 他のカラムに依存する属性 ⇨切り出し |
正規化実演 第1正規形
❌Step0 : 非正規化テーブル(Before)
| 書籍ID | 書名 | 著者1 | 著者2 | 出版社名 |
|---|---|---|---|---|
| 1 | SQL超入門 | 山田太郎 | 佐藤花子 | 技術評論社 |
| 2 | Ruby入門 | 高橋次郎 | NULL | オライリー |
問題点
- 著者が複数列に分かれていて構造が不安定
- 出版社名を毎回直接書くため、冗長・誤記の温床
⬇︎
✅Step1 : 第1正規形(1NF) : 繰り返しのある列を排除し、縦待ちにする
| 書籍ID | 書名 | 著者名 | 出版社名 |
|---|---|---|---|
| 1 | SQL超入門 | 山田太郎 | 技術評論社 |
| 1 | SQL超入門 | 佐藤花子 | 技術評論社 |
| 2 | Ruby入門 | 高橋次郎 | オライリー |
改善点
- 著者が何人でも対応できる構造に(列が増えない)
⬇︎
✅Step2 : 第2正規形(2NF) : 主キーの一部にだけ依存するデータ(著者名)を別テーブルへ
※ 主キー: テーブル上でユニークになるキー
書籍テーブル
| 書籍ID | 書名 | 出版社名 |
|---|---|---|
| 1 | SQL超入門 | 技術評論社 |
| 2 | Ruby入門 | オライリー |
書籍_著者テーブル(中間テーブル)
| 書籍ID | 著者名 |
|---|---|
| 1 | 山田太郎 |
| 1 | 佐藤花子 |
| 2 | 高橋次郎 |
改善点
- 書籍と著者の多対多関係が表現可能に
⬇︎
✅Step3 : 第3正規形(3NF) : 推移的従属を排除 出版社名を別テーブルに分離
書籍テーブル
| 書籍ID | 書名 | 出版社ID |
|---|---|---|
| 1 | SQL超入門 | 1 |
| 2 | Ruby入門 | 2 |
出版社テーブル
| 出版社ID | 出版社名 |
|---|---|
| 1 | 技術評論社 |
| 2 | オライリー |
主キー・複合キー
主キーとは？
そのレコードを一意に識別するためのカラム
- 他と被らない値
- NULLは禁止
- １つのテーブルに１つだけ
| ユーザーID | 名前 | メールアドレス |
|---|---|---|
| 1 | SQL超入門 | 1 |
『ユーザーID』が主キー手に適任(どれか１つでその人を一意に特定できる)
複合キーとは？
2つ以上のカラムを組み合わせて主キーにすること
| 注文ID | 商品ID | 数量 |
|---|---|---|
| 101 | A01 | 2 |
| 101 | B02 | 1 |
- このテーブルでは『注文IDだけ』『商品IDだけ』では一意にならない
- 注文ID＋商品IDの組み合わせで主キーにする
複合キーのデメリット
- 複合キーは便利だが、複雑になりやすい
- 外部キー制約やJOINの時に扱いが面倒になりがち
サロゲートキーとは？
意味を持たない、システムが勝手に割り振るID(代理キー)
✅よくある形式
- id: 自動連番(1,2,3,...)
- UUID(グローバルにユニークな識別子)
| 書籍ID | 書名 | ISBN |
|---|---|---|
| 1 | SQL超入門 | 978-12345678 |
ISBNで一意に識別できるが、内部的にはシンプルな数字のIDを主キーにした方が扱いやすい
サロゲートキーのメリット
- 意味がない → 値の変更・比較がしやすい
- システム間で一貫した管理がしやすい
- 複合キーの複雑さを回避できる
ナチュラルキー VS サロゲートキー
| 比較項目 | ナチュラルキー | サロゲートキー |
|---|---|---|
| 例 | メールアドレス、ISBN | 自動連番ID、UUID |
| 意味を持つ？ | 持つ | 持たない |
| 値の変更リスク | あり(メール変更など) | 基本なし |
| 推奨用途 | 小規模・読みやすさ重視 | 拡張性・運用重視 |
非正規化
現場では正規化だけでは限界がある
| 正規化の限界点 | 例 |
|---|---|
| JOINが多くなる | 複数テーブルを毎回結合 → SQLが複雑・遅い |
| データ取得が遅い | 特に読み取りが多い画面ではパフォーマンスに影響 |
| 設計が細かすぎる | シンプルな用途には過剰すぐて開発効率が悪化 |
非正規化とは？
- あえて正規化を”ゆるめて”、扱いやすさ・速度を優先する設計手法
| パターン | よく使われる場面 |
|---|---|
| 表示高速化のための埋め込み | 商品名・著者名・カテゴリ名などの埋め込み |
| 集計の保持 | 合計金額・件数などのキャッシュ例 |
| 履歴としての複数保持 | 氏名・住所・メールなど当時の状態保持 |
非正規化の例
例１: 著者名を毎回JOINせずに、書籍テーブルに埋め込む
| 書籍ID | 書籍名 | 著者名 |
|---|---|---|
| 1 | SQL超入門 | 山田太郎 |
例2: 合計値・件数など別カラムで持っておく(冗長)
| 注文ID | ユーザーID | 商品数合計 | 金額合計 |
|---|---|---|---|
| 1001 | U001 | 5 | 8000 |
例3: 後の変更を考慮してその時点でのデータを保存
| 注文ID | ユーザID | 注文日 | 注文者氏名 | 注文者メール |
|---|---|---|---|---|
| 1001 | U001 | 25-10-01 | 田中太郎 | tanaka@example.com |
マスターテーブル VS トランザクションテーブル
マスターテーブルとは？
- データベース内で"基準"となる静的な情報を保持するテーブル
具体例
- ユーザーマスタ: ユーザーID、氏名、メールアドレス
- 商品マスタ: 商品ID、商品名、価格
- 都道府県マスタ: 都道府県コード、都道府県名
- カテゴリマスタ: カテゴリID、カテゴリ名
マスターテーブルは『名簿・辞書・設定表』
トランザクションテーブルとは？
- 現実の動き・出来事・操作の履歴を記録するテーブル
具体例
- 注文テーブル: 注文ID、ユーザーID、注文日
- 注文詳細テーブル: 注文ID、商品ID、数量
- アクセグログ: ユーザーID、アクセス日時、アクセスページ
- メッセージ送信履歴: 送信者ID、受信者ID、メッセージ内容、日時
トランザクションテーブルは『日誌・伝票・ログ』
設計のベストプラクティス
カラム設計
✅明示的な命名で誤解を防ぐ
- status → 意味が広すぎる → publication_status,account_statusのように明確に
- type → STIと衝突するので使わない → category_typeなどに明示的に
- そのほかdata,date,info,detailなどは抽象的すぎるかつ予約語と衝突する可能性があるので避ける
✅定義をコードに寄せすぎない
- enum role:{guest:0, member:1, admin:2}は便利だが、DBに制約がないと危険
- emumが増えそうな場合、ENUMをマスターテーブルとして分離する方が明確で拡張しやすい
- ActiveHashも同様で拡張性が低いため、マスターテーブルに切り出す方が吉
✅カラムの設計パターンはチームで揃える
- 『日付』は_at,『フラグ』は is_で始める
- 論理削除や状態が変わるような場合、フラグではなく日時を保存(is_deletedではなくdeleted_at)
参考サイト
【超入門】現場で通用する「プロが教えるテーブル設計！」
データベース設計入門#2 正規化｜無駄のないテーブル設計とは？【日本一わかりやすくDB正規化を解説します】