如何在 SQL 中设计多种 payment schema？

2026-10-10支付老李

结论

建议采用“主表 + 支付方式子表”的设计：

Payment 保存所有支付方式共有的数据，例如金额、订单、状态和支付时间。
payment_method 表示本次支付使用 CASH、CHEQUE 还是 MOBILE。
各明细表以 payment_id 作为主键，同时将其作为指向 Payment 的外键。
不要在 Payment 中为每种支付方式分别添加可空外键。

以后增加支付方式时，只需添加相应的明细表或扩展支付方式字典，不必修改 Payment 的列结构。

推荐表结构

下面使用通用 SQL。自增语法需要根据 MySQL、PostgreSQL、SQL Server 等数据库进行调整。

CREATE TABLE PaymentMethod (
    method_code VARCHAR(20) PRIMARY KEY,
    method_name VARCHAR(100) NOT NULL
);

INSERT INTO PaymentMethod (method_code, method_name)
VALUES
    ('CASH', 'Cash'),
    ('CHEQUE', 'Cheque'),
    ('MOBILE', 'Mobile Payment');

CREATE TABLE Payment (
    payment_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    payment_amount DECIMAL(18, 2) NOT NULL,
    payment_method VARCHAR(20) NOT NULL,
    payment_status VARCHAR(20) NOT NULL,
    paid_at TIMESTAMP NULL,

    CONSTRAINT fk_payment_method
        FOREIGN KEY (payment_method)
        REFERENCES PaymentMethod(method_code),

    CONSTRAINT ck_payment_amount
        CHECK (payment_amount > 0)
);

各支付方式的明细表只保存该方式特有的信息：

CREATE TABLE CashPaymentDetails (
    payment_id BIGINT PRIMARY KEY,
    received_amount DECIMAL(18, 2) NOT NULL,

    CONSTRAINT fk_cash_payment
        FOREIGN KEY (payment_id)
        REFERENCES Payment(payment_id)
        ON DELETE CASCADE
);

CREATE TABLE ChequePaymentDetails (
    payment_id BIGINT PRIMARY KEY,
    cheque_number VARCHAR(100) NOT NULL,
    cheque_type VARCHAR(30) NULL,
    bank_name VARCHAR(200) NULL,

    CONSTRAINT fk_cheque_payment
        FOREIGN KEY (payment_id)
        REFERENCES Payment(payment_id)
        ON DELETE CASCADE
);

CREATE TABLE MobilePaymentDetails (
    payment_id BIGINT PRIMARY KEY,
    provider VARCHAR(50) NOT NULL,
    transaction_reference VARCHAR(200) NOT NULL,

    CONSTRAINT fk_mobile_payment
        FOREIGN KEY (payment_id)
        REFERENCES Payment(payment_id)
        ON DELETE CASCADE,

    CONSTRAINT uq_mobile_transaction
        UNIQUE (provider, transaction_reference)
);

表之间的关系如下：

Payment 1 -> 0..1 CashPaymentDetails
Payment 1 -> 0..1 ChequePaymentDetails
Payment 1 -> 0..1 MobilePaymentDetails

一笔现金支付可以这样写入：

INSERT INTO Payment (
    payment_id,
    order_id,
    payment_amount,
    payment_method,
    payment_status
)
VALUES (
    1,
    10001,
    300.00,
    'CASH',
    'COMPLETED'
);

INSERT INTO CashPaymentDetails (
    payment_id,
    received_amount
)
VALUES (
    1,
    300.00
);

查询时，再根据支付方式关联对应的明细表：

SELECT
    p.payment_id,
    p.order_id,
    p.payment_amount,
    p.payment_status,
    c.received_amount
FROM Payment AS p
JOIN CashPaymentDetails AS c
    ON c.payment_id = p.payment_id
WHERE p.payment_method = 'CASH';

为什么让子表引用主表

如果由 Payment 保存各个明细表的外键，每增加一种支付方式，都要：

修改 Payment，添加一个可空列。
添加新的外键和索引。
修改“只能有一个外键非 NULL”的约束。
修改依赖该表结构的查询和程序代码。

支付方式越多，核心表就会越宽。

改为由明细表引用 Payment 后，Payment 的结构可以保持稳定。子表中的 payment_id 既是主键，也是外键，所以同一笔支付在同一种明细表中最多只有一条记录，也不需要再设置单独的 cash_id、cheque_id 或 mobile_id。

如何保证只能选择一种支付方式

普通外键只能保证被引用的记录存在，无法单独保证以下规则：

CASH 支付必须有且只能有一条现金明细。
CASH 支付不能同时拥有支票或移动支付明细。
明细表的类型必须与 Payment.payment_method 一致。

如果需要由数据库严格执行这些规则，可以使用复合外键。先在主表中添加可供引用的唯一键：

ALTER TABLE Payment
ADD CONSTRAINT uq_payment_id_method
UNIQUE (payment_id, payment_method);

然后在每个子表中保存固定的支付方式，并建立复合外键：

CREATE TABLE CashPaymentDetails (
    payment_id BIGINT PRIMARY KEY,
    payment_method VARCHAR(20) NOT NULL DEFAULT 'CASH',
    received_amount DECIMAL(18, 2) NOT NULL,

    CONSTRAINT ck_cash_method
        CHECK (payment_method = 'CASH'),

    CONSTRAINT fk_cash_payment_type
        FOREIGN KEY (payment_id, payment_method)
        REFERENCES Payment(payment_id, payment_method)
        ON DELETE CASCADE
);

支票表和移动支付表可以使用相同的模式，分别设置 CHECK (payment_method = 'CHEQUE') 和 CHECK (payment_method = 'MOBILE')。

这样可以防止现金明细关联到标记为 MOBILE 的支付记录。不过，“每条 Payment 必须恰好有一条对应明细”属于跨表完整性规则，通常还需要采用以下一种方式来保证：

在同一事务中写入主表和对应的子表。
将存储过程作为唯一写入入口。
使用延迟约束或触发器，具体取决于数据库是否支持。
在应用服务中校验，并通过集成测试覆盖异常路径。

不要分别提交主表和明细表，否则可能留下只有 Payment、没有对应明细的半成品记录。

金额不要重复存储

原结构在 Payment.payment_amount 和各明细表中都保存了金额，例如 cash_amount、cheque_amount 和 mobile_amount。如果这些列表示同一个金额，就应当只保留：

Payment.payment_amount

同一事实保存多次容易造成数据不一致。例如，主表金额是 300.00，现金明细中的金额却是 280.00。

只有字段含义确实不同时，才应该分别保存。例如：

Payment.payment_amount：本次实际用于结算的金额。
CashPaymentDetails.received_amount：顾客交付的现金金额。
CashPaymentDetails.change_amount：找零金额。
MobilePaymentDetails.provider_fee：支付渠道手续费。

列名应准确表达各字段的业务含义，而不是按照支付方式重复保存同一个金额。

哪些字段可以直接放在 Payment 中

并非每种支付方式都需要单独建立子表。如果某种方式没有专属属性，只需记录其类型，那么保存 Payment.payment_method 就够了。

例如，现金支付没有收款人、找零或币种等额外信息时，不需要 CashPaymentDetails：

Payment
├── payment_id
├── order_id
├── payment_amount
├── payment_method = 'CASH'
├── payment_status
└── paid_at

支票号码、银行名称、移动支付交易流水号等支付方式特有的数据，才需要放入相应的子表。

不建议使用通用的明细外键

还有一种看起来比较简洁的方案：

Payment
├── payment_method
└── detail_id

在这种设计中，detail_id 根据 payment_method 指向不同的表。这属于多态外键。大多数关系型数据库无法声明一个“根据另一列动态引用不同表”的外键，因此数据库不能可靠地保证引用完整性。

也不建议使用 EAV 结构保存全部明细：

PaymentAttribute
├── payment_id
├── attribute_name
└── attribute_value

这种设计会削弱数据类型、非空约束、唯一约束和外键约束，查询也会更加复杂。除非支付字段完全动态，无法提前建模，否则使用独立子表更合适。

JSON 列可以保存支付网关返回的非核心原始载荷，但不适合替代需要查询、约束或建立索引的正式业务字段。

关于 NULL 的开销

少量 NULL 通常不是这类设计中最严重的问题，实际存储成本还取决于数据库的行格式和 NULL bitmap。不能仅凭“有两个 NULL”就断定存在明显的性能或空间损失。

更需要关注的是可扩展性和数据完整性。每增加一种支付方式都修改核心表，还要使用越来越复杂的约束来保证只有一个外键有值。即使目前只有三种支付方式，“主表 + 子表”的设计通常也更清晰。

可以按以下原则设计：

共有字段放在 Payment。
使用 payment_method 表示支付方式。
特有字段放在相应的明细子表。
子表以 payment_id 作为主键和外键。
在同一事务中写入主表和明细表。
不要重复保存含义相同的支付金额。
需要数据库强约束时，使用 payment_id + payment_method 复合外键，并根据目标数据库补充触发器或延迟约束。