ストーリー

データモデリングとは何か

データモデリングはシステムが扱うデータの構造、関係、制約を定義するプロセスです。適切なデータモデルは、システムの保守性、パフォーマンス、拡張性に直結します。

正規化の原則

第1正規形（1NF）

ルール: 各カラムが原子的（これ以上分解できない）値を持つこと。繰り返しグループを排除する。

-- 違反例: タグが1カラムに複数値
CREATE TABLE products_bad (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  tags VARCHAR(255)  -- "electronics,sale,new" のようなCSV
);

-- 1NF準拠: 繰り返しを別テーブルに分離
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE product_tags (
  product_id INT REFERENCES products(id),
  tag VARCHAR(50) NOT NULL,
  PRIMARY KEY (product_id, tag)
);

第2正規形（2NF）

ルール: 1NFを満たし、かつ部分関数従属を排除する（複合主キーの一部にのみ依存するカラムを除去）。

-- 違反例: supplier_name は supplier_id にのみ依存
CREATE TABLE order_items_bad (
  order_id INT,
  product_id INT,
  supplier_id INT,
  supplier_name VARCHAR(255),  -- supplier_id にのみ依存（部分関数従属）
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

-- 2NF準拠: supplier を分離
CREATE TABLE suppliers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  supplier_id INT REFERENCES suppliers(id),
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

第3正規形（3NF）

ルール: 2NFを満たし、かつ推移的関数従属を排除する（非キー属性が別の非キー属性に依存しない）。

-- 違反例: city が postal_code に推移的に依存
CREATE TABLE customers_bad (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  postal_code VARCHAR(10),
  city VARCHAR(100),       -- postal_code → city の推移的依存
  prefecture VARCHAR(50)   -- postal_code → prefecture の推移的依存
);

-- 3NF準拠: 住所情報を分離
CREATE TABLE postal_areas (
  postal_code VARCHAR(10) PRIMARY KEY,
  city VARCHAR(100) NOT NULL,
  prefecture VARCHAR(50) NOT NULL
);

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  postal_code VARCHAR(10) REFERENCES postal_areas(postal_code)
);

ER モデリングの実践

エンティティの種類

リレーションシップの表現

// TypeScript + Prisma でのリレーション定義
// schema.prisma

// 1:N リレーション
model Customer {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  orders    Order[]  // 1人の顧客が複数の注文を持つ
}

model Order {
  id         Int         @id @default(autoincrement())
  orderDate  DateTime    @default(now())
  status     OrderStatus @default(PENDING)
  customerId Int
  customer   Customer    @relation(fields: [customerId], references: [id])
  items      OrderItem[]
}

// 多対多リレーション（中間テーブル）
model Product {
  id         Int           @id @default(autoincrement())
  name       String
  categories CategoryProduct[]
}

model Category {
  id       Int               @id @default(autoincrement())
  name     String            @unique
  products CategoryProduct[]
}

model CategoryProduct {
  productId  Int
  categoryId Int
  product    Product  @relation(fields: [productId], references: [id])
  category   Category @relation(fields: [categoryId], references: [id])
  @@id([productId, categoryId])
}

非正規化のトレードオフ

正規化は冗長性を排除しデータ整合性を保証しますが、パフォーマンスとのトレードオフがあります。

いつ非正規化すべきか

非正規化パターン

-- パターン1: 計算済みカラム
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2);

-- トリガーで自動更新
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE orders
  SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = NEW.order_id
  )
  WHERE id = NEW.order_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

-- パターン2: マテリアライズドビュー（分析用）
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
  DATE_TRUNC('month', o.order_date) AS month,
  p.category_id,
  c.name AS category_name,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(oi.quantity) AS total_quantity,
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
GROUP BY 1, 2, 3;

-- 定期的にリフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

まとめ

チェックリスト

• 1NF、2NF、3NFの各ルールを説明できる
• ER図を用いて概念/論理モデルを設計できる
• 非正規化が有効な場面と注意点を理解した
• マテリアライズドビューの活用方法を理解した

次のステップへ

次はNoSQLデータモデリングの手法を学び、RDBだけでは解決できないユースケースへの対応方法を理解します。

推定読了時間: 30分