電商系統的訂單+商品+營銷的Mysql表設計是怎么樣呢

一、前述

問題1: 電商系統創建訂單的邏輯中,如果訂單使用了優惠券的話,是會把優惠券直接標在訂單表么，還是會單獨創建一個表里記錄訂單和多個優惠券之間的關系

問題2: 如何設計一套mysql數據庫的表，可以將訂單信息、支付信息、優惠券信息以及商品信息之間關聯起來

二、實戰演練

結論先說：如果訂單允許使用多個優惠券，必須用一張單獨的關聯表來記錄 order ? coupon 的關系；同時在 orders 表保留優惠券總額（摘要字段）和一個主券引用（可選）以便快速查詢。

下面寫一套可直接落地的 MySQL 表設計（包含建表 SQL）、事務示例、常見場景說明與設計理由（包括快查字段、快照、退款/退券策略、分庫分表注意點）。

設計要點（快速讀）

orders：訂單主體（保留付款/價格摘要字段），主鍵為 BIGINT（生產建議用分布式 ID，示例用 AUTO_INCREMENT 便于演示）。
order_items：訂單商品行，必須保存商品的快照（price/title），以保證歷史穩定性。
coupon_templates：優惠券模板（規則定義）。
user_coupons：優惠券實例（發放給用戶的那張券，包含有效期/狀態/面額等）。
order_coupons：關聯表，記錄某筆訂單用了哪些 user_coupon、每張券抵扣了多少（并保存券的快照），支持多券。
payments：支付流水表（一個訂單可能有多筆支付/分次付款），用于對賬與退款關聯。
refunds：退款單（關聯到 payment / order），用于記錄退款和是否恢復優惠券等業務邏輯。

建表 SQL（MySQL，示例可直接執行）

說明：示例使用 AUTO_INCREMENT 方便演示；生產環境建議用分布式 ID（snowflake/Leaf）替代 AUTO_INCREMENT 并去掉 AUTO_INCREMENT 屬性。
所有金額均以分/最小貨幣單位（整數）存儲，避免精度問題。

-- 1) 用戶/商戶/商品（簡化，用于 FK/演示）
CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(idx_username(username))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE merchants (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(64) NOT NULL,
  title VARCHAR(255) NOT NULL,
  price INT NOT NULL, -- 單位：分（當前售賣價的基價）
  currency CHAR(3) DEFAULT 'CNY',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(idx_sku(sku))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 2) 優惠券模板（定義規則）
CREATE TABLE coupon_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  type ENUM('FIXED','PERCENT','FREE_SHIPPING') NOT NULL COMMENT 'FIXED: 固定金額; PERCENT: 百分比',
  amount INT DEFAULT NULL COMMENT '當 type=FIXED 時，面額（分）',
  percent SMALLINT DEFAULT NULL COMMENT '當 type=PERCENT 時，百分比，0-10000(表示0.00%-100.00%)，可用精度小數處理',
  min_spend INT DEFAULT 0 COMMENT '滿減門檻（分）',
  max_discount INT DEFAULT NULL COMMENT '百分比折扣上限（分）',
  stackable TINYINT(1) DEFAULT 0 COMMENT '是否可與其他優惠券疊加',
  valid_from DATETIME,
  valid_to DATETIME,
  total_issuance INT DEFAULT NULL,
  metadata JSON DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 3) 發放到用戶的優惠券實例（每張券都是一條記錄）
CREATE TABLE user_coupons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(64) DEFAULT NULL, -- 若有券碼
  status ENUM('ISSUED','LOCKED','USED','EXPIRED','REVOKED') DEFAULT 'ISSUED',
  face_amount INT DEFAULT NULL,      -- 遷移模板到實例時的“面額快照”，單位分（若FIXED）
  percent SMALLINT DEFAULT NULL,     -- percent 快照
  min_spend INT DEFAULT NULL,        -- 快照
  expire_at DATETIME DEFAULT NULL,
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  locked_until DATETIME DEFAULT NULL,
  used_at DATETIME DEFAULT NULL,
  used_order_id BIGINT UNSIGNED DEFAULT NULL,
  source VARCHAR(64) DEFAULT NULL,   -- 發放來源/活動ID
  metadata JSON DEFAULT NULL,
  INDEX(idx_user_status(user_id, status)),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (template_id) REFERENCES coupon_templates(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 4) 訂單主表
CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(64) NOT NULL UNIQUE COMMENT '對外訂單號 (建議業務方生成，或交易系統發號器)',
  user_id BIGINT UNSIGNED NOT NULL,
  merchant_id BIGINT UNSIGNED NOT NULL,
  currency CHAR(3) DEFAULT 'CNY',
  total_amount INT NOT NULL COMMENT '商品原始總價（分）',
  shipping_fee INT DEFAULT 0 COMMENT '運費（分）',
  promotion_discount INT DEFAULT 0 COMMENT '促銷/活動優惠總額（分）',
  coupon_discount INT DEFAULT 0 COMMENT '優惠券抵扣總額（分） —— 快查字段',
  payable_amount INT NOT NULL COMMENT '實際應付金額 = total_amount + shipping_fee - promotion_discount - coupon_discount',
  status ENUM('CREATED','PAID','CANCELLED','SHIPPED','COMPLETED','REFUNDED','CLOSED') DEFAULT 'CREATED',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(idx_user_created(user_id, created_at)),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (merchant_id) REFERENCES merchants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 5) 訂單商品明細（每行存快照）
CREATE TABLE order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED DEFAULT NULL,
  sku VARCHAR(64) DEFAULT NULL,
  title VARCHAR(255) NOT NULL COMMENT '商品標題快照',
  qty INT NOT NULL DEFAULT 1,
  unit_price INT NOT NULL COMMENT '下單時單價（分）',
  orig_total INT NOT NULL COMMENT 'unit_price * qty（分）',
  item_discount INT DEFAULT 0 COMMENT '本行促銷/活動優惠（分）',
  item_coupon_discount INT DEFAULT 0 COMMENT '本行優惠券抵扣（分），用于拆分多張券場景',
  final_amount INT NOT NULL COMMENT '最終該行實付金額（分）',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(idx_order_id(order_id)),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 6) 訂單與優惠券的關聯表（記錄每張券實際抵扣金額及快照）
CREATE TABLE order_coupons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  user_coupon_id BIGINT UNSIGNED NOT NULL COMMENT '引用 user_coupons.id（券實例）',
  template_id BIGINT UNSIGNED NOT NULL COMMENT '引用 coupon_templates.id（便于查詢）',
  code VARCHAR(64) DEFAULT NULL,
  applied_amount INT NOT NULL COMMENT '該張券在本訂單中實際抵扣（分）',
  coupon_snapshot JSON DEFAULT NULL COMMENT '冗余快照（如面額/percent/min_spend/stackable），用于審計/回溯',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(idx_order_id(order_id)),
  INDEX(idx_user_coupon(user_coupon_id)),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (user_coupon_id) REFERENCES user_coupons(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 7) 支付流水表（支持多筆支付/分次支付）
CREATE TABLE payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  payment_no VARCHAR(64) NOT NULL UNIQUE COMMENT '對外支付單號/商戶支付流水號',
  order_id BIGINT UNSIGNED NOT NULL,
  amount INT NOT NULL COMMENT '本次支付金額（分）',
  channel VARCHAR(64) NOT NULL COMMENT '渠道，如 ALIPAY, WECHAT, STRIPE 等',
  channel_txn_id VARCHAR(128) DEFAULT NULL COMMENT '通道返回流水號',
  status ENUM('PENDING','SUCCESS','FAILED','PARTIALLY_REFUNDED','REFUNDED') DEFAULT 'PENDING',
  fee INT DEFAULT 0 COMMENT '渠道手續費（分）',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME DEFAULT NULL,
  refunded_amount INT DEFAULT 0,
  INDEX(idx_order_id(order_id)),
  FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- 8) 退款表（記錄退款單、是否需要退回優惠券等）
CREATE TABLE refunds (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  refund_no VARCHAR(64) NOT NULL UNIQUE,
  order_id BIGINT UNSIGNED NOT NULL,
  payment_id BIGINT UNSIGNED DEFAULT NULL,
  amount INT NOT NULL COMMENT '退款金額（分）',
  status ENUM('REQUESTED','PROCESSING','SUCCESS','FAILED') DEFAULT 'REQUESTED',
  reason VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME DEFAULT NULL,
  channel_refund_id VARCHAR(128) DEFAULT NULL,
  need_restore_coupon TINYINT(1) DEFAULT 0 COMMENT '退款成功后是否需返還優惠券（業務決定）',
  INDEX(idx_order_id(order_id)),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (payment_id) REFERENCES payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

設計邏輯與細節說明（逐項解釋）

是否把優惠券直接寫在 `orders` 表？

不推薦將多張優惠券的關系直接放在 orders 表（比如把 coupon_id 寫成 CSV），理由：
- 訂單可能使用多張券（多對多）；
- 需要記錄每張券的實際抵扣金額與快照（券規則可能變），以及用于審計和退券處理；
- 查詢/統計/對賬更復雜。
推薦方案：order_coupons 單獨表存一條券實例與訂單的關聯，并在 orders 表保留 coupon_discount（總額）和可選 primary_coupon_id（若你想快速指向主券），用于快速檢索和支付計算。

為什么需要快照字段（order_items.unit_price / coupon_snapshot）？

商品價格、活動規則、優惠券模板可能隨時間變更。為保證訂單歷史一旦生成不可變更，必須把當時的關鍵字段快照在 order_items、user_coupons（實例化時）和 order_coupons.coupon_snapshot 里。否則后續審計/回溯無法還原當時賬務。

優惠券的生命周期（狀態機）

ISSUED：發到用戶，未被鎖定/使用。
LOCKED：下單時臨時鎖住（避免并發二次使用），通常和訂單創建在同一事務/或 SELECT FOR UPDATE 配合設置 locked_until。下單成功且支付成功后設置 USED，超時/取消則回滾或設置為 ISSUED。
USED：已在訂單中生效并且確認（通常在支付成功或訂單提交成功時被標記）。
EXPIRED / REVOKED：失效或被后臺回收。

事務處理要點：在創建訂單并應用券時，應 SELECT ... FOR UPDATE 鎖住對應 user_coupons 行，status 檢查通過后更新為 LOCKED、插入 orders、order_items、order_coupons，最后在支付成功時更新 user_coupons.status = USED（或在下單提交成功時直接置為 USED，根據業務決定）。

報表/統計與查詢優化

經常需要查詢用戶在某段時間使用的券、按商戶匯總的券抵扣等，所以 user_coupons、order_coupons 應該有 user_id, order_id, template_id 的索引。
orders.coupon_discount、orders.promotion_discount 作為聚合/對賬快查字段，避免每次統計都 join 多張表。

退款與優惠券處理

退款時要明確業務規則：退款是否退回優惠券？
- 常見做法：如果整單退款且規則允許，則將 user_coupons.status 恢復為 ISSUED（或 REVOKED，看活動策略），同時在 refunds.need_restore_coupon=1 記錄；若只部分退款，可能不退券或分段退回。
- 所以 refunds 表要記錄 need_restore_coupon 以及退款成功后由后臺或異步任務去恢復券狀態并記錄歷史。

并發/冪等

所有對 user_coupons 的消費都要做到冪等和并發保護，用 SELECT ... FOR UPDATE 或行級鎖實現。
order_no、payment_no 都需要唯一索引以保證冪等（避免重復下單/重復支付）。

分庫分表考慮

在分庫分表場景：
- orders 通常以 user_id 或 order_no 的某種哈希為分片鍵；
- user_coupons 也建議按 user_id 分片（便于局部事務操作）；
- 跨庫事務要盡量避免，訂單創建時最好能在同一個庫完成 orders、order_items、order_coupons、user_coupons(LOCK) 的操作——因此建議把 user_coupons 與 orders 存在同一分片路由上（例如按 user_id 分片）。
- 如果 user_coupons 在別的庫，應用層需做好分布式事務補償（異步補償 / SAGA）。

典型下單與優惠券使用（事務化偽代碼）

-- 1) 應用層生成 order_no（或從發號器拿 ID），并校驗券合法性
BEGIN;

-- 鎖住要使用的券（按 user_coupon_id）
SELECT * FROM user_coupons WHERE id = ? FOR UPDATE;
-- 校驗 status=ISSUED && expire_at>now && 滿足 min_spend && template.stackable 或多券疊加策略

UPDATE user_coupons SET status='LOCKED', locked_until = NOW() + INTERVAL 30 MIN WHERE id = ?;

-- 插入訂單（或先在內存準備數據并插入）
INSERT INTO orders (order_no, user_id, merchant_id, total_amount, shipping_fee, promotion_discount, coupon_discount, payable_amount, status)
VALUES (..., 'CREATED');

-- 插入 order_items（包含商品快照）
INSERT INTO order_items (...);

-- 插入 order_coupons（記錄每張券實際抵扣）
INSERT INTO order_coupons (order_id, user_coupon_id, template_id, code, applied_amount, coupon_snapshot) VALUES (...);

COMMIT;

-- 2) 用戶支付：
-- 若支付成功，確認券為 USED（原來 LOCKED）
UPDATE user_coupons SET status='USED', used_at=NOW(), used_order_id = <order_id> WHERE id = ? AND status='LOCKED';

-- 若用戶取消或超時未支付，釋放券
UPDATE user_coupons SET status='ISSUED', locked_until=NULL WHERE id=? AND status='LOCKED';

示例：查詢一個訂單及其優惠券/支付信息

SELECT o.order_no, o.total_amount, o.coupon_discount, o.payable_amount, p.payment_no, p.status AS payment_status
FROM orders o
LEFT JOIN payments p ON p.order_id = o.id AND p.status = 'SUCCESS'
WHERE o.order_no = 'ORD202509120001';

-- 查詢該訂單使用了哪些券
SELECT oc.applied_amount, uc.code, ct.name, oc.coupon_snapshot
FROM order_coupons oc
JOIN user_coupons uc ON oc.user_coupon_id = uc.id
JOIN coupon_templates ct ON oc.template_id = ct.id
WHERE oc.order_id = (SELECT id FROM orders WHERE order_no='ORD202509120001');

小結（建議）

不要把多個 coupon 寫成 orders 表字段或 CSV，要用 order_coupons 關聯表。

orders 表保留 coupon_discount 總額作為快查字段，避免頻繁 join。

user_coupons 必須保存實例快照（面額 / percent / expire），并且在下單時 SELECT ... FOR UPDATE 做鎖定；確認支付后標記 USED，失敗則釋放。

退款流程要提前設計（是否恢復券、是否部分退回），并在 refunds 表記錄是否需要恢復券。

生產環境主鍵使用分布式 ID（雪花 / 號段），便于分庫分表與擴展；示例里為便捷起見用了 AUTO_INCREMENT，上線時替換即可。

三、要不要將正向訂單和逆向訂單拆成不同表

?? 總體設計思路

是否放在同一個表？
- 放在一個表：
  - 優點：統一查詢、減少表數量。
  - 缺點：字段差異大，邏輯分支復雜，影響擴展性。
- 分表：
  - 通常業界實踐是 正向訂單 和 逆向訂單 分開存放，因為生命周期、業務字段、處理邏輯不同。
  - 比如：淘寶、京東、亞馬遜這種大規模電商平臺，都會區分“銷售訂單”和“售后訂單（退款/退貨/拒付）”。
逆向訂單要不要分多個表？
- 一般不建議為每個逆向類型單獨建表（退貨單表、退款單表、拒付單表），否則表過多，擴展困難。
- 推薦：
  - 正向訂單表（銷售訂單）
  - 逆向訂單表（售后訂單：退貨、拒付、退款都放這里，通過 reverse_type 區分）
逆向訂單與正向訂單關系：
- 逆向訂單是基于原始正向訂單產生的。
- 必須有一個字段 origin_order_id，指向對應的正向訂單。

?? 支付單是否要拆成正向/逆向兩類？

你說得對：

在支付體系里，退款也需要有單獨的“支付單號”（比如支付寶/微信會返回一個 refund_id）。
如果退款單直接更新在原始支付單里，會丟失 多次退款 的鏈路信息（一個支付可能對應多個退款）。

因此，業界一般做法是：

支付單表只記錄“正向支付”（即從用戶到商戶的資金流）。
退款單（逆向資金流）單獨建表，而不是混在一個表里。

?? 數據庫表設計（MySQL）

1. 正向訂單表（sales_order）

CREATE TABLE sales_order (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '訂單ID',
    user_id BIGINT NOT NULL COMMENT '用戶ID',
    merchant_id BIGINT NOT NULL COMMENT '商戶ID',
    total_amount DECIMAL(18,2) NOT NULL COMMENT '訂單總金額',
    status VARCHAR(32) NOT NULL COMMENT '訂單狀態：CREATED, PAID, SHIPPED, COMPLETED, CANCELED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 正向支付單表（payment）

CREATE TABLE payment (
    payment_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '支付單ID',
    order_id BIGINT NOT NULL COMMENT '正向訂單ID',
    pay_channel_order_no VARCHAR(64) NOT NULL COMMENT '支付通道返回的支付流水號',
    amount DECIMAL(18,2) NOT NULL COMMENT '支付金額',
    method VARCHAR(32) NOT NULL COMMENT '支付方式: ALIPAY, WECHAT, VISA...',
    status VARCHAR(32) NOT NULL COMMENT '支付狀態：INIT, SUCCESS, FAILED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_channel_order (pay_channel_order_no),
    FOREIGN KEY (order_id) REFERENCES sales_order(order_id)
);

3. 逆向訂單表（reverse_order）

CREATE TABLE reverse_order (
    reverse_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '逆向訂單ID',
    origin_order_id BIGINT NOT NULL COMMENT '原始正向訂單ID',
    reverse_type VARCHAR(32) NOT NULL COMMENT '逆向類型: REFUND, RETURN, CHARGEBACK',
    reason VARCHAR(255) COMMENT '逆向原因',
    amount DECIMAL(18,2) NOT NULL COMMENT '逆向金額',
    status VARCHAR(32) NOT NULL COMMENT '逆向訂單狀態：CREATED, PROCESSING, SUCCESS, REJECTED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (origin_order_id) REFERENCES sales_order(order_id)
);

4. 逆向支付單表（reverse_payment）

CREATE TABLE reverse_payment (
    reverse_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '退款單ID',
    payment_id BIGINT NOT NULL COMMENT '關聯的正向支付單ID',
    reverse_order_id BIGINT NOT NULL COMMENT '關聯的逆向訂單ID',
    reverse_channel_order_no VARCHAR(64) COMMENT '支付通道返回的退款流水號',
    amount DECIMAL(18,2) NOT NULL COMMENT '退款金額',
    status VARCHAR(32) NOT NULL COMMENT '退款狀態：INIT, PROCESSING, SUCCESS, FAILED',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payment(payment_id),
    FOREIGN KEY (reverse_order_id) REFERENCES reverse_order(reverse_id)
);

?? 三種訂單處理流程

1. 正常下單（正向訂單）

用戶下單 → sales_order 生成記錄（CREATED）
支付成功 → 更新 payment.status=SUCCESS，并更新 sales_order.status=PAID
商戶發貨 → sales_order.status=SHIPPED
用戶確認收貨 → sales_order.status=COMPLETED

2. 退貨單（逆向訂單 - RETURN）

用戶發起退貨 → reverse_order 新增一條（reverse_type=RETURN, status=CREATED）
商戶審核 → reverse_order.status=PROCESSING
商戶同意退貨 → 用戶退回商品 → 驗貨 → reverse_order.status=SUCCESS
支付系統發起退款（更新 payment.status=REFUNDED）

3. 拒付單（逆向訂單 - CHARGEBACK）

銀行或支付機構通知拒付 → reverse_order 新增（reverse_type=CHARGEBACK）
風控/客服人工審核 → reverse_order.status=PROCESSING
若拒付成立 → reverse_order.status=SUCCESS，商戶資金扣減
若拒付失敗 → reverse_order.status=REJECTED

4. 退款單（逆向訂單 - REFUND）

用戶未發貨申請退款 → reverse_order 新增（reverse_type=REFUND）
商戶審核通過 → reverse_order.status=SUCCESS
支付網關執行退款，更新 payment.status=REFUNDED

?? 支付單關系與流程

正向支付
- 用戶下單 → 創建 payment 記錄
- 調用支付通道（支付寶/微信） → 返回通道支付流水號（pay_channel_order_no）
- 更新 payment.status=SUCCESS
退款
- 用戶申請退款 → 創建 reverse_order（逆向訂單）
- 基于某個 payment 新建 refund 記錄
- 調用支付通道退款接口 → 返回 refund_channel_order_no
- 更新 refund.status=SUCCESS
- 如果全額退款 → 同時更新 payment.status=REFUNDED
- 如果部分退款 → payment 保持 SUCCESS，但掛有多條 refund
多次退款
- 一個 payment → 可以對應多個 refund（部分退款場景常見）
拒付（chargeback）
- 通常不通過退款接口，而是由銀行/支付機構發起資金回退
- 在你的 reverse_order 表里記一條 CHARGEBACK，然后在退款表 refund 中也插一條對應的資金逆向流水

? 總結

正向訂單與逆向訂單分表存儲，通過 origin_order_id 建立關系。
逆向訂單表中通過 reverse_type 區分退款、退貨、拒付，而不是拆成多個表。
處理流程：
- 正向：下單 → 支付 → 發貨 → 收貨
- 退貨：退貨單 → 審核 → 退回商品 → 退款
- 拒付：支付機構發起 → 審核 → 資金調整
- 退款：申請退款 → 審核 → 支付網關退款

支付單表：只記錄正向資金流。
退款單表：單獨建表，掛在支付單之下，用于記錄逆向資金流。
退款單號：既有內部生成的 refund_id，也有支付通道返回的 refund_channel_order_no。
優勢：
- 保留完整支付+退款鏈路
- 支持部分退款、多次退款
- 對賬時可以一一對應支付通道流水

?? 為什么推薦拆表？

業務語義清晰
- 正向訂單：代表用戶買東西（從用戶到商戶的資金流）。
- 逆向訂單：代表退款/拒付/退貨（從商戶到用戶的資金流）。
- 放在一個表里，需要 order_type 字段區分，查詢和統計時要加很多條件，邏輯耦合度高。
狀態機完全不同
- 正向訂單常見狀態：INIT → PAID → SHIPPED → FINISHED
- 逆向訂單常見狀態：INIT → APPROVED → REFUNDING → REFUNDED / FAILED
- 如果強行放在一張表，會出現大量無效字段，字段語義會越來越模糊。
擴展性好
- 逆向訂單后續可能有 多次申請、部分退款、銀行拒付 等場景，字段設計和正向訂單差別很大。
- 單獨一張表可以靈活加字段，不會污染正向訂單表。
性能與分庫分表
- 電商里正向訂單數量遠大于逆向訂單。
- 如果放在一張表，數據量巨大且冷熱數據混雜；拆表后，逆向訂單表更小更輕，查詢效率更高。
符合行業主流實踐
- 支付寶 / 微信支付：明確區分 trade_order 和 refund_order（退款有單獨的 refund_id）。
- Amazon / eBay：也有 Order 表和 Return / Refund 表，分開存儲。

?? 那是不是一定要拆？

小系統（數據量小、邏輯簡單）：可以放一起，用 order_type 字段區分，開發成本低。
大中型系統（電商/支付中心）：強烈推薦拆表，清晰、可擴展、利于分庫分表。

?? 個人推薦

正向訂單表：只存購買行為
逆向訂單表：單獨一張表存退款/退貨/拒付

這樣：

正向和逆向的訂單號規則也可以分開（比如 O20250913001 / R20250913001）。

支付單和退款單可以分別掛載在正向/逆向訂單之下。

四、訂單明細(order_item)和訂單優惠券(order_coupons)是不是要拆分正向逆向

這是個非常關鍵的問題，涉及 正向單 & 逆向單 的設計選擇。我們可以從電商系統里常見的 庫存回滾、優惠券返還、對賬一致性 來拆解。

1?? 方案一：在 `order_items` 和 `order_coupons` 增加狀態字段

做法：
在現有的正向訂單明細表中，新增字段，比如：
- refund_status（未退款/部分退款/已退款）
- refunded_quantity（已退款數量）
- refunded_amount（已退款金額）
優點：
- 表結構簡單，不需要額外的逆向表。
- 查詢訂單整體情況時只查一張表，簡單直觀。
缺點：
- 難以支撐復雜的退款場景（部分退款、多次退款、跨期退款）。
- 如果要做對賬或審計，正向/逆向混在一起，不容易拆清楚。
- 退款數據和下單數據耦合，不利于擴展。

2?? 方案二：新增退款記錄（逆向單表）

做法：
新增兩張“逆向表”：
- refund_items（對應 order_items，記錄每次退款的商品明細）
- refund_coupons（對應 order_coupons，記錄每次優惠券返還的情況）
邏輯：
- 正向單表只保留原始下單數據，永遠不變。
- 退款時，在 refund_items 和 refund_coupons 里新增一條逆向記錄，和對應的退款單 ID 關聯。
優點：
- 數據清晰，正向與逆向天然隔離。
- 支持多次退款（部分退款、多商品退款），便于追溯。
- 更容易做財務對賬。
缺點：
- 表數量增加，查詢時要做多表 JOIN。
- 需要額外的退款單設計（refund_order）。

3?? 業界常見做法

淘寶 / 京東 / 美團 等大型電商，基本都會采用 逆向表模式（方案二），因為：
- 電商退款場景非常復雜（部分退款、優惠券部分返還、活動優惠如何分攤）。
- 需要保留所有歷史記錄以供審計。
- 正向單的“不可變性”非常重要（避免篡改）。

? 推薦做法

個人推薦 方案二（逆向表），原因是：

正向表保持只讀（下單時落一次），符合“訂單一經生成不可修改”的原則。
退款、退貨、逆向操作獨立成逆向單表，和財務系統/庫存系統/營銷系統做對賬更清晰。
對于 庫存回滾、優惠券釋放，只需要在退款表中新增一條記錄，庫存和券服務即可根據逆向表的數據來進行回滾。

?? 總結：

小型系統：可以在 order_items / order_coupons 加狀態字段（簡單粗暴）。
中大型電商：必須拆逆向表，記錄退款明細。

五、那么將上面的退款item信息和優惠券信息直接放在逆向訂單表里加字段呢

其實就是 用 JSON 存退款明細，避免額外建 refund_items / refund_coupons 表。我們可以把兩種做法對比一下。

?? 方案 A：逆向訂單表 + JSON 字段（refund_skus / refund_coupons）

表結構示例

CREATE TABLE refund_orders (
    refund_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    refund_amount DECIMAL(10,2) NOT NULL,
    refund_reason VARCHAR(255),
    refund_status TINYINT NOT NULL,
    refund_skus JSON,          -- [{"sku_id":123,"qty":1,"amount":50.00}, ...]
    refund_coupons JSON,       -- [{"coupon_id":456,"amount":10.00}, ...]
    created_at DATETIME,
    updated_at DATETIME
);

優點
- 表數量少，結構簡單，維護成本低。
- 小團隊、業務場景不復雜時，開發效率高。
- 不需要復雜的多表 JOIN。
缺點
- JSON 不好做 精確查詢/統計（比如“查詢某個 SKU 被退款的次數”）。
- 金融/財務對賬場景下，JSON 數據不夠規整，不符合審計要求。
- 復雜退款場景（多次退款、部分退款、優惠券跨退款拆分）時，JSON 會越來越難維護。

?? 方案 B：拆分 refund_items / refund_coupons 子表

表結構示例

CREATE TABLE refund_orders (
    refund_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    refund_amount DECIMAL(10,2) NOT NULL,
    refund_reason VARCHAR(255),
    refund_status TINYINT NOT NULL,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE refund_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    refund_id BIGINT NOT NULL,
    sku_id BIGINT NOT NULL,
    refund_quantity INT NOT NULL,
    refund_amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE refund_coupons (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    refund_id BIGINT NOT NULL,
    coupon_id BIGINT NOT NULL,
    refund_amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME,
    updated_at DATETIME
);

優點
- 數據規整，方便做統計/聚合/財務對賬。
- 支持多次退款/部分退款，不會出現 JSON 難維護的問題。
- 審計友好（可以單表追蹤優惠券/商品的退款情況）。
缺點
- 表數量多，維護復雜度更高。
- 查詢時要 JOIN，開發略麻煩。

?? 推薦選擇

如果你做的是 小型系統 / 內部系統 / 不涉及復雜對賬：
?? 方案 A（JSON 存在 refund_orders 表里）足夠，簡單快速。
如果你做的是 中大型電商 / 有財務審計 / 退款場景復雜：
?? 強烈推薦方案 B（拆分 refund_items / refund_coupons），因為正向/逆向數據往往要被統計、追溯、審計，JSON 會成為數據治理的障礙。

? 個人推薦：
長遠看還是方案 B（單獨子表）更靠譜。雖然多了幾張表，但數據結構規整，統計和審計更輕松。JSON 方案適合 MVP 或 POC 階段，等業務復雜后還是會回到子表設計。

六、訂單系統需要記錄訂單所使用的優惠券的核銷情況么

雖然 營銷系統 是優惠券的發放方、生命周期管理方（發放、領取、過期、可用范圍），但在實際的電商支付鏈路中：

下單時：訂單系統必須知道“優惠券是否已經占用、是否可用”，否則訂單金額算不準。
支付成功時：訂單系統必須明確這筆訂單 確實使用了哪些優惠券，并將它們標記為已核銷。
退款/退貨時：訂單系統要知道是否需要 釋放優惠券（可退回）或保持已核銷（不可退回）。

?? 如果只依賴營銷系統，而訂單系統不存儲核銷結果，會出現風險：

賬不對：訂單表里顯示優惠 50 元，但營銷系統顯示券還沒用掉。
退款難：訂單系統沒記錄用過哪些券，退款時無法正確釋放。
對賬難：財務側對比訂單優惠金額與營銷系統券核銷金額，不一致。

所以，訂單系統需要保存優惠券的核銷結果。

1.實際落地的設計

表設計（簡化版，對之前的order_coupon整改）

-- 訂單優惠券關聯表
CREATE TABLE order_coupons (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    coupon_id BIGINT NOT NULL,
    discount_amount DECIMAL(10,2) NOT NULL,
    status TINYINT NOT NULL,  -- 0: 占用中, 1: 已核銷, 2: 已退回
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE(order_id, coupon_id)
);

status 字段非常關鍵：
- 0: 占用中 → 下單鎖券，還未支付
- 1: 已核銷 → 支付成功，券使用完成
- 2: 已退回 → 訂單取消或退款，券退回用戶

業務流程

下單
- 訂單系統寫 order_coupons(status=0)
- 營銷系統同步標記該券“已占用”
支付成功
- 訂單系統將 status=1
- 營銷系統也更新為“已核銷”
訂單取消/退款
- 如果券可退回：訂單系統更新 status=2，通知營銷系統退回
- 如果券不可退回：保持 status=1

3. 行業參考

淘寶/天貓：訂單詳情頁會明確顯示“已使用的優惠券/紅包”，并在訂單取消時自動退回券 → 說明訂單系統一定存了核銷信息。
京東：訂單詳情同樣會記錄優惠券抵扣金額，并在退單時處理釋放。
亞馬遜（含 gift card）：訂單側會記錄 gift card 抵扣的金額，退款時會退回到賬戶余額或 gift card。

?? 都是 訂單系統和營銷系統雙記錄，保證對賬和退款鏈路可追溯。

? 結論：
訂單系統必須 記錄優惠券核銷情況，不能只靠營銷系統。
最合理的方式是：

營銷系統 負責優惠券生命周期和發放管理；
訂單系統 負責訂單維度的優惠券核銷落地（通過 order_coupons 表），確保賬實一致。

轉載請注明出處：http://www.rzrgm.cn/fnlingnzb-learner/p/19089321

posted @ 2025-09-13 16:23 Boblim 閱讀(52) 評論(0) 收藏舉報

刷新頁面返回頂部

Boblim