PAYATHON 2026

如何设计支持 Order、Return、Refund 和 Exchange 的数据库？

支付老李

结论

不要把 Return、Refund 和 Exchange 都设计成一张”新订单”，也不要通过修改原始 OrderItem 来表示这些业务。

更合适的做法是分别记录不同的业务事实：

  • Order / OrderItem：记录客户最初购买的商品。
  • Return / ReturnItem：记录客户实际退回的商品和退货原因。
  • Refund：记录退还给客户的资金。退款可能分多次完成，也可能经过不同的支付渠道。
  • Exchange：换货通常不是单独的库存动作，而是”退回旧商品 + 创建新订单或替换出库单”。
  • Invoice：记录应收凭证。退货后通常需要开具 CreditNote（红字发票、贷项通知单），换出的商品则可能需要新发票。

后续记录都应关联原始 OrderItem。原始订单和订单明细一旦确认，原则上就不应再覆盖数量、价格等字段来表示退换货，否则会破坏审计记录。

推荐的数据关系

核心关系可以表示为：

Order
  └── OrderItem
        ├── ReturnItem ── Return
        │      └── RefundAllocation ── Refund
        └── ExchangeItem ── Exchange
                                  └── ReplacementOrderItem ── OrderItem

这套关系需要支持以下情况：

  • 一张订单可能发生多次退货。
  • 一条订单明细可能分批退回。
  • 一次退货可能包含多条订单明细。
  • 一次退款可能涉及一次或多次支付交易。
  • 一次换货可能退回多件旧商品，并产生多件替换商品。
  • 新旧商品不一定一一对应，例如两件商品换一件价格更高的商品。

因此，不建议只在 OrderItem 上增加 IsReturned 或 ExchangeProductID。这些字段无法准确表示部分退货、多次退货、差价和多次退款。

一个可落地的关系模型

下面的示例省略了客户、地址、税率、币种换算和库存仓库等非核心字段。

CREATE TABLE Product (
    ProductID       BIGINT PRIMARY KEY,
    Name            VARCHAR(255) NOT NULL
);

CREATE TABLE CustomerOrder (
    OrderID         BIGINT PRIMARY KEY,
    CustomerID      BIGINT NOT NULL,
    OrderDate       TIMESTAMP NOT NULL,
    Status          VARCHAR(30) NOT NULL,
    CurrencyCode    CHAR(3) NOT NULL,
    TotalAmount     DECIMAL(18, 2) NOT NULL
);

CREATE TABLE OrderItem (
    OrderItemID     BIGINT PRIMARY KEY,
    OrderID         BIGINT NOT NULL,
    ProductID       BIGINT NOT NULL,
    Quantity        DECIMAL(18, 3) NOT NULL,
    UnitPrice       DECIMAL(18, 2) NOT NULL,
    TaxAmount       DECIMAL(18, 2) NOT NULL DEFAULT 0,
    DiscountAmount  DECIMAL(18, 2) NOT NULL DEFAULT 0,

    FOREIGN KEY (OrderID) REFERENCES CustomerOrder(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID),

    CHECK (Quantity > 0)
);

为 OrderItem 设置独立的 OrderItemID 很重要。只用 (OrderID, ProductID) 作为主键通常不够，因为同一商品可能因促销、批次、规格或单价不同，在同一订单中出现多行。

退货

CREATE TABLE CustomerReturn (
    ReturnID        BIGINT PRIMARY KEY,
    OrderID         BIGINT NOT NULL,
    RequestedAt     TIMESTAMP NOT NULL,
    ReceivedAt      TIMESTAMP NULL,
    Status          VARCHAR(30) NOT NULL,
    ReasonNote      VARCHAR(1000) NULL,

    FOREIGN KEY (OrderID) REFERENCES CustomerOrder(OrderID)
);

CREATE TABLE ReturnItem (
    ReturnItemID    BIGINT PRIMARY KEY,
    ReturnID        BIGINT NOT NULL,
    OrderItemID     BIGINT NOT NULL,
    Quantity        DECIMAL(18, 3) NOT NULL,
    ReasonCode      VARCHAR(30) NOT NULL,
    ConditionCode   VARCHAR(30) NULL,
    Resolution      VARCHAR(30) NOT NULL,
    RefundAmount    DECIMAL(18, 2) NULL,

    FOREIGN KEY (ReturnID) REFERENCES CustomerReturn(ReturnID),
    FOREIGN KEY (OrderItemID) REFERENCES OrderItem(OrderItemID),

    CHECK (Quantity > 0),
    CHECK (RefundAmount IS NULL OR RefundAmount >= 0)
);

Resolution 可以使用以下业务值：

REFUND
EXCHANGE
STORE_CREDIT
REPAIR
REJECTED

至于使用枚举、检查约束还是状态字典表，应根据数据库类型和状态的变更频率决定。

CustomerReturn 表示一次退货申请或一张退货单，ReturnItem 则记录具体退回的原订单明细和数量。不要只在退货单中保存 ProductID，因为同一商品可能在不同订单中以不同价格售出。

退款与支付

退货和退款是两件事。商品可能已经收到，但退款还没有执行；系统也可能在无需退货的情况下发放部分退款，例如价格补偿。

CREATE TABLE Payment (
    PaymentID       BIGINT PRIMARY KEY,
    OrderID         BIGINT NOT NULL,
    Provider        VARCHAR(50) NOT NULL,
    ProviderRef     VARCHAR(255) NULL,
    Amount          DECIMAL(18, 2) NOT NULL,
    CurrencyCode    CHAR(3) NOT NULL,
    Status          VARCHAR(30) NOT NULL,
    PaidAt          TIMESTAMP NULL,

    FOREIGN KEY (OrderID) REFERENCES CustomerOrder(OrderID),
    CHECK (Amount > 0)
);

CREATE TABLE Refund (
    RefundID        BIGINT PRIMARY KEY,
    PaymentID       BIGINT NOT NULL,
    ReturnID        BIGINT NULL,
    Amount          DECIMAL(18, 2) NOT NULL,
    CurrencyCode    CHAR(3) NOT NULL,
    Status          VARCHAR(30) NOT NULL,
    ProviderRef     VARCHAR(255) NULL,
    CreatedAt       TIMESTAMP NOT NULL,
    CompletedAt     TIMESTAMP NULL,

    FOREIGN KEY (PaymentID) REFERENCES Payment(PaymentID),
    FOREIGN KEY (ReturnID) REFERENCES CustomerReturn(ReturnID),

    CHECK (Amount > 0)
);

CREATE TABLE RefundAllocation (
    RefundID        BIGINT NOT NULL,
    ReturnItemID    BIGINT NOT NULL,
    Amount          DECIMAL(18, 2) NOT NULL,

    PRIMARY KEY (RefundID, ReturnItemID),
    FOREIGN KEY (RefundID) REFERENCES Refund(RefundID),
    FOREIGN KEY (ReturnItemID) REFERENCES ReturnItem(ReturnItemID),

    CHECK (Amount >= 0)
);

并非所有系统都需要 RefundAllocation。如果业务只要求退款关联整张退货单，可以省略该表。如果需要准确说明每一笔金额退给了哪件商品，就应保留。

退款状态通常至少包括：

PENDING
PROCESSING
SUCCEEDED
FAILED
CANCELLED

只有支付渠道确认成功后，退款才能视为完成。创建退款记录并不代表资金已经退回。

换货如何建模

一次换货通常包含三个动作：

  1. 接收原商品。
  2. 为替换商品创建新的履约或销售记录。
  3. 计算差价，并进行补收或退款。

可以增加以下换货关联表：

CREATE TABLE Exchange (
    ExchangeID          BIGINT PRIMARY KEY,
    ReturnID            BIGINT NOT NULL,
    ReplacementOrderID  BIGINT NOT NULL,
    Status              VARCHAR(30) NOT NULL,
    CreatedAt           TIMESTAMP NOT NULL,

    FOREIGN KEY (ReturnID) REFERENCES CustomerReturn(ReturnID),
    FOREIGN KEY (ReplacementOrderID)
        REFERENCES CustomerOrder(OrderID)
);

CREATE TABLE ExchangeItem (
    ExchangeItemID          BIGINT PRIMARY KEY,
    ExchangeID              BIGINT NOT NULL,
    ReturnItemID            BIGINT NOT NULL,
    ReplacementOrderItemID  BIGINT NOT NULL,

    FOREIGN KEY (ExchangeID) REFERENCES Exchange(ExchangeID),
    FOREIGN KEY (ReturnItemID) REFERENCES ReturnItem(ReturnItemID),
    FOREIGN KEY (ReplacementOrderItemID)
        REFERENCES OrderItem(OrderItemID)
);

替换订单仍可使用正常的 CustomerOrder 和 OrderItem 表，再通过订单类型标明来源：

ALTER TABLE CustomerOrder
ADD COLUMN OrderType VARCHAR(30) NOT NULL DEFAULT 'SALE';

ALTER TABLE CustomerOrder
ADD COLUMN SourceOrderID BIGINT NULL;

ALTER TABLE CustomerOrder
ADD CONSTRAINT FK_Order_SourceOrder
FOREIGN KEY (SourceOrderID) REFERENCES CustomerOrder(OrderID);

例如：

OrderType = SALE
OrderType = EXCHANGE_REPLACEMENT

如果换出的商品更贵，应将差价作为替换订单上的新应收款处理；如果更便宜，则通过退款或门店余额返还差额。不要直接覆盖旧商品或新商品的价格。

是否需要 ExchangeItem，取决于业务要求。如果只需知道整张替换订单来自哪次换货，Exchange 已经足够。如果必须追踪每件旧商品具体换成了哪件新商品，则需要保留 ExchangeItem。

新 Invoice 是否是常见做法

重新打印或生成一张单据很常见，但界面上显示的”新 Invoice”，在会计意义上不一定都是普通销售发票。

常见的处理方式包括：

  • 保留原始销售发票，不做修改。
  • 针对退货部分开具 CreditNote、红字发票或其他冲销凭证。
  • 为换出的新商品开具新的销售发票。
  • 使用 OriginalInvoiceID 或单独的关联表连接新旧凭证。
  • 通过新收款或退款结清差价。

具体应使用红字发票、贷项通知单，还是作废后重开，取决于所在国家或地区的税务规则，不能只由数据库设计决定。

示例结构如下：

CREATE TABLE Invoice (
    InvoiceID           BIGINT PRIMARY KEY,
    OrderID             BIGINT NULL,
    InvoiceType         VARCHAR(30) NOT NULL,
    OriginalInvoiceID   BIGINT NULL,
    IssuedAt            TIMESTAMP NOT NULL,
    CurrencyCode        CHAR(3) NOT NULL,
    TotalAmount         DECIMAL(18, 2) NOT NULL,
    Status              VARCHAR(30) NOT NULL,

    FOREIGN KEY (OrderID) REFERENCES CustomerOrder(OrderID),
    FOREIGN KEY (OriginalInvoiceID) REFERENCES Invoice(InvoiceID)
);

CREATE TABLE InvoiceLine (
    InvoiceLineID       BIGINT PRIMARY KEY,
    InvoiceID           BIGINT NOT NULL,
    OrderItemID         BIGINT NULL,
    ReturnItemID        BIGINT NULL,
    Description         VARCHAR(500) NOT NULL,
    Quantity            DECIMAL(18, 3) NOT NULL,
    UnitAmount          DECIMAL(18, 2) NOT NULL,
    TaxAmount           DECIMAL(18, 2) NOT NULL DEFAULT 0,

    FOREIGN KEY (InvoiceID) REFERENCES Invoice(InvoiceID),
    FOREIGN KEY (OrderItemID) REFERENCES OrderItem(OrderItemID),
    FOREIGN KEY (ReturnItemID) REFERENCES ReturnItem(ReturnItemID)
);

InvoiceType 可以是：

SALE
CREDIT_NOTE
REPLACEMENT

例如，客户把原发票中的一件商品换成另一件商品时，可以产生：

原始 Invoice
  ├── CreditNote：冲销被退回商品的金额
  └── Replacement Invoice：记录换出商品及应付金额

如果业务允许一张凭证关联多张原始发票，单个 OriginalInvoiceID 就无法满足要求，此时应使用多对多关联表：

CREATE TABLE InvoiceRelation (
    SourceInvoiceID     BIGINT NOT NULL,
    RelatedInvoiceID    BIGINT NOT NULL,
    RelationType        VARCHAR(30) NOT NULL,

    PRIMARY KEY (SourceInvoiceID, RelatedInvoiceID, RelationType),
    FOREIGN KEY (SourceInvoiceID) REFERENCES Invoice(InvoiceID),
    FOREIGN KEY (RelatedInvoiceID) REFERENCES Invoice(InvoiceID)
);

必须实施的业务校验

外键只能保证引用关系存在，无法保证退换货数据符合业务规则。以下约束还需要在事务或服务层实现：

  • 同一 OrderItem 的累计退货数量不能超过已交付数量。
  • 已取消或未支付的商品不能通过正常退货流程重复退款。
  • 累计成功退款金额不能超过可退金额。
  • 退货数量、退款金额和库存入库数量不能共用同一字段。
  • 退款必须使用幂等键，避免支付接口重试造成重复退款。
  • 换货商品应保留当时的价格、税费和折扣快照。
  • 只有通过质检的退货才能重新进入可销售库存。
  • 所有状态变化都应记录时间、操作人和原因，重要财务记录不应物理删除。

校验累计退货数量时，逻辑可以写成：

SELECT
    oi.OrderItemID,
    oi.Quantity AS PurchasedQuantity,
    COALESCE(SUM(
        CASE
            WHEN r.Status IN ('APPROVED', 'RECEIVED', 'COMPLETED')
            THEN ri.Quantity
            ELSE 0
        END
    ), 0) AS ReturnedQuantity
FROM OrderItem oi
LEFT JOIN ReturnItem ri
    ON ri.OrderItemID = oi.OrderItemID
LEFT JOIN CustomerReturn r
    ON r.ReturnID = ri.ReturnID
WHERE oi.OrderItemID = :order_item_id
GROUP BY oi.OrderItemID, oi.Quantity;

实际写入时，应在同一个数据库事务中锁定相关记录，或采用其他并发控制方式。否则，两个并发退货请求可能都会在校验时看到”仍有可退数量”，最终造成超额退货。

不建议采用的设计

以下字段在简单演示中可以使用，但业务稍微复杂后就会遇到问题：

OrderItem.IsReturned
OrderItem.RefundedAmount
OrderItem.ExchangeProductID

它们很难准确表示以下情况：

  • 购买 5 件，只退 2 件。
  • 同一明细分两次退货。
  • 一次退货分两次退款。
  • 退款失败后重新发起。
  • 两件旧商品换一件新商品。
  • 换货产生补款或退款。
  • 商品已退款，但无需退回。
  • 商品已经退回，但质检后拒绝退款。

设计的重点并不是”再生成一张 Invoice”，而是分别建模订单、实物退货、资金退款、替换出库和财务凭证，再用稳定的 ID 连接每一步。这样才能处理部分退换、多次退款和差价，同时保留完整的审计链路。

备注：内容仅供参考。