データモデリング - 効率的なデータベース設計

データモデリングの3段階

ER図の基本

erDiagram
    User ||--o{ Order : places
    Order ||--|{ OrderItem : contains
    Product ||--o{ OrderItem : "included in"
    Category ||--o{ Product : contains

    User {
        uuid id PK
        string email UK
        string name
        datetime created_at
    }

    Order {
        uuid id PK
        uuid user_id FK
        decimal total_amount
        string status
        datetime ordered_at
    }

    OrderItem {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        int quantity
        decimal unit_price
    }

    Product {
        uuid id PK
        uuid category_id FK
        string name
        decimal price
        int stock
    }

正規化

第1正規形（1NF）

繰り返しグループを排除し、各カラムは原子値のみを持つ。

-- 違反例
CREATE TABLE orders (
  id INT,
  products TEXT  -- 'Product A, Product B, Product C'
);

-- 1NF適用後
CREATE TABLE orders (
  id INT PRIMARY KEY
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_name TEXT
);

第2正規形（2NF）

部分関数従属を排除（複合キーの一部への依存をなくす）。

-- 違反例（product_nameはproduct_idのみに依存）
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  product_name TEXT,  -- 部分従属
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

-- 2NF適用後
CREATE TABLE products (
  id INT PRIMARY KEY,
  name TEXT
);

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

第3正規形（3NF）

推移的関数従属を排除。

-- 違反例（category_nameはcategory_idを経由して依存）
CREATE TABLE products (
  id INT PRIMARY KEY,
  name TEXT,
  category_id INT,
  category_name TEXT  -- 推移的従属
);

-- 3NF適用後
CREATE TABLE categories (
  id INT PRIMARY KEY,
  name TEXT
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name TEXT,
  category_id INT REFERENCES categories(id)
);

非正規化

パフォーマンスのために意図的に正規化を崩すケース。

-- 正規化（JOINが必要）
SELECT o.id, u.name, o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id;

-- 非正規化（読み取り高速化）
CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  user_name TEXT,  -- 非正規化フィールド
  total_amount DECIMAL
);

-- トレードオフ
-- ✅ 読み取りが高速
-- ❌ データ更新時に複数箇所を変更
-- ❌ データ不整合のリスク

非正規化パターン

// 集計値の保持
interface Product {
  id: string;
  name: string;
  reviewCount: number;     // 非正規化
  averageRating: number;   // 非正規化
}

// レビュー追加時に更新
async function addReview(productId: string, rating: number) {
  await db.transaction(async (tx) => {
    // レビュー追加
    await tx.review.create({ productId, rating });

    // 集計値を更新
    const stats = await tx.review.aggregate({
      where: { productId },
      _avg: { rating: true },
      _count: true,
    });

    await tx.product.update({
      where: { id: productId },
      data: {
        reviewCount: stats._count,
        averageRating: stats._avg.rating,
      },
    });
  });
}

ドメイン駆動設計との連携

集約ルート

// 集約ルート: Order
interface Order {
  id: OrderId;
  customerId: CustomerId;
  items: OrderItem[];  // 集約内のエンティティ
  status: OrderStatus;

  addItem(product: Product, quantity: number): void;
  removeItem(itemId: OrderItemId): void;
  calculateTotal(): Money;
}

// リポジトリは集約単位で操作
interface OrderRepository {
  findById(id: OrderId): Promise<Order | null>;
  save(order: Order): Promise<void>;
}

値オブジェクト

class Money {
  constructor(
    private readonly amount: number,
    private readonly currency: string
  ) {
    if (amount < 0) throw new Error('Amount cannot be negative');
  }

  add(other: Money): Money {
    if (this.currency !== other.currency) {
      throw new Error('Currency mismatch');
    }
    return new Money(this.amount + other.amount, this.currency);
  }

  equals(other: Money): boolean {
    return this.amount === other.amount && this.currency === other.currency;
  }
}

スキーマ設計パターン

継承パターン

-- シングルテーブル継承
CREATE TABLE payments (
  id UUID PRIMARY KEY,
  type VARCHAR(20),  -- 'credit_card', 'bank_transfer', 'paypal'
  amount DECIMAL,
  -- クレジットカード用
  card_number VARCHAR(16),
  card_expiry DATE,
  -- 銀行振込用
  bank_code VARCHAR(10),
  account_number VARCHAR(20),
  -- PayPal用
  paypal_email VARCHAR(255)
);

-- クラステーブル継承（推奨）
CREATE TABLE payments (
  id UUID PRIMARY KEY,
  type VARCHAR(20),
  amount DECIMAL
);

CREATE TABLE credit_card_payments (
  id UUID PRIMARY KEY REFERENCES payments(id),
  card_number VARCHAR(16),
  card_expiry DATE
);

CREATE TABLE bank_transfers (
  id UUID PRIMARY KEY REFERENCES payments(id),
  bank_code VARCHAR(10),
  account_number VARCHAR(20)
);

ソフトデリート

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255),
  deleted_at TIMESTAMP,  -- NULLなら有効

  CONSTRAINT uk_email UNIQUE (email) WHERE deleted_at IS NULL
);

-- 削除
UPDATE users SET deleted_at = NOW() WHERE id = '...';

-- 検索時
SELECT * FROM users WHERE deleted_at IS NULL;

関連記事

• SQL vs NoSQL - データベース選択
• データベースインデックス - パフォーマンス最適化
• ドメイン駆動設計 - DDD実践
• データベースレプリケーション - スケーリング

まとめ

データモデリングは、ビジネス要件の理解から始まり、正規化で整合性を確保し、必要に応じて非正規化でパフォーマンスを最適化します。ドメイン駆動設計と組み合わせることで、保守性の高い設計が実現できます。