ECサイトを設計する（実践）

これまで学んだ設計理論を実践に活かす時間です。今回はECサイトを題材に、要件整理からER図、CREATE TABLE 文まで一気通貫で設計します。

要件整理

今回設計するECサイトの要件は次のとおりです。

ユーザー管理:

• 会員登録・ログインができる
• 複数の配送先住所を登録できる

商品管理:

• 商品はカテゴリに属する（カテゴリは階層なし）
• 商品には在庫数がある
• 商品にユーザーがレビュー（評価＋コメント）を書ける

購買フロー:

• カートに商品を追加できる
• カートから注文を確定できる
• 注文には使用した配送先住所が記録される
• 注文後に商品価格が変わっても注文時の価格は変わらない

これらの要件から、以下の8テーブルが必要と判断できます。

users, addresses, categories, products, carts, orders, order_items, reviews

完全なER図

CREATE TABLE 文

users テーブル

CREATE TABLE users (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  email      VARCHAR(255) NOT NULL,
  password   VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_email (email)
);

addresses テーブル

CREATE TABLE addresses (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  postal_code VARCHAR(8) NOT NULL,
  prefecture  VARCHAR(10) NOT NULL,
  city        VARCHAR(100) NOT NULL,
  address1    VARCHAR(255) NOT NULL,
  address2    VARCHAR(255),
  is_default  TINYINT(1) NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_addresses_users FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  KEY idx_addresses_user_id (user_id)
);

is_default フラグで「デフォルト住所」を管理します。アプリケーション側で「1ユーザーにデフォルト住所は1つ」を保証します。

categories テーブル

CREATE TABLE categories (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  slug       VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_categories_slug (slug)
);

slug はURL用の識別子（例: mens-shirts）です。URLに使うため UNIQUE 制約をかけます。

products テーブル

CREATE TABLE products (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id BIGINT UNSIGNED,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  price       INT UNSIGNED NOT NULL,
  stock       INT UNSIGNED NOT NULL DEFAULT 0,
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_products_categories
    FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL,
  KEY idx_products_category_id (category_id)
);

カテゴリが削除されても商品は残したいため ON DELETE SET NULL を使います。

carts テーブル

CREATE TABLE carts (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity   INT UNSIGNED NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_carts_users    FOREIGN KEY (user_id)    REFERENCES users (id)    ON DELETE CASCADE,
  CONSTRAINT fk_carts_products FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
  UNIQUE KEY uq_carts_user_product (user_id, product_id)
);

UNIQUE KEY uq_carts_user_product により、同じユーザーが同じ商品を2行追加するのを防ぎます。

orders テーブル

CREATE TABLE orders (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id           BIGINT UNSIGNED NOT NULL,
  shipping_address  TEXT NOT NULL,  -- 注文時の住所スナップショット（JSON形式）
  total_price       INT UNSIGNED NOT NULL,
  status            ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending',
  ordered_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE RESTRICT,
  KEY idx_orders_user_id (user_id),
  KEY idx_orders_status (status)
);

shipping_address はJSON文字列で住所情報を保持します。addresses テーブルへの外部キーではなく、注文時の住所をスナップショットとして保存することで、後から住所が変更・削除されても注文の配送先が変わりません。

order_items テーブル

CREATE TABLE order_items (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity   INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price INT UNSIGNED NOT NULL,  -- 注文時の価格スナップショット
  CONSTRAINT fk_order_items_orders   FOREIGN KEY (order_id)   REFERENCES orders (id)   ON DELETE CASCADE,
  CONSTRAINT fk_order_items_products FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT,
  UNIQUE KEY uq_order_items_order_product (order_id, product_id),
  KEY idx_order_items_product_id (product_id)
);

reviews テーブル

CREATE TABLE reviews (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  rating     TINYINT UNSIGNED NOT NULL,  -- 1〜5
  comment    TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reviews_users    FOREIGN KEY (user_id)    REFERENCES users (id)    ON DELETE CASCADE,
  CONSTRAINT fk_reviews_products FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE,
  UNIQUE KEY uq_reviews_user_product (user_id, product_id),  -- 1ユーザー1商品につき1レビュー
  KEY idx_reviews_product_id (product_id)
);

インデックスの設計

適切な インデックス? テーブルの検索を高速化するためのデータ構造。本の索引のように、特定の列の値から行の位置をすばやく特定できる。検索は速くなるが、書き込みはやや遅くなる。 設計はクエリのパフォーマンスに直結します。今回設計したインデックスの根拠を説明します。

テーブル	インデックス	理由
users	email (UNIQUE)	ログイン時の検索、メール重複チェック
addresses	user_id	ユーザーの住所一覧取得
products	category_id	カテゴリ別商品一覧
orders	user_id	購入履歴表示
orders	status	管理画面での注文ステータス絞り込み
order_items	product_id	商品の注文実績確認
reviews	product_id	商品詳細ページのレビュー取得

インデックスの注意点: インデックスは読み取りを速くする一方、書き込みを遅くします。不要なインデックスは貼らないようにしましょう。

設計判断のまとめ

今回の設計で行ったトレードオフをまとめます。

1. 住所のスナップショット — orders.shipping_address を TEXT（JSON）にし、addresses への外部キーを持たない。注文後の住所変更に対応するため
2. price のスナップショット — order_items.unit_price を持つ。商品価格変更後も注文時の価格を保持するため
3. ON DELETE SET NULL（categories） — カテゴリ削除でも商品は消えないよう。商品の category_id は NULL を許可
4. ENUM で status 管理 — 注文ステータスは有限のため ENUM で型安全に管理

まとめ

要件から設計に落とし込む際には、「このデータは後から変わりうるか」「このリレーションは本当に適切か」を常に問い続けることが大切です。設計には「唯一の正解」はなく、要件とトレードオフのバランスを取ることが求められます。

次回は、正規化された設計を意図的に崩す「非正規化」とパフォーマンスのトレードオフを学びます。