Day23 前半のゴール
「“ECサイトのDB設計”を、怖がらずに分解して考えられるようになる」
いよいよ設計演習です。テーマは「ECサイトのDB設計」。
いきなり完璧な設計を作る必要はありません。今日の前半のゴールはこうです。
ECサイトで「何を保存しないといけないか」を言葉で整理できる
テーブル候補（ユーザー・商品・注文…）を自分で挙げられる
正規化寄りの“きれいな形”を一度組み立ててみる
ここまで行けば、後半で「実務的な妥協（非正規化・パフォーマンス・履歴）」を混ぜていくときに、軸がブレません。
まずは要件を“日本語で”整理する
「テーブル名を考える前に、“どんな画面・機能があるか”を出す」
いきなりテーブル名を考え始めると、ほぼ確実に迷子になります。
先に「このECサイトは何ができるのか」を日本語で出していきます。
典型的なECサイトなら、だいたいこんな機能があります。
ユーザーが会員登録・ログインできる
商品一覧・商品詳細が見られる
カートに商品を入れて、注文できる
注文履歴が見られる
ここから、「DBに何を保存する必要があるか」を逆算します。
ユーザー情報（誰が注文したか）
商品情報（何を売っているか）
注文情報（いつ・誰が・いくらで買ったか）
注文明細（どの商品を何個買ったか）
この時点で、すでにテーブル候補が見えてきます。
users
products
orders
order_items
Day23 前半では、この4つを“骨格”として設計していきます。
コアテーブル1：ユーザー（users）
「“誰が買ったか”の軸になるテーブル」
ECサイトでは、「誰が何を買ったか」がすべての起点になります。
その「誰」を表すのが users テーブルです。
シンプルな定義のイメージはこうです。
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
) ENGINE=InnoDB;
ここで意識しておきたいポイントは二つです。
メールアドレスは UNIQUE にして「同じメールで複数アカウントを作れない」ようにする
パスワードは平文ではなくハッシュを保存する（セキュリティの最低ライン）
Day23 のテーマは設計ですが、セキュリティの観点も必ず絡みます。
「パスワードは絶対にそのまま保存しない」は、設計段階で決めておくべきルールです。
コアテーブル2：商品（products）
「“何を売っているか”を表すテーブル」
次に、「何を売るか」を表す products テーブルです。
シンプルな定義のイメージはこうです。
CREATE TABLE products (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
description TEXT,
price INT NOT NULL,
stock INT NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
) ENGINE=InnoDB;
ここでのポイントは、
price は「現在の販売価格」であり、後で変わる可能性がある
stock は「現在の在庫数」であり、注文ごとに変動する
ということです。
この時点ではまだ、「注文時の価格」「注文時点の在庫」といった“履歴”は考えません。
まずは「今の状態」を持つマスタとして products を置きます。
後半で、「注文時の価格をどこに持つか」という話に踏み込みます。
コアテーブル3：注文（orders）
「“1回の購入行為”を表すテーブル」
ユーザーが「カートの中身を確定して注文する」と、
1件の「注文」が発生します。
それを表すのが orders テーブルです。
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL,
total_amount INT NOT NULL,
status VARCHAR(20) NOT NULL,
ordered_at DATETIME NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
ここでの重要ポイントは二つです。
user_id で「誰の注文か」を紐づける（外部キー）
total_amount と status を持たせて、「注文全体の状態」を表す
status は例えば、
pending（受付中）
paid（支払い済み）
shipped（発送済み）
canceled（キャンセル）
のような値を想定できます。
total_amount を持つかどうかは設計の分かれ目です。
正規化だけを考えるなら、「明細の合計は毎回計算すればいい」とも言えますが、
実務では「注文時点の合計金額」を orders に持たせることが多いです。
ここは Day20 の「正規化と非正規化」と直結するポイントなので、
前半では「一旦持たせる」前提で進め、後半で理由を深掘りします。
コアテーブル4：注文明細（order_items）
「“どの商品を何個買ったか”を表すテーブル」
1件の注文の中には、複数の商品が含まれます。
それを表すのが order_items テーブルです。
CREATE TABLE order_items (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity INT NOT NULL,
unit_price INT NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id) REFERENCES orders(id),
CONSTRAINT fk_order_items_product
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;
ここで一番大事なのは、unit_price をどこから持ってくるか、です。
正規化だけを考えるなら、
価格は products.price にある
→ order_items には product_id と quantity だけ持てばいい
となります。
しかし、実務では、
注文時点の価格を unit_price として order_items にコピーする
ことがほぼ必須です。
なぜかというと、
商品価格が後から変わっても、過去の注文の金額は変わってはいけない
「当時はいくらで売っていたか」を正しく残す必要がある
からです。
ここは「履歴としての非正規化」の典型例で、
Day20 で話した「当時のスナップショットを残す」設計そのものです。
Day23 前半では、
order_items.unit_price は「注文時点の価格」であり、products.price とは別物
という意識だけ、しっかり持っておいてください。
ここまでの設計でできることを確認する
「よくある画面をSQLでイメージしてみる」
設計が“それっぽいかどうか”を確かめる一番いい方法は、
「よくある画面をSQLで書けるか」を試すことです。
例えば、「ユーザーの注文履歴一覧」を出したいとします。
ユーザー名
注文ID
注文日時
合計金額
を表示したいなら、こう書けます。
SELECT
o.id AS order_id,
u.name AS user_name,
o.ordered_at,
o.total_amount
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.id = 123
ORDER BY o.ordered_at DESC;
「注文の中身（どの商品を何個買ったか）」を見たいなら、こうです。
SELECT
o.id AS order_id,
o.ordered_at,
p.name AS product_name,
oi.quantity,
oi.unit_price,
(oi.quantity * oi.unit_price) AS amount
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.id = 999;
この二つのクエリが自然に書けるなら、
users / products / orders / order_items の関係は、かなりいい線を行っています。
正規化と“実務的な非正規化”の境目を意識する
「どこまできれいにして、どこから“履歴用のコピー”を許すか」
Day23 前半で作った設計は、ざっくり言うとこうです。
users と products は、かなり正規化寄りのマスタ
orders と order_items は、「履歴」として非正規化を含むテーブル
特に、
orders.total_amount
order_items.unit_price
は、「計算すれば出るけど、あえて持つ」カラムです。
これは、
毎回明細から合計を計算すると重い
価格や割引ルールが変わっても、当時の金額を残したい
といった実務的な理由から来ています。
Day23 後半では、
配送先情報をどこに持つか（ユーザー？注文？）
クーポンや割引をどう表現するか
パフォーマンスを意識したインデックス・非正規化
などを絡めて、「より現実に近いEC設計」に寄せていきます。
Day23 前半のまとめ
ECサイトのDB設計を考えるときは、いきなりテーブル名から入るのではなく、「ユーザーが何をするか（会員登録・商品閲覧・注文・履歴確認）」を日本語で整理し、そこから「誰（users）」「何を（products）」「どの注文（orders）」「その中身（order_items）」という4つのコアテーブルを導き出すのが出発点になる。
users と products は「今の状態」を持つマスタとして比較的正規化寄りに設計しつつ、orders には total_amount、order_items には unit_price を持たせて「注文時点の合計金額・単価」を履歴として残すことで、「商品価格が後から変わっても過去の注文金額は変わらない」「注文履歴画面をシンプルなJOINで出せる」といった実務的な要件を満たす“意図のある非正規化”を取り入れる。
この4テーブルで「ユーザーの注文履歴一覧」「特定注文の明細表示」といった典型的な画面を自然なSQLで書けることを確認できれば、ECサイトDB設計の骨格はほぼできており、Day23 後半ではそこに配送先・クーポン・パフォーマンス・インデックスなどを重ねて、より現場寄りの設計にチューニングしていく。