Day23 後半のゴール
「“現実のECっぽい制約”を設計に落とし込めるようになる」
前半で、ECサイトの骨格（users / products / orders / order_items）は組めました。
後半では、現実のECで必ず出てくるテーマを足していきます。
配送先をどこに持つか
クーポンや割引をどう表現するか
パフォーマンスとインデックスをどう意識するか
ここを押さえると、「教科書のEC」から「実務寄りのEC」に一段ギアが上がります。
配送先情報をどこに持つか
「“ユーザーの住所”と“注文時の配送先”は別物として扱う」
まず、ほぼ全てのECで必要になるのが「配送先」です。
ここでよくある勘違いが、
ユーザーの住所 = 配送先
と決めつけてしまうことです。
実際には、
ユーザーの会員情報としての住所（請求先・連絡先）
注文時に指定された配送先（実家・職場・ギフト先など）
は別物として扱う方が安全です。
ユーザーの基本住所を users に持つパターン
シンプルに考えると、users に住所カラムを足す形が思い浮かびます。
ALTER TABLE users
ADD COLUMN postal_code VARCHAR(20),
ADD COLUMN address1 VARCHAR(255),
ADD COLUMN address2 VARCHAR(255),
ADD COLUMN phone VARCHAR(50);
これは「会員情報としての住所」です。
マイページで編集できる、いわゆる“現在の住所”。
注文時の配送先は orders にコピーする
一方で、「注文時にどこに送ったか」は、
users の住所とは切り離して orders に持つのが実務的です。
ALTER TABLE orders
ADD COLUMN shipping_name VARCHAR(100) NOT NULL,
ADD COLUMN shipping_postal_code VARCHAR(20) NOT NULL,
ADD COLUMN shipping_address1 VARCHAR(255) NOT NULL,
ADD COLUMN shipping_address2 VARCHAR(255),
ADD COLUMN shipping_phone VARCHAR(50);
ここでの重要ポイントは、
注文時の配送先は「当時のスナップショット」であり、
ユーザーが後から住所を変えても、過去の注文の配送先は変わらない
ということです。
これは Day20 でやった「履歴としての非正規化」と同じ考え方です。
「正規化的には重複だけど、履歴としては必要なコピー」を、意図を持って許可しています。
クーポン・割引をどう表現するか
「“ルール”と“適用結果”を分けて考える」
次に、ECでよくある「クーポン」「割引」の話です。
ここも、設計を雑にするとすぐに破綻します。
考えるべきものは大きく二つです。
クーポンそのものの定義（どんな条件・どんな割引か）
そのクーポンが、どの注文にどう適用されたか
クーポンマスタ（coupons）のイメージ
まずは「クーポンそのもの」を表すテーブルです。
CREATE TABLE coupons (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
discount_type VARCHAR(20) NOT NULL, -- 'percent' or 'fixed'
discount_value INT NOT NULL, -- 10% なら 10, 500円引きなら 500
valid_from DATETIME NOT NULL,
valid_until DATETIME NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
) ENGINE=InnoDB;
ここでは、「クーポンのルール」を定義しています。
何％引きなのか、いつまで有効なのか、といった情報です。
注文にクーポンを紐づける（orders に持たせる）
次に、「どの注文でどのクーポンが使われたか」を表現します。
シンプルなパターンとしては、orders にクーポン関連カラムを足します。
ALTER TABLE orders
ADD COLUMN coupon_id BIGINT,
ADD COLUMN discount_amount INT NOT NULL DEFAULT 0,
ADD CONSTRAINT fk_orders_coupon
FOREIGN KEY (coupon_id) REFERENCES coupons(id);
ここでのポイントは二つです。
coupon_id は「どのクーポンを使ったか」を示す
discount_amount は「その注文で実際にいくら値引きされたか」を“結果として”保存する
discount_amount を毎回計算で出すこともできますが、
クーポンのルールが後から変わったり、複数割引が絡んだりすると、
「当時の割引額」を再現するのが難しくなります。
なので、
クーポンのルールは coupons に
実際に適用された割引額は orders.discount_amount に
という分離が、実務的にちょうどいいバランスです。
パフォーマンスとインデックスをどう意識するか
「“よく使う画面”から逆算して貼る」
設計演習なので、パフォーマンスも少しだけ意識しておきます。
全部にインデックスを貼ればいいわけではなく、「よく使うクエリ」から逆算します。
典型的なクエリ1：ユーザーの注文履歴
よくあるのは、「ユーザーの注文履歴を新しい順に出す」クエリです。
SELECT
o.id,
o.ordered_at,
o.total_amount,
o.status
FROM orders o
WHERE o.user_id = 123
ORDER BY o.ordered_at DESC
LIMIT 20;
このクエリを速くするには、
orders.user_id
orders.ordered_at
にインデックスが効いてほしいです。
MySQL では、複合インデックスを貼るのが定石です。
CREATE INDEX idx_orders_user_ordered_at
ON orders (user_id, ordered_at DESC);
これにより、
user_id で絞りつつ、ordered_at でソートする
というパターンに強くなります。
典型的なクエリ2：商品一覧
商品一覧画面では、例えば「新着順」「価格順」で並べ替えることが多いです。
SELECT
id, name, price
FROM products
WHERE /* 条件なし or カテゴリ条件など */
ORDER BY created_at DESC
LIMIT 20;
この場合は、
products.created_at
（場合によっては price）
にインデックスを貼ることを検討します。
ただし、「全件を created_at でソートして LIMIT 20」なら、
主キーと組み合わせたインデックスや、クラスタ化の影響も絡むので、
本当に必要かどうかは EXPLAIN を見て判断するのが実務的です。
Day23 の段階では、
よく使う画面ごとに「WHERE と ORDER BY に出てくるカラム」を意識し、
そこにインデックス候補がある、と考えられれば十分です。
「正しさ」と「現実的な運用」のバランスを見る
「仕様として割り切るところを、ちゃんと“言語化”する」
ここまで設計してくると、
「もっと細かくやろうと思えばいくらでもできる」状態になります。
例えば、
配送先を別テーブル shipping_addresses に切り出して再利用するか
クーポンを複数同時に使えるようにするか（中間テーブルが必要になる）
注文のステータス遷移を別テーブルで履歴管理するか
など、やろうと思えばどこまでも複雑にできます。
ここで大事なのは、
今回のECでは、どこまでを仕様としてサポートするか
を、ちゃんと決めることです。
例えば、最初のバージョンでは、
配送先は「毎回入力」ではなく「ユーザーの住所をコピーして編集可」にする
クーポンは1注文につき1つだけ
ステータス履歴はログで見るだけで、DBでは最新状態だけ持つ
といった割り切りも、十分に“正しい選択”です。
設計のうまさは、「全部盛りにすること」ではなく、
「要件に対して過不足なく、理由を持って削ったり足したりできること」です。
ここまでの設計を一言で説明してみる
「“誰が・何を・いくらで・どこに送ったか”を一貫して追えるか」
Day23 のEC設計を、一言でまとめるとこうなります。
users
→ 誰が
products
→ 何を
orders
→ いつ・いくらで・どんな条件（クーポン・ステータス）で
order_items
→ その注文の中で、どの商品を何個・いくらで
orders.shipping_*
→ どこに送ったか
orders.discount_amount / coupons
→ どんな割引が適用されたか
これらを通して、
「このユーザーが、過去にどんな注文をして、どこに送り、いくら払ったか」
を一貫して追える設計になっているかどうか。
ここが、ECサイトDB設計の“芯”です。
Day23 後半のまとめ
ECサイトのDB設計を現実寄りにしていくうえで重要なのは、「ユーザーの現在の情報」と「注文時点の履歴」をきちんと分けることであり、会員情報としての住所は users に持ちつつ、実際にその注文で使われた配送先は orders.shipping_* としてコピーして残し、クーポンも「ルール（coupons）」と「その注文で実際にいくら値引きされたか（orders.discount_amount）」を分離することで、「後からユーザー情報やクーポン条件が変わっても、過去の注文内容は変わらない」という“履歴としての正しさ”を守る。
さらに、「ユーザーの注文履歴」「商品一覧」といったよく使う画面から逆算して、orders(user_id, ordered_at) や products(created_at) などにインデックスを設計しつつ、配送先を別テーブルにするか、クーポンを複数許可するか、ステータス履歴をどこまで持つか、といった部分は「今回のECでどこまでを仕様としてサポートするか」という観点で意図的に線を引くことで、正規化・非正規化・パフォーマンス・運用のバランスが取れた“説明できる設計”に仕上がっていく。