🛒 STEP 19: プロジェクト② EC設計【中級〜上級】

Amazon風ECサイトのデータベースを設計しよう

📋 このステップで学ぶこと

ECサイトの要件分析と複雑な設計
商品、カート、注文、在庫管理の設計
トランザクション処理の考慮
決済履歴と注文ステータス管理
レポート用スタースキーマ設計
在庫管理ロジックとデータ整合性

学習時間の目安： 3.5時間　｜　難易度： 中級〜上級

📋 1. ECサイトの要件定義

STEP 18からの続き：より複雑なシステムへ

STEP 18ではSNSのデータベース設計を学びました。このSTEP 19では、さらに複雑なECサイト（オンラインショップ）のデータベースを設計します。ECサイトはトランザクション処理、在庫管理、決済処理など、データの整合性が非常に重要なシステムです。Amazon風のフルスケールなECサイトを想定して設計しましょう。

主要機能

    🎯 ECサイトで必要な機能
    商品管理：商品情報、カテゴリ、画像、在庫
ユーザー管理：会員登録、配送先情報
カート機能：商品をカートに追加、数量変更
注文処理：注文確定、注文ステータス管理
決済処理：クレジットカード、銀行振込等
在庫管理：在庫数の管理、在庫切れ防止
レビュー機能：商品レビューと評価
レポート：売上分析、商品別売上

エンティティの洗い出し

エンティティ	説明	主な属性
users	顧客情報	ID、名前、メール、パスワード
addresses	配送先住所	ID、郵便番号、住所、電話番号
categories	商品カテゴリ	ID、カテゴリ名、親カテゴリID
products	商品情報	ID、商品名、価格、説明
product_images	商品画像	ID、商品ID、画像URL
inventory	在庫情報	商品ID、在庫数、倉庫ID
carts	カート	ID、ユーザーID
cart_items	カート内商品	カートID、商品ID、数量
orders	注文	ID、ユーザーID、合計金額、ステータス
order_items	注文明細	注文ID、商品ID、数量、単価
payments	決済履歴	ID、注文ID、決済方法、金額
reviews	商品レビュー	ID、商品ID、ユーザーID、評価

📐 2. ER図とテーブル設計

usersテーブル

CREATE TABLE users (
    — 主キー：ユーザーの一意識別子
    user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — ログイン用メールアドレス（重複不可）
    email VARCHAR(255) NOT NULL UNIQUE,
    
    — パスワードハッシュ（平文は絶対NG）
    password_hash VARCHAR(255) NOT NULL,
    
    — 名前：姓と名を分けて保存（日本語対応）
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    
    — 連絡先電話番号（任意）
    phone VARCHAR(20),
    
    — タイムスタンプ
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    — インデックス：メールでのログイン検索用
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

addressesテーブル

CREATE TABLE addresses (
    — 主キー：配送先の一意識別子
    address_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どのユーザーの住所か
    user_id BIGINT UNSIGNED NOT NULL,
    
    — 住所情報：日本の住所形式に対応
    postal_code VARCHAR(10) NOT NULL,     — 郵便番号（123-4567）
    prefecture VARCHAR(50) NOT NULL,      — 都道府県
    city VARCHAR(100) NOT NULL,           — 市区町村
    street VARCHAR(255) NOT NULL,         — 町名・番地
    building VARCHAR(100),                — 建物名・部屋番号（任意）
    
    — 配送先電話番号
    phone VARCHAR(20) NOT NULL,
    
    — デフォルト配送先フラグ
    is_default BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    — 外部キー：ユーザー削除時に住所も削除
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

categoriesテーブル（階層構造）

CREATE TABLE categories (
    — 主キー：カテゴリの一意識別子
    category_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — カテゴリ名（例：家電、パソコン、ノートPC）
    category_name VARCHAR(100) NOT NULL,
    
    — 親カテゴリID：自己参照で階層構造を表現
    — NULLなら最上位カテゴリ
    parent_category_id BIGINT UNSIGNED NULL,
    
    — 表示順序
    display_order INT UNSIGNED DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    — 外部キー：親カテゴリ削除時はNULLに
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    
    INDEX idx_parent (parent_category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

🔗 カテゴリの階層構造の例

家電 (parent_category_id = NULL)
  ├─ パソコン・周辺機器 (parent_category_id = 1)
  │   ├─ ノートPC (parent_category_id = 2)
  │   └─ デスクトップPC (parent_category_id = 2)
  └─ スマートフォン (parent_category_id = 1)

productsテーブル

CREATE TABLE products (
    — 主キー：商品の一意識別子
    product_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どのカテゴリに属するか
    category_id BIGINT UNSIGNED NOT NULL,
    
    — 商品情報
    product_name VARCHAR(255) NOT NULL,   — 商品名
    description TEXT,                      — 商品説明（長文対応）
    
    — 価格情報：DECIMAL型で正確な金額を保存
    price DECIMAL(10, 2) NOT NULL,        — 販売価格
    cost_price DECIMAL(10, 2),            — 原価（レポート・利益計算用）
    
    — 販売状態：FALSEなら非公開
    is_active BOOLEAN DEFAULT TRUE,
    
    — レビュー情報：非正規化（毎回計算より高速）
    average_rating DECIMAL(3, 2) DEFAULT 0.00,  — 平均評価（0.00〜5.00）
    reviews_count INT UNSIGNED DEFAULT 0,        — レビュー数
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    — 外部キー：カテゴリがある商品は削除禁止（RESTRICT）
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE RESTRICT,
    
    — インデックス：カテゴリ別・価格順・評価順の検索用
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    INDEX idx_rating (average_rating DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

💰 価格はDECIMAL型

DECIMAL(10, 2) = 最大8桁.小数点以下2桁
例：99999999.99（約1億円まで対応）
浮動小数点型（FLOAT, DOUBLE）は誤差が出るため金額には使わない！

product_imagesテーブル

CREATE TABLE product_images (
    — 主キー：画像の一意識別子
    image_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どの商品の画像か
    product_id BIGINT UNSIGNED NOT NULL,
    
    — 画像URL（外部ストレージの場合はCDN URL）
    image_url VARCHAR(500) NOT NULL,
    
    — 表示順序：小さい順に表示
    display_order INT UNSIGNED DEFAULT 0,
    
    — メイン画像フラグ：一覧表示用
    is_primary BOOLEAN DEFAULT FALSE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    — 外部キー：商品削除時に画像も削除
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

inventoryテーブル（在庫管理）

CREATE TABLE inventory (
    — 主キー：在庫レコードの一意識別子
    inventory_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どの商品の在庫か
    product_id BIGINT UNSIGNED NOT NULL,
    
    — 倉庫ロケーション（複数倉庫対応）
    warehouse_location VARCHAR(100) DEFAULT ‘main’,
    
    — 在庫数：実際の在庫数
    quantity INT UNSIGNED NOT NULL DEFAULT 0,
    
    — 予約数：カートに入れられて予約中の数
    — 利用可能在庫 = quantity – reserved_quantity
    reserved_quantity INT UNSIGNED DEFAULT 0,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    — 外部キー：商品削除時に在庫も削除
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    
    — ユニーク制約：同じ商品・倉庫の組み合わせは1レコードのみ
    UNIQUE KEY unique_product_warehouse (product_id, warehouse_location),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

🔒 在庫管理の重要性

quantity：実在庫数
reserved_quantity：カート内で予約中の数
利用可能在庫 = quantity – reserved_quantity
注文確定時に reserved_quantity を quantity から引く

cartsテーブル

CREATE TABLE carts (
    — 主キー：カートの一意識別子
    cart_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どのユーザーのカートか
    user_id BIGINT UNSIGNED NOT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    — 外部キー：ユーザー削除時にカートも削除
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    
    — ユニーク制約：1ユーザーにつき1カートのみ
    UNIQUE KEY unique_user_cart (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

cart_itemsテーブル

CREATE TABLE cart_items (
    — 主キー：カートアイテムの一意識別子
    cart_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どのカートに入っているか
    cart_id BIGINT UNSIGNED NOT NULL,
    
    — どの商品か
    product_id BIGINT UNSIGNED NOT NULL,
    
    — 数量
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    
    — カートに追加した日時
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    — 外部キー：カート・商品削除時に自動削除
    FOREIGN KEY (cart_id) REFERENCES carts(cart_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    
    — ユニーク制約：同じカートに同じ商品は1レコードのみ（数量で管理）
    UNIQUE KEY unique_cart_product (cart_id, product_id),
    INDEX idx_cart (cart_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ordersテーブル

CREATE TABLE orders (
    — 主キー：注文の一意識別子
    order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — 注文者
    user_id BIGINT UNSIGNED NOT NULL,
    
    — 配送先住所ID
    shipping_address_id BIGINT UNSIGNED NOT NULL,
    
    — 金額情報：DECIMAL型で正確に保存
    total_amount DECIMAL(10, 2) NOT NULL,     — 注文合計
    shipping_fee DECIMAL(10, 2) DEFAULT 0.00, — 送料
    tax_amount DECIMAL(10, 2) DEFAULT 0.00,   — 税額
    
    — 注文・支払いステータス：ENUM型で固定値のみ許可
    order_status ENUM(‘pending’, ‘confirmed’, ‘shipped’, ‘delivered’, ‘cancelled’) DEFAULT ‘pending’,
    payment_status ENUM(‘pending’, ‘paid’, ‘failed’, ‘refunded’) DEFAULT ‘pending’,
    
    — 各ステータスの日時
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  — 注文日時
    shipped_at TIMESTAMP NULL,                        — 発送日時
    delivered_at TIMESTAMP NULL,                      — 配達完了日時
    
    — 外部キー：RESTRICT=注文がある限りユーザー・住所は削除不可
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT,
    FOREIGN KEY (shipping_address_id) REFERENCES addresses(address_id) ON DELETE RESTRICT,
    
    INDEX idx_user (user_id),
    INDEX idx_status (order_status),
    INDEX idx_ordered_at (ordered_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

⚠️ ON DELETE RESTRICTの理由

注文情報は履歴として永続的に保存する必要があります。
ユーザーが退会しても、過去の注文履歴は残すため、RESTRICTにしています。

order_itemsテーブル

CREATE TABLE order_items (
    — 主キー：注文明細の一意識別子
    order_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どの注文の明細か
    order_id BIGINT UNSIGNED NOT NULL,
    
    — どの商品か
    product_id BIGINT UNSIGNED NOT NULL,
    
    — スナップショット：注文時点の情報を保存
    — 商品名・価格が後で変わっても注文履歴には影響しない
    product_name VARCHAR(255) NOT NULL,       — 注文時の商品名
    unit_price DECIMAL(10, 2) NOT NULL,       — 注文時の単価
    
    — 数量と小計
    quantity INT UNSIGNED NOT NULL,
    subtotal DECIMAL(10, 2) NOT NULL,         — 単価 × 数量
    
    — 外部キー
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT,
    
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

📸 スナップショット設計

product_nameとunit_priceは、注文時点の情報を保存します。
商品名や価格が後で変わっても、注文履歴には注文時の情報が残ります。

paymentsテーブル

CREATE TABLE payments (
    — 主キー：支払いの一意識別子
    payment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どの注文の支払いか
    order_id BIGINT UNSIGNED NOT NULL,
    
    — 支払い方法：ENUM型で固定値のみ許可
    payment_method ENUM(‘credit_card’, ‘bank_transfer’, ‘cash_on_delivery’, ‘paypal’) NOT NULL,
    
    — 支払い金額
    amount DECIMAL(10, 2) NOT NULL,
    
    — 支払いステータス
    payment_status ENUM(‘pending’, ‘completed’, ‘failed’, ‘refunded’) DEFAULT ‘pending’,
    
    — 決済サービスから返されるトランザクションID（照合用）
    transaction_id VARCHAR(255),
    
    — 支払い完了日時
    paid_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    — 外部キー：支払いがあるなら注文は削除不可
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE RESTRICT,
    
    INDEX idx_order (order_id),
    INDEX idx_transaction (transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

reviewsテーブル

CREATE TABLE reviews (
    — 主キー：レビューの一意識別子
    review_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    — どの商品へのレビューか
    product_id BIGINT UNSIGNED NOT NULL,
    
    — 誰が書いたレビューか
    user_id BIGINT UNSIGNED NOT NULL,
    
    — 購入済み確認用：この注文で購入した商品のレビュー
    order_id BIGINT UNSIGNED NOT NULL,
    
    — 評価：1〜5の整数（CHECK制約で範囲を強制）
    rating INT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5),
    
    — レビューコメント（任意）
    comment TEXT,
    
    — 購入確認済みフラグ（「購入済みユーザー」バッジ表示用）
    is_verified_purchase BOOLEAN DEFAULT TRUE,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    — 外部キー
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    
    — ユニーク制約：同じ注文で同じ商品に複数レビューは不可
    UNIQUE KEY unique_user_product_order (user_id, product_id, order_id),
    INDEX idx_product (product_id),
    INDEX idx_rating (rating DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

🔄 3. トランザクション処理の設計

注文確定の流れ

ECサイトで最も重要な注文処理は、複数のテーブルを更新するため、トランザクションで保護します。

START TRANSACTION;

— 1. カート内容を取得
SELECT ci.product_id, ci.quantity, p.price
FROM cart_items ci
INNER JOIN products p ON ci.product_id = p.product_id
WHERE ci.cart_id = 123;

— 2. 在庫チェック
SELECT product_id, (quantity – reserved_quantity) as available
FROM inventory
WHERE product_id IN (1, 2, 3)
FOR UPDATE;  — 行ロック

— 3. 在庫が足りない場合はROLLBACK
— （アプリケーション側で判断）

— 4. 注文を作成
INSERT INTO orders (user_id, shipping_address_id, total_amount, shipping_fee, tax_amount)
VALUES (10, 5, 15000.00, 500.00, 1500.00);

SET @order_id = LAST_INSERT_ID();

— 5. 注文明細を作成
INSERT INTO order_items (order_id, product_id, product_name, unit_price, quantity, subtotal)
SELECT @order_id, p.product_id, p.product_name, p.price, ci.quantity, p.price * ci.quantity
FROM cart_items ci
INNER JOIN products p ON ci.product_id = p.product_id
WHERE ci.cart_id = 123;

— 6. 在庫を減らす
UPDATE inventory i
INNER JOIN cart_items ci ON i.product_id = ci.product_id AND ci.cart_id = 123
SET i.quantity = i.quantity – ci.quantity,
    i.reserved_quantity = i.reserved_quantity – ci.quantity;

— 7. カートを空にする
DELETE FROM cart_items WHERE cart_id = 123;

COMMIT;

⚠️ トランザクション処理の注意点

FOR UPDATE：在庫確認時に行ロックをかける（同時注文対策）
在庫チェック：在庫不足ならROLLBACKする
スナップショット：注文時の商品名・価格を保存
デッドロック対策：テーブル更新順序を統一

📊 4. レポート用スタースキーマ設計

スタースキーマとは？

売上分析のために、OLTP（トランザクション処理）とは別に、OLAP（分析処理）用のテーブルを設計します。

【スタースキーマの構造】

        ┌────────────┐
        │ dim_date   │
        └────────────┘
              │
              │
        ┌─────┴──────┐
        │            │
┌───────┴────┐  ┌────┴────────┐  ┌─────────────┐
│ dim_product│  │ fact_sales  │  │ dim_customer│
└────────────┘  └─────────────┘  └─────────────┘
                      │
                ┌─────┴──────┐
                │            │
        ┌───────┴────┐  ┌────┴────────┐
        │ dim_region │  │ dim_payment │
        └────────────┘  └─────────────┘

fact_salesテーブル（ファクトテーブル）

CREATE TABLE fact_sales (
    sale_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    date_id INT UNSIGNED NOT NULL,  — 日付ディメンション
    product_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    region_id INT UNSIGNED,
    payment_method_id INT UNSIGNED,
    
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    sales_amount DECIMAL(10, 2) NOT NULL,  — 売上金額
    cost_amount DECIMAL(10, 2),  — 原価
    profit_amount DECIMAL(10, 2),  — 利益
    
    INDEX idx_date (date_id),
    INDEX idx_product (product_id),
    INDEX idx_customer (customer_id),
    INDEX idx_composite (date_id, product_id, customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

dim_dateテーブル（日付ディメンション）

CREATE TABLE dim_date (
    date_id INT UNSIGNED PRIMARY KEY,
    full_date DATE NOT NULL,
    year INT UNSIGNED NOT NULL,
    month INT UNSIGNED NOT NULL,
    day INT UNSIGNED NOT NULL,
    quarter INT UNSIGNED NOT NULL,
    day_of_week INT UNSIGNED NOT NULL,
    week_of_year INT UNSIGNED NOT NULL,
    is_weekend BOOLEAN DEFAULT FALSE,
    is_holiday BOOLEAN DEFAULT FALSE,
    
    INDEX idx_full_date (full_date),
    INDEX idx_year_month (year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

📅 日付ディメンションの例

date_id: 20240115
full_date: 2024-01-15
year: 2024
month: 1
day: 15
quarter: 1
day_of_week: 1 (月曜日)
week_of_year: 3
is_weekend: FALSE
is_holiday: FALSE

売上分析クエリ例

— 月別売上
SELECT 
    d.year,
    d.month,
    SUM(f.sales_amount) as total_sales,
    SUM(f.profit_amount) as total_profit
FROM fact_sales f
INNER JOIN dim_date d ON f.date_id = d.date_id
GROUP BY d.year, d.month
ORDER BY d.year DESC, d.month DESC;

— 商品別売上TOP10
SELECT 
    p.product_name,
    SUM(f.quantity) as total_quantity,
    SUM(f.sales_amount) as total_sales
FROM fact_sales f
INNER JOIN products p ON f.product_id = p.product_id
WHERE f.date_id >= 20240101 AND f.date_id <= 20241231
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC
LIMIT 10;

🔒 5. 在庫管理とデータ整合性

在庫ロック戦略

🛡️ 楽観的ロック vs 悲観的ロック

楽観的ロック（Optimistic Locking）

更新時にバージョン番号をチェック
競合が少ない場合に有効

ALTER TABLE inventory ADD COLUMN version INT UNSIGNED DEFAULT 0;

UPDATE inventory
SET quantity = quantity – 1,
    version = version + 1
WHERE product_id = 123 AND version = 5;  — バージョンチェック

悲観的ロック（Pessimistic Locking）

読み取り時に行ロック（FOR UPDATE）
確実だが、パフォーマンス低下

SELECT * FROM inventory 
WHERE product_id = 123 
FOR UPDATE;  — 行ロック

在庫不足の防止

— カート追加時に在庫チェック
INSERT INTO cart_items (cart_id, product_id, quantity)
SELECT 123, 456, 2
FROM inventory
WHERE product_id = 456 
  AND (quantity – reserved_quantity) >= 2;

— 在庫を予約
UPDATE inventory
SET reserved_quantity = reserved_quantity + 2
WHERE product_id = 456;

⚡ 6. パフォーマンス最適化

インデックス設計

— 商品検索用
CREATE INDEX idx_products_category_price ON products(category_id, price);
CREATE INDEX idx_products_active_rating ON products(is_active, average_rating DESC);

— 注文検索用
CREATE INDEX idx_orders_user_date ON orders(user_id, ordered_at DESC);
CREATE INDEX idx_orders_status ON orders(order_status, ordered_at DESC);

— 売上分析用（複合インデックス）
CREATE INDEX idx_fact_sales_analysis ON fact_sales(date_id, product_id, customer_id);

キャッシュ戦略

🚀 Redisを使ったキャッシュ例

商品詳細：30分キャッシュ（頻繁に見られる）
カート内容：リアルタイム（キャッシュしない）
人気商品ランキング：1時間キャッシュ
在庫数：5分キャッシュ（整合性重視）

読み取りレプリカの活用

【データベース構成】

Master DB（書き込み専用）
  ↓ レプリケーション
Replica DB（読み取り専用）

【使い分け】
✅ 商品検索、一覧表示 → Replica DB
✅ 注文処理、在庫更新 → Master DB
✅ 売上レポート → 専用のAnalytics DB

📝 7. STEP 19 のまとめ

✅ このステップで学んだこと

ECサイトの複雑な要件分析と設計
トランザクション処理の実装（注文確定の流れ）
在庫管理ロジックとデータ整合性
スナップショット設計（注文時の価格保存）
スタースキーマによるレポート設計
楽観的ロック/悲観的ロックの使い分け

💡 重要ポイント

ECサイトはデータ整合性が最重要（特に在庫・注文）
トランザクションを使って複数テーブルの更新を保護
スナップショットで過去の注文情報を正確に保持
DECIMAL型を使って金額の誤差をなくす
OLTPとOLAPを分離してパフォーマンス向上

🎯 次のステップへ

いよいよ最終ステップ！STEP 20では予約システムの設計に挑戦します。
時間軸の考慮やダブルブッキング防止など、さらに高度な設計を学びましょう。

📝 総合演習

演習 1 応用

クーポン機能を追加してください。

要件：

クーポンコードで割引（固定額または割引率）
有効期限と利用回数制限
特定の商品やカテゴリに限定可能

【解答】

CREATE TABLE coupons (
    coupon_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    coupon_code VARCHAR(50) NOT NULL UNIQUE,
    discount_type ENUM(‘fixed’, ‘percentage’) NOT NULL,
    discount_value DECIMAL(10, 2) NOT NULL,
    min_purchase_amount DECIMAL(10, 2) DEFAULT 0.00,
    max_discount_amount DECIMAL(10, 2),  — 割引率の場合の上限
    valid_from TIMESTAMP NOT NULL,
    valid_until TIMESTAMP NOT NULL,
    max_usage_count INT UNSIGNED,
    current_usage_count INT UNSIGNED DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_code (coupon_code),
    INDEX idx_valid (valid_from, valid_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE coupon_products (
    coupon_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    
    PRIMARY KEY (coupon_id, product_id),
    FOREIGN KEY (coupon_id) REFERENCES coupons(coupon_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_coupons (
    order_id BIGINT UNSIGNED NOT NULL,
    coupon_id BIGINT UNSIGNED NOT NULL,
    discount_amount DECIMAL(10, 2) NOT NULL,
    
    PRIMARY KEY (order_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (coupon_id) REFERENCES coupons(coupon_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

演習 2 発展

ポイントシステムを追加してください。

要件：

購入金額の1%がポイントとして付与
ポイントは次回注文で使用可能
ポイントの有効期限は1年間
ポイント履歴を記録

【解答】

— ユーザーのポイント残高
CREATE TABLE user_points (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    total_points INT UNSIGNED DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— ポイント履歴
CREATE TABLE point_transactions (
    transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED,
    transaction_type ENUM(‘earn’, ‘use’, ‘expire’) NOT NULL,
    points INT NOT NULL,  — 獲得:+、使用:-
    balance_after INT UNSIGNED NOT NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE SET NULL,
    
    INDEX idx_user_date (user_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— ポイント獲得処理（注文完了時）
DELIMITER //
CREATE TRIGGER after_order_complete
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    DECLARE earned_points INT;
    
    IF NEW.order_status = ‘delivered’ AND OLD.order_status <> ‘delivered’ THEN
        — 1%をポイント付与（100円=1ポイント）
        SET earned_points = FLOOR(NEW.total_amount / 100);
        
        — ポイント残高を更新
        INSERT INTO user_points (user_id, total_points)
        VALUES (NEW.user_id, earned_points)
        ON DUPLICATE KEY UPDATE total_points = total_points + earned_points;
        
        — ポイント履歴を記録
        INSERT INTO point_transactions (user_id, order_id, transaction_type, points, balance_after, expires_at)
        SELECT NEW.user_id, NEW.order_id, ‘earn’, earned_points, total_points, DATE_ADD(NOW(), INTERVAL 1 YEAR)
        FROM user_points
        WHERE user_id = NEW.user_id;
    END IF;
END //
DELIMITER ;

演習 3 発展

ウィッシュリスト（お気に入り）機能を追加してください。

要件：

ユーザーが商品をウィッシュリストに追加できる
複数のウィッシュリストを作成可能（例：「誕生日」「クリスマス」）
ウィッシュリストを公開/非公開に設定可能

【解答】

— ウィッシュリスト
CREATE TABLE wishlists (
    wishlist_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    wishlist_name VARCHAR(100) NOT NULL DEFAULT ‘デフォルト’,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    
    INDEX idx_user (user_id),
    INDEX idx_public (is_public)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— ウィッシュリスト商品
CREATE TABLE wishlist_items (
    wishlist_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wishlist_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    priority INT UNSIGNED DEFAULT 0,  — 優先度
    note TEXT,  — メモ
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (wishlist_id) REFERENCES wishlists(wishlist_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_wishlist_product (wishlist_id, product_id),
    INDEX idx_wishlist (wishlist_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— ウィッシュリスト取得クエリ
SELECT 
    wl.wishlist_name,
    p.product_id,
    p.product_name,
    p.price,
    pi.image_url,
    wi.priority,
    wi.note
FROM wishlists wl
INNER JOIN wishlist_items wi ON wl.wishlist_id = wi.wishlist_id
INNER JOIN products p ON wi.product_id = p.product_id
LEFT JOIN product_images pi ON p.product_id = pi.product_id AND pi.is_primary = TRUE
WHERE wl.user_id = 1
ORDER BY wl.wishlist_name, wi.priority DESC, wi.added_at DESC;

❓ よくある質問

Q1: なぜ注文テーブルに商品名と価格を保存するのですか？

注文時点のスナップショットを残すためです。商品名や価格は後から変更される可能性があります。もし注文テーブルが商品テーブルを参照するだけだと、「あのとき3,000円で買ったはずなのに、今見たら5,000円になっている」という事態が起きます。過去の注文履歴を正確に保持するために、注文時の情報をスナップショットとして保存します。

Q2: 在庫管理でreserved_quantityを使う理由は？

カート内での仮押さえを管理するためです。ユーザーがカートに商品を追加した時点で在庫を「予約」し、他のユーザーが同時に購入しようとした際の在庫切れを防ぎます。ただし、カートに入れたまま放置されるケースもあるため、一定時間経過後に予約を解除する仕組み（カート有効期限）も必要です。

Q3: カテゴリの階層構造で「閉包テーブル」を使わないのはなぜ？

シンプルさを優先したためです。閉包テーブル（Closure Table）パターンを使うと、任意の深さの階層を効率的に取得できます。しかし、ECサイトのカテゴリは通常3〜4階層程度なので、自己参照（parent_category_id）で十分対応できます。深い階層が必要な場合や、頻繁に祖先・子孫を取得する場合は閉包テーブルを検討してください。

Q4: 楽観的ロックと悲観的ロックはどちらを使うべき？

競合の頻度によります。ECサイトの在庫管理では、同じ商品を同時に購入するケースが多い（人気商品のセールなど）場合は悲観的ロック（FOR UPDATE）が安全です。一般的な商品で競合が少ない場合は、楽観的ロック（バージョン番号）の方がパフォーマンスが良いです。実務では、商品の人気度に応じて使い分けることもあります。

Q5: OLTP用とOLAP用のデータベースを分ける必要がありますか？

規模によります。小規模なECサイトでは、同じデータベース上でレポート用のビューを作成するだけで十分です。しかし、注文数が増えてレポートクエリが重くなると、本番のトランザクション処理に影響が出ます。その場合は、レプリカDBを使う、または専用のデータウェアハウス（Amazon Redshift、BigQueryなど）を構築することを検討します。

Q6: 消費税の計算はどこで行うべきですか？

注文確定時にアプリケーション側で計算し、DBに保存します。消費税率は法改正で変わる可能性があるため、注文時点の税率と税額を order テーブルに保存しておくことが重要です。また、軽減税率（食品は8%、それ以外は10%など）に対応する場合は、商品ごとに税率を持たせる設計も検討が必要です。

📝

学習メモ

データベース設計・データモデリング - Step 19

📋 過去のメモ一覧 ▼