LESSON 25分

ストーリー

「このテーブル、同じ住所が100行に重複してるぞ」

高橋アーキテクトがスキーマを見て眉をひそめた。住所を1箇所変更するために100行のUPDATEが走り、しかも一部が更新漏れで不整合を起こしていた。

「正規化は『重複をなくす技術』だ。理論を知れば、こういう問題は設計段階で防げる」

正規化とは

正規化（Normalization）は、データの冗長性を排除し、更新異常を防ぐためのテーブル分割技法。

更新異常の3つの種類

異常説明例
挿入異常不要なデータなしに挿入できない注文なしに顧客情報を登録できない
更新異常冗長データの一部だけ更新される顧客住所が一部の行だけ変更される
削除異常必要なデータが一緒に消える最後の注文を消すと顧客情報も消える

第一正規形（1NF）

条件: すべての属性が原子的（これ以上分解できない）な値を持つ

1NF違反の例

-- NG: 電話番号が複数値を持っている
CREATE TABLE customers_bad (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  phones VARCHAR(500)  -- "090-1234-5678, 080-9876-5432"
);

-- OK: 電話番号を別テーブルに分離
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE customer_phones (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  phone VARCHAR(20) NOT NULL,
  phone_type VARCHAR(20) -- 'mobile', 'home', 'work'
);

TypeScriptでの表現

// NG: 配列をカンマ区切り文字列で保持
interface CustomerBad {
  id: number;
  name: string;
  phones: string; // "090-xxxx, 080-xxxx"
}

// OK: 1NF準拠
interface Customer {
  id: number;
  name: string;
}

interface CustomerPhone {
  id: number;
  customerId: number;
  phone: string;
  phoneType: 'mobile' | 'home' | 'work';
}

第二正規形（2NF）

条件: 1NFを満たし、すべての非キー属性が主キー全体に完全関数従属する

複合主キーの場合に問題となる。主キーの一部にだけ依存する属性を分離する。

-- NG: 2NF違反（product_nameはproduct_idのみに依存）
CREATE TABLE order_items_bad (
  order_id INT,
  product_id INT,
  product_name VARCHAR(200),  -- product_idのみに依存（部分関数従属）
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

-- OK: 2NF準拠
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(200) NOT NULL
);

CREATE TABLE order_items (
  order_id INT,
  product_id INT REFERENCES products(id),
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

第三正規形（3NF）

条件: 2NFを満たし、非キー属性間の推移的関数従属がない

-- NG: 3NF違反（city_nameはzip_codeに依存し、zip_codeはidに依存 → 推移的依存）
CREATE TABLE customers_bad (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  zip_code VARCHAR(10),
  city_name VARCHAR(100)  -- zip_codeから決まる → 推移的関数従属
);

-- OK: 3NF準拠
CREATE TABLE zip_codes (
  zip_code VARCHAR(10) PRIMARY KEY,
  city_name VARCHAR(100) NOT NULL
);

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  zip_code VARCHAR(10) REFERENCES zip_codes(zip_code)
);

ボイス・コッド正規形（BCNF）

条件: すべての関数従属 X → Y において、Xが候補キーである

3NFとの違いは、非キー属性から候補キーの一部への関数従属も排除する点。

-- 例: 学生-科目-教員の関係
-- 制約: 1科目を担当する教員は1人、1教員は1科目のみ担当
-- {学生, 科目} → 教員 かつ 教員 → 科目

-- BCNF違反（教員 → 科目 だが、教員は候補キーでない）
CREATE TABLE enrollments_bad (
  student_id INT,
  subject VARCHAR(100),
  teacher VARCHAR(100),
  PRIMARY KEY (student_id, subject)
);

-- BCNF準拠: テーブルを分割
CREATE TABLE teacher_subjects (
  teacher_id INT PRIMARY KEY,
  subject VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE enrollments (
  student_id INT,
  teacher_id INT REFERENCES teacher_subjects(teacher_id),
  PRIMARY KEY (student_id, teacher_id)
);

非正規化の判断

正規化が常に正解ではない。以下の場合は意図的に非正規化する。

シチュエーション理由例
読み取り頻度が圧倒的に高いJOINコスト削減レポートテーブル
履歴を保持したい変更前の値を記録注文時の商品名・価格
集計値のキャッシュリアルタイム計算のコストフォロワー数、いいね数
-- 意図的な非正規化: 注文時点の商品情報を保存
CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  product_name_snapshot VARCHAR(200) NOT NULL,  -- 注文時の名前
  unit_price_snapshot DECIMAL(10,2) NOT NULL,   -- 注文時の価格
  quantity INT NOT NULL
);

まとめ

正規形条件排除するもの
1NF属性が原子的繰り返しグループ
2NF1NF + 完全関数従属部分関数従属
3NF2NF + 推移的関数従属なし推移的関数従属
BCNFすべての決定子が候補キー非キーから候補キーへの依存

理解度チェックリスト

  • 更新異常の3種類（挿入・更新・削除）を説明できる
  • 1NF、2NF、3NFの条件をそれぞれ説明できる
  • BCNFと3NFの違いを理解している
  • 非正規化が適切なケースを判断できる

次のステップ

次はデータモデルを視覚的に表現するER図の読み書きを学ぶ。概念モデルから論理モデルへの変換技法を身につけよう。

推定読了時間: 25分