アルアカ - Arcadia Academia

アプリの成長とともにDBは肥大化し、同じ情報があちこちに重複、更新漏れや参照のねじれ、レポートの数字が毎回違う……。そのほとんどは「設計段階での構造の歪み」に起因します。歪みを取り除くための王道テクニックが正規化です。この記事では、現場の開発者・データエンジニア・PdMが共通言語として使えるよう、第一正規形からBCNFまでを、実務で遭遇する例を通して噛み砕いて解説。

正規化の目的はシンプルです。データの重複を減らし、更新時の整合性を守り、問い合わせの意味が一貫するようにすること。数式にすると難しく見えますが、要は「更新・追加・削除のときに困らない形に整える」作業です。

更新時の困りごとは典型的に三つに分類できます。

1. 更新の不整合：同じ情報が複数の行・列にあり、片方だけ更新して破綻する
2. 追加の不具合：一部の情報がないと行が追加できない
3. 削除の副作用：ある情報を消したいのに、関係ない情報まで消えてしまう

正規化は、これらの異常を避けるために「属性のまとまり」と「キーへの依存」を整理していきます。

• 関係（テーブル）：同種の行の集合
• 属性（カラム）：列。属性は意味のまとまり
• 候補キー：行を一意に識別する最小の属性集合
• 主キー：候補キーのうち採用するキー
• 関数従属性：Xが決まればYも一意に決まる、という依存関係 X→Y

以降、これらを使って各正規形を見ていきます。

1NFの要点は「カラムは分解可能でない単一値」かつ「繰り返し属性を持たない」こと。

悪い例：注文テーブルに購入商品IDsをカンマ区切りで詰め込む、住所を1列に全部入れる。

良い例：注文と明細を分ける、住所は都道府県・市区町村・番地などに分離（必要な粒度で）。

実務Tips

• 文字列に配列を埋めない。可変長の繰り返しは子テーブルへ。
• JSON列を使う場合も、検索・集計・更新の要件を洗い出し、正規化テーブルに落とせる部分は落とす。

複合主キーを持つテーブルで起こる「部分関数従属性」をなくすことが2NF。

例：受注明細テーブルの主キーが（注文ID, 商品ID）で、商品名や標準価格を同居させると、商品IDだけで決まる属性が混ざり更新異常を招きます。商品情報は商品テーブルに分離しましょう。

実務Tips

• 複合主キーのテーブルを見つけたら「各属性は主キーの全体に依存しているか？」をチェック。
• 片側のキーだけで決まる属性は、親テーブルへ移動。

3NFは「非キー属性が他の非キー属性に依存していない」状態。

例：顧客テーブルに（顧客ID, 郵便番号, 住所, 市区町村）のように、郵便番号→市区町村→住所と連鎖的に決まる属性が並ぶと、郵便番号変更時に整合性が崩れます。郵便番号辞書テーブルを用意し、顧客は郵便番号のみ保持する、などで依存を一箇所に集約します。

実務Tips

• 「Aを直せばBも必ず直す」関係が非キー間にあれば分割候補。
• マスタ辞書化のコストとAPI連携の整備を天秤にかける。

3NFをさらに厳格にしたのがBCNF。

例：大学の「受講」テーブルで、講師と科目の組み合わせが必ず一意に講義室を決める、かつ（学生ID, 科目ID）が主キーのとき、科目ID→講義室という決定が主キーでない要因に存在。これを分解し、科目と講義室の対応を別テーブルに持ち、受講からは参照する設計へ。

実務Tips

• 一意制約やユニークインデックスを見直し、「テーブル内の一意制約から派生する決定要因」が候補キーか確認。
• BCNFで分割しすぎると結合が増える。アクセスパターンの頻度とJOINコストを測る。

多値従属性の排除がテーマ。

例：ユーザが複数の言語と複数のデバイスを持つ場合、ユーザテーブルに言語とデバイスを同居させると、言語×デバイスの直積が発生。ユーザ言語、ユーザデバイスを分離するのが定石。

初期スキーマ（悪い例）

注文テーブル

• 注文ID
• 顧客名
• 顧客メール
• 住所
• 商品IDs（カンマ区切り）
• 合計金額

問題点

• 1NF違反：商品IDsが繰り返し属性
• 2NF違反：商品名や標準価格を入れたくなる誘惑
• 3NF違反：住所を1列、メールからドメインを導出、など

正しい分割の一例

• 顧客（顧客ID, 名前, メール, 郵便番号, 住所行1, 住所行2）
• 注文（注文ID, 顧客ID, 注文日時, 請求先ID, 配送先ID, 状態）
• 注文明細（注文ID, 行番号, 商品ID, 数量, 単価, 税率ID, 値引ID）
• 商品（商品ID, 商品名, 標準価格, カテゴリID, 状態）
• マスタ群（税率, 値引, カテゴリ など）

段階の確認方法

1. 繰り返し・配列状データを子テーブルへ（1NF）
2. 複合キーの片側で決まる属性が混ざっていないか（2NF）
3. 非キー間の派生・辞書依存を分離（3NF）
4. 候補キー以外の決定要因を抽出して別関係に（BCNF）
5. 多値従属性の直積を排除（4NF）

正規化を進めるほどテーブル数は増え、問い合わせはJOIN中心になります。パフォーマンスや可観測性、アプリ側の複雑性とトレードオフです。実務では以下の条件で「意図的な非正規化」を検討します。

• 読み取りが圧倒的多数で、更新は限定的
• サマリ値（合計、件数、最新日時）を即時に表示したい
• 時系列スナップショットを保持して監査を容易にしたい
• データウェアハウスやレイクハウスで事実テーブルを高速参照したい

代表的な非正規化テクニック

• 集計値の冗長保持（例：注文合計金額を行動トリガで更新）
• マテリアライズドビューの活用
• スター・スキーマ（ディメンジョンの非正規化）
• 重複を許容したイベントソーシングと後段の正規化

注意点

• 冗長化した値は「真実の出どころ」を決める。更新経路を1つに。
• 監査用と運用用でスキーマを分ける。
• インデックスとトランザクション分離レベルの調整で読み取り性能を確保。

正規化はスキーマの話ですが、RDBの制約機能を組み合わせて初めて効果を発揮します。

• 主キー・ユニーク制約：候補キーの実装。自然キーとサロゲートキーの選択基準を整理。
  • 自然キー：外部システムと共有する識別子が安定しているとき適合
  • サロゲートキー：UUIDやシーケンス。内部結合やシャーディングで有利
• 外部キー制約：参照整合性を担保。ON DELETE/UPDATEの挙動を明示。
• チェック制約：値域やフォーマットのバリデーションをDB側で保証。
• インデックス：正規化で増えたJOINのコストを相殺。結合キーとフィルタ列を中心に設計。
• パーティショニング：時系列の巨大テーブルを扱う際の武器。保管と検索の両面で効く。

ドキュメントDBやキーバリューストアでは、アクセスパターン最適化のために「非正規化」が基本戦略になることがあります。ただし、概念としての正規化は依然重要です。理由は二つ。

1. ドメインの実体・関係を明示できるため、サービス間の契約やイベント設計が堅くなる
2. データウェアハウスへデータを落とす際、正規形のモデルに変換しやすい

つまり、永続層が何であれ「正規化的な思考」を持ってスキーマやドキュメント構造を設計するのが現実解です。

1. ユースケースと問い合わせ列挙
   • 画面・API・バッチ・分析の観点で、必要な検索・集計・更新を洗い出す
2. 実体と関係の発見
   • 名詞・動詞から候補を抽出。ER図で粗く整理
3. 候補キーの決定
   • 一意性の根拠を確認。自然キーが不安定ならサロゲート
4. 属性の粒度を検討（1NF）
   • 原子化と繰り返しの分離。住所・名前・タグなど境界に注意
5. 依存の分析（2NF/3NF/BCNF）
   • 部分従属や非キー間依存、候補キー以外の決定要因を洗い出す
6. 性能・運用要件と合わせて非正規化の計画
   • 冗長値、マテビュー、イベントソース、キャッシュ
7. 制約・インデックス・トランザクション設計
   • 参照整合性、整合性ルール、ロック戦略を明文化
8. マイグレーション戦略
   • 段階移行、バックフィル、読み書き分離、ロールバック手順

• 配列やカンマ区切りの列がないか（1NF）
• 複合主キーの片側で決まる属性が混入していないか（2NF）
• 非キー属性同士の依存がないか（3NF）
• 候補キー以外の決定要因はテーブル外に出せているか（BCNF）
• 多値従属性の直積が発生していないか（4NF）
• 更新系と参照系のボトルネックに対して索引と冗長化の戦略があるか
• 制約がアプリのバリデーションと二重になっていないか
• 監査・ロールバックに耐えられる履歴管理やトレーサビリティがあるか

• 正規化はパフォーマンスを落とすだけではない
  • 整合性維持と冗長更新の削減で、むしろ運用コストを下げる場合が多い
• 3NFにすれば十分という定説
  • ドメイン次第。時間割・予約・割当のような制約が強い領域ではBCNFの意義が大きい
• 正規化と非正規化は二者択一
  • 設計段階で正規化し、必要に応じて意識的に冗長化するのが基本。順番が大事

顧客と注文・明細の最小構成を、正規化発想で定義します。

create table customers (
  customer_id    bigint primary key,
  name           varchar(200) not null,
  email          varchar(255) not null unique,
  postal_code    varchar(16),
  address_line1  varchar(255),
  address_line2  varchar(255),
  created_at     timestamp not null default now()
);

create table products (
  product_id   bigint primary key,
  name         varchar(200) not null,
  std_price    numeric(12,2) not null,
  category_id  bigint,
  status       varchar(32) not null
);

create table orders (
  order_id     bigint primary key,
  customer_id  bigint not null references customers(customer_id),
  ordered_at   timestamp not null default now(),
  bill_to_id   bigint references customers(customer_id),
  ship_to_id   bigint references customers(customer_id),
  status       varchar(32) not null
);

create table order_items (
  order_id     bigint not null references orders(order_id) on delete cascade,
  line_no      int not null,
  product_id   bigint not null references products(product_id),
  qty          int not null check (qty > 0),
  unit_price   numeric(12,2) not null,
  tax_rate_id  bigint,
  discount_id  bigint,
  primary key (order_id, line_no)
);

この定義は、1NF（原子化）、2NF（明細の複合キー全面依存）、3NF（非キー依存の排除）を満たしつつ、参照整合性とユニーク性を制約で担保しています。合計金額はビューやマテビューで提供し、必要なら非正規化列としてキャッシュする設計が現実的です。

正規化は学術的な儀式ではなく、変更に強いシステムを作るための「運用コスト最適化手段」です。1NFで原子化、2NFで部分従属の排除、3NFで非キー依存の断絶、BCNFで決定要因を候補キーに限定、4NFで直積の罠を回避。ここまで落とし込んだうえで、業務要件・性能要件に応じて意図的な非正規化を加える。これが現場で長持ちするDB設計の王道です。まずは既存スキーマをこの観点で点検し、小さな改善から始めていきましょう。