PAYATHON 2026

在线商店 Class diagram 的数据库表关联设计问题

支付阿杰

结论

问题不在于“表太分散”，而在于订单、商品、收款和商家累计销售额被塞进了同一条数据传递链。

更合适的设计是：

  • Product 直接关联商品所属的商家或 needy resident。
  • Order 表示一笔订单，OrderItem 保存订单中的商品明细。
  • Payment 关联订单，不接收某个 ordered product 的价格。
  • 商家销售额优先从已支付的订单明细中汇总，避免在多个表之间逐级复制和更新。
  • 下单时将商品单价作为快照保存在 OrderItem 中，防止商品改价影响历史订单。

数据库中的信息不必从 Product “传递”到 OrderItem，再“传递”到 Payment。只要外键设计正确，查询时通过 JOIN 就能找到商品、商家、订单和付款之间的关系。

推荐的数据关系

主要实体可以整理为以下关系：

Resident  1 ─── N  Product
Customer  1 ─── N  Order
Order     1 ─── N  OrderItem
Product   1 ─── N  OrderItem
Order     1 ─── N  Payment

如果一笔订单可以包含多个商家的商品，销售额可以沿着下面的关联链计算：

Resident -> Product -> OrderItem -> Order -> Payment

各字段的职责如下：

  • Product.resident_id 表示商品属于哪个 needy resident。
  • Order.customer_id 表示订单由哪个 customer 创建。
  • OrderItem.order_id 和 OrderItem.product_id 表示订单购买了哪些商品。
  • Payment.order_id 表示付款属于哪笔订单。

如果一笔订单只能购买同一个商家的商品，也可以在 Order 中增加 resident_id。这是业务约束，不应单纯为了方便查询而重复保存。

建议的表结构

下面是一份简化的关系型数据库示例，具体数据类型需要根据所用数据库调整。

CREATE TABLE resident (
    resident_id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE customer (
    customer_id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE product (
    product_id BIGINT PRIMARY KEY,
    resident_id BIGINT NOT NULL,
    name VARCHAR(200) NOT NULL,
    current_price DECIMAL(12, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',

    CONSTRAINT fk_product_resident
        FOREIGN KEY (resident_id)
        REFERENCES resident(resident_id),

    CONSTRAINT chk_product_price
        CHECK (current_price >= 0),

    CONSTRAINT chk_product_stock
        CHECK (stock_quantity >= 0)
);

CREATE TABLE customer_order (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    payment_status VARCHAR(20) NOT NULL DEFAULT 'UNPAID',
    total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer(customer_id),

    CONSTRAINT chk_order_total
        CHECK (total_amount >= 0)
);

CREATE TABLE order_item (
    order_item_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12, 2) NOT NULL,
    line_amount DECIMAL(12, 2) NOT NULL,

    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id)
        REFERENCES customer_order(order_id),

    CONSTRAINT fk_order_item_product
        FOREIGN KEY (product_id)
        REFERENCES product(product_id),

    CONSTRAINT chk_order_item_quantity
        CHECK (quantity > 0),

    CONSTRAINT chk_order_item_price
        CHECK (unit_price >= 0),

    CONSTRAINT chk_order_item_amount
        CHECK (line_amount >= 0)
);

CREATE TABLE payment (
    payment_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    payment_status VARCHAR(20) NOT NULL,
    payment_method VARCHAR(30),
    transaction_reference VARCHAR(100),
    paid_at TIMESTAMP NULL,

    CONSTRAINT fk_payment_order
        FOREIGN KEY (order_id)
        REFERENCES customer_order(order_id),

    CONSTRAINT chk_payment_amount
        CHECK (amount >= 0)
);

这里使用 customer_order，因为 ORDER 在不少数据库中是关键字。

下单时应该保存哪些信息

创建订单时，从 Product.current_price 读取当前价格，并写入 OrderItem.unit_price。

INSERT INTO order_item (
    order_item_id,
    order_id,
    product_id,
    quantity,
    unit_price,
    line_amount
)
SELECT
    :order_item_id,
    :order_id,
    product_id,
    :quantity,
    current_price,
    current_price * :quantity
FROM product
WHERE product_id = :product_id
  AND status = 'ACTIVE';

unit_price 是必要的历史快照。例如，商品下单时售价为 100，商家之后将价格改成 120，历史订单仍应显示 100，不能重新读取 Product.current_price。

如果业务需要保留历史商品名称、SKU 或商家名称，也可以在 OrderItem 中保存对应的快照：

product_name_snapshot VARCHAR(200),
sku_snapshot VARCHAR(100)

不必把所有商家资料都复制到订单明细中。只有可能发生变化，同时又需要保留历史状态的字段，才适合保存快照。

Payment 不应保存商品价格

Payment 记录实际支付行为，包括：

  • 应付或实付金额；
  • 支付渠道；
  • 第三方交易号；
  • 支付状态；
  • 支付时间；
  • 退款信息。

商品售价属于 OrderItem，订单总额属于 Order。即使当前业务是一件商品对应一次付款，也不建议让 Payment 直接关联 OrderItem。否则以后增加购物车、多商品订单、部分退款或多次支付时，扩展会很困难。

如果一笔订单只能支付一次，可以为 Payment.order_id 添加唯一约束：

ALTER TABLE payment
ADD CONSTRAINT uq_payment_order UNIQUE (order_id);

如果需要支持分次付款、支付失败后的重试，或者组合使用多种支付方式，就不要添加这个约束。一笔订单可以对应多条支付记录。

支付状态和订单状态要分开

“已支付”不能完整描述订单状态，至少应该把状态分成两组。

支付状态：

UNPAID
PENDING
PAID
PARTIALLY_REFUNDED
REFUNDED
FAILED

履约状态：

PENDING
CONFIRMED
PROCESSING
SHIPPED
COMPLETED
CANCELLED

例如，一笔订单可以同时处于以下状态：

payment_status = 'PAID'
order_status   = 'PROCESSING'

不要用一个 status 字段同时表示付款、发货、完成和取消，否则状态组合很快就会混乱。

商家销售额应该如何计算

通常不建议在每次付款后直接修改 resident.sales_amount。支付回调重复、退款、订单取消或程序异常，都可能导致累计字段出现偏差。

更稳妥的做法是实时汇总已支付的订单：

SELECT
    p.resident_id,
    SUM(oi.line_amount) AS gross_sales
FROM order_item AS oi
JOIN product AS p
    ON p.product_id = oi.product_id
JOIN customer_order AS o
    ON o.order_id = oi.order_id
WHERE o.payment_status = 'PAID'
GROUP BY p.resident_id;

这个查询有一个前提：订单没有发生部分退款，并且支付状态能准确表示订单是否已经全额支付。

如果还要处理退款、平台手续费、优惠券、运费和商家结算，只汇总 line_amount 就不够了。此时可以增加独立的商家账本或结算明细表：

CREATE TABLE resident_ledger (
    ledger_entry_id BIGINT PRIMARY KEY,
    resident_id BIGINT NOT NULL,
    order_item_id BIGINT,
    entry_type VARCHAR(30) NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_ledger_resident
        FOREIGN KEY (resident_id)
        REFERENCES resident(resident_id),

    CONSTRAINT fk_ledger_order_item
        FOREIGN KEY (order_item_id)
        REFERENCES order_item(order_item_id)
);

entry_type 可以包括：

SALE
REFUND
COMMISSION
ADJUSTMENT
PAYOUT

销售发生时写入一条 SALE，退款时再写入一条金额为负数的 REFUND，不修改原来的销售记录。这样便于审计，也能减少累计金额失真的风险。

一个订单包含多个商家时的处理

如果平台允许一个购物车中出现多个 needy resident 的商品，可以采用下面两种模式。

模式一：一个订单包含多个商家

OrderItem 通过 Product 找到对应商家。付款仍然关联整笔订单，再根据各项订单明细计算每个商家的收入。

这种设计更符合顾客的购物习惯，但优惠、运费、退款和结算的分摊会更复杂。

模式二：创建父订单和商家子订单

可以增加 seller_order：

Order
 ├── SellerOrder A
 │    ├── OrderItem 1
 │    └── OrderItem 2
 └── SellerOrder B
      └── OrderItem 3

示例结构如下：

CREATE TABLE seller_order (
    seller_order_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    resident_id BIGINT NOT NULL,
    subtotal DECIMAL(12, 2) NOT NULL,
    fulfillment_status VARCHAR(20) NOT NULL DEFAULT 'PENDING',

    FOREIGN KEY (order_id)
        REFERENCES customer_order(order_id),

    FOREIGN KEY (resident_id)
        REFERENCES resident(resident_id),

    UNIQUE (order_id, resident_id)
);

多商家平台通常更适合这种结构，因为每个商家都可以独立接单、发货、取消和结算。

支付成功时的正确流程

支付流程可以按以下顺序处理：

  1. 创建 Order，将初始支付状态设为 UNPAID。
  2. 根据商品当前价格创建 OrderItem，同时保存价格快照。
  3. 计算并保存订单总额。
  4. 创建 Payment，状态设为 PENDING。
  5. 支付渠道返回成功结果后，将 Payment 更新为 PAID。
  6. 确认有效支付总额达到订单应付金额后，将订单更新为 PAID。
  7. 如果使用账本，在同一事务中生成商家的销售账目。
  8. 后续退款通过退款记录或负数账目冲销。

关键更新应放在数据库事务中。例如：

BEGIN;

UPDATE payment
SET payment_status = 'PAID',
    paid_at = CURRENT_TIMESTAMP,
    transaction_reference = :transaction_reference
WHERE payment_id = :payment_id
  AND payment_status = 'PENDING';

UPDATE customer_order
SET payment_status = 'PAID'
WHERE order_id = :order_id
  AND payment_status = 'UNPAID';

INSERT INTO resident_ledger (
    ledger_entry_id,
    resident_id,
    order_item_id,
    entry_type,
    amount
)
SELECT
    :ledger_entry_id,
    p.resident_id,
    oi.order_item_id,
    'SALE',
    oi.line_amount
FROM order_item AS oi
JOIN product AS p
    ON p.product_id = oi.product_id
WHERE oi.order_item_id = :order_item_id;

COMMIT;

实际实现还要处理幂等性，因为支付平台可能多次发送同一个支付成功通知。可以使用唯一的 transaction_reference，并为账本添加业务唯一约束，防止重复记账。

例如：

ALTER TABLE payment
ADD CONSTRAINT uq_payment_transaction
UNIQUE (transaction_reference);

ALTER TABLE resident_ledger
ADD CONSTRAINT uq_ledger_sale_item
UNIQUE (order_item_id, entry_type);

需要特别注意的问题

  • 金额应使用 DECIMAL，不要使用 FLOAT 或 DOUBLE。
  • 外键用于表达实体关系，不必为了“传递数据”而重复保存所有字段。
  • OrderItem 必须保存下单时的单价，不能在查询历史订单时使用商品当前价格。
  • 付款金额应与订单关联，不应由 OrderItem.price 直接覆盖。
  • 销售额、实收金额和可结算金额是不同的概念，需要分别定义。
  • 支付成功回调、退款和记账操作必须支持重复请求，同时避免重复入账。
  • 库存扣减、订单创建和价格确认通常需要事务或并发控制。
  • 如果商品可以转让给其他商家，最好在 OrderItem 或 SellerOrder 中保存下单时的 resident_id，以保留历史归属。
  • 删除商品或商家时，不要直接级联删除历史订单。通常应将其停用，或者把相关外键的删除行为设为 RESTRICT。

表之间彼此分开没有问题，这本来就是关系型数据库规范化设计的常见形式。需要调整的是各实体的职责：商品、订单、付款和结算分别管理自己的数据，再用明确的主键和外键建立关联。

备注：内容仅供参考。