PAYATHON 2026

在关联表中使用 NULL 设计支付交易关系

支付阿杰

结论

多个可空外键再加上”只能有一个非 NULL”的约束，在数据库设计中称为互斥关联，也叫 exclusive arc。这种设计适合目标类型较少且长期稳定的场景，不适合付款原因不断增加的系统。

如果 driver_tx 每增加一种业务原因就新增一个可空列，会产生以下问题：

  • 表结构会随着业务类型增加而不断膨胀。
  • 约束会越来越复杂，容易出现字段全部为 NULL，或者多个字段同时非 NULL 的情况。
  • 查询和索引需要处理大量类型判断。
  • reservation_item_id 通常已经能够确定 reservation_id，同时保存两者可能导致数据不一致。
  • 如果一笔付款需要结算多个项目，单行互斥外键无法表示这种关系。

更合适的做法是将三个概念分开：

  1. 谁付款，谁收款。
  2. 为什么付款，也就是对应哪些应收、应付或结算项目。
  3. 某笔付款具体结算了哪些项目。

付款与结算项目通常是多对多关系。

统一付款参与方

如果 driver、agent、customer 等对象都可能成为付款方或收款方，可以建立统一的 party 主体表，再用子类型表保存各类主体的专有属性。

CREATE TABLE party (
    party_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    party_type VARCHAR(32) NOT NULL,
    PRIMARY KEY (party_id)
) ENGINE=InnoDB;

CREATE TABLE driver (
    party_id BIGINT UNSIGNED NOT NULL,
    driver_name VARCHAR(200) NOT NULL,
    PRIMARY KEY (party_id),
    CONSTRAINT fk_driver_party
        FOREIGN KEY (party_id) REFERENCES party (party_id)
) ENGINE=InnoDB;

CREATE TABLE agent (
    party_id BIGINT UNSIGNED NOT NULL,
    agent_name VARCHAR(200) NOT NULL,
    PRIMARY KEY (party_id),
    CONSTRAINT fk_agent_party
        FOREIGN KEY (party_id) REFERENCES party (party_id)
) ENGINE=InnoDB;

付款表只需引用付款方和收款方，不必为 driver、agent 分别建立交易表：

CREATE TABLE payment_tx (
    tx_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    payer_party_id BIGINT UNSIGNED NOT NULL,
    payee_party_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(19, 4) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    tx_status VARCHAR(32) NOT NULL,
    occurred_at DATETIME NOT NULL,
    PRIMARY KEY (tx_id),
    CONSTRAINT chk_payment_amount
        CHECK (amount > 0),
    CONSTRAINT chk_payment_parties
        CHECK (payer_party_id <> payee_party_id),
    CONSTRAINT fk_payment_payer
        FOREIGN KEY (payer_party_id) REFERENCES party (party_id),
    CONSTRAINT fk_payment_payee
        FOREIGN KEY (payee_party_id) REFERENCES party (party_id)
) ENGINE=InnoDB;

如果系统允许付款方或收款方不存在，例如付款人是身份未知的外部人员，那么相应外键是否允许为空，应根据业务规则决定，不要只为使用方便而将其设为 NULL。

将付款原因建模为结算项目

付款交易本身不应直接承载所有业务来源。可以建立 accounting_item，用于表示需要结算的应收或应付项目。

CREATE TABLE accounting_item (
    accounting_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    debtor_party_id BIGINT UNSIGNED NOT NULL,
    creditor_party_id BIGINT UNSIGNED NOT NULL,
    item_type VARCHAR(32) NOT NULL,
    amount DECIMAL(19, 4) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    item_status VARCHAR(32) NOT NULL,
    PRIMARY KEY (accounting_item_id),
    CONSTRAINT chk_accounting_item_amount
        CHECK (amount > 0),
    CONSTRAINT fk_item_debtor
        FOREIGN KEY (debtor_party_id) REFERENCES party (party_id),
    CONSTRAINT fk_item_creditor
        FOREIGN KEY (creditor_party_id) REFERENCES party (party_id)
) ENGINE=InnoDB;

不同业务来源的数据保存在各自的子类型表中。整笔 reservation 的司机费用可以这样表示：

CREATE TABLE reservation_driver_item (
    accounting_item_id BIGINT UNSIGNED NOT NULL,
    reservation_id BIGINT UNSIGNED NOT NULL,
    driver_party_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (accounting_item_id),
    CONSTRAINT fk_reservation_driver_item
        FOREIGN KEY (accounting_item_id)
        REFERENCES accounting_item (accounting_item_id),
    CONSTRAINT fk_reservation_driver_reservation
        FOREIGN KEY (reservation_id)
        REFERENCES reservation (reservation_id),
    CONSTRAINT fk_reservation_driver_driver
        FOREIGN KEY (driver_party_id)
        REFERENCES driver (party_id)
) ENGINE=InnoDB;

针对单个 reservation item 的费用，则使用另一张表：

CREATE TABLE reservation_item_driver_item (
    accounting_item_id BIGINT UNSIGNED NOT NULL,
    reservation_item_id BIGINT UNSIGNED NOT NULL,
    driver_party_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (accounting_item_id),
    CONSTRAINT fk_item_driver_accounting_item
        FOREIGN KEY (accounting_item_id)
        REFERENCES accounting_item (accounting_item_id),
    CONSTRAINT fk_item_driver_reservation_item
        FOREIGN KEY (reservation_item_id)
        REFERENCES reservation_item (reservation_item_id),
    CONSTRAINT fk_item_driver_driver
        FOREIGN KEY (driver_party_id)
        REFERENCES driver (party_id)
) ENGINE=InnoDB;

如果 reservation_item 已经引用了 reservation，这里就不必再次保存 reservation_id。查询时可以通过 reservation_item 找到所属的 reservation，也能避免两个字段指向不同数据。

用分配表连接付款与结算项目

一笔付款可能支付多个 reservation item，一个费用项目也可能分成几次支付，因此两者之间应通过分配表建立关系：

CREATE TABLE payment_allocation (
    tx_id BIGINT UNSIGNED NOT NULL,
    accounting_item_id BIGINT UNSIGNED NOT NULL,
    allocated_amount DECIMAL(19, 4) NOT NULL,
    PRIMARY KEY (tx_id, accounting_item_id),
    CONSTRAINT chk_allocated_amount
        CHECK (allocated_amount > 0),
    CONSTRAINT fk_allocation_tx
        FOREIGN KEY (tx_id)
        REFERENCES payment_tx (tx_id),
    CONSTRAINT fk_allocation_item
        FOREIGN KEY (accounting_item_id)
        REFERENCES accounting_item (accounting_item_id)
) ENGINE=InnoDB;

这个结构可以表示：

  • 一笔付款结算整个 reservation。
  • 一笔付款结算多个 reservation item。
  • 一个 reservation item 分多次付款。
  • 司机因 reservation 之外的原因获得付款。
  • 一笔付款只支付某个应付项目的一部分。

新增付款原因时，只需增加相应的 accounting_item 子类型表，不必修改核心付款表。

处理信用卡和支票信息

tx_cc 和 tx_ch 可以继续作为付款方式的子类型表，前提是每笔付款最多使用一种方式，而且它们的 tx_id 同时作为主键和外键：

CREATE TABLE tx_cc (
    tx_id BIGINT UNSIGNED NOT NULL,
    credit_card_id BIGINT UNSIGNED NOT NULL,
    authorization_code VARCHAR(100) NULL,
    PRIMARY KEY (tx_id),
    CONSTRAINT fk_tx_cc_tx
        FOREIGN KEY (tx_id) REFERENCES payment_tx (tx_id),
    CONSTRAINT fk_tx_cc_card
        FOREIGN KEY (credit_card_id) REFERENCES credit_card (credit_card_id)
) ENGINE=InnoDB;

CREATE TABLE tx_ch (
    tx_id BIGINT UNSIGNED NOT NULL,
    bank_account_id BIGINT UNSIGNED NOT NULL,
    check_number VARCHAR(100) NOT NULL,
    PRIMARY KEY (tx_id),
    CONSTRAINT fk_tx_ch_tx
        FOREIGN KEY (tx_id) REFERENCES payment_tx (tx_id),
    CONSTRAINT fk_tx_ch_account
        FOREIGN KEY (bank_account_id) REFERENCES bank_account (bank_account_id)
) ENGINE=InnoDB;

仅靠这两张表，无法阻止同一个 tx_id 同时出现在 tx_cc 和 tx_ch 中。可以在 payment_tx 中增加付款方式类型，并通过事务、存储过程或触发器维护完整性。

如果一笔付款允许混合使用信用卡、支票或余额，就不应将付款方式设计成互斥子类型。此时可以增加 payment_component，让一笔交易包含多个付款组成部分。

暂时保留可空外键时的做法

如果业务规模较小，而且关联类型确定不会继续增加，可以保留现有结构，但必须明确声明”恰好一个字段非 NULL”：

CREATE TABLE driver_tx (
    tx_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NOT NULL,
    reservation_id BIGINT UNSIGNED NULL,
    reservation_item_id BIGINT UNSIGNED NULL,
    PRIMARY KEY (tx_id),

    CONSTRAINT chk_driver_tx_target
        CHECK (
            (reservation_id IS NOT NULL AND reservation_item_id IS NULL)
            OR
            (reservation_id IS NULL AND reservation_item_id IS NOT NULL)
        ),

    CONSTRAINT fk_driver_tx_tx
        FOREIGN KEY (tx_id) REFERENCES payment_tx (tx_id),

    CONSTRAINT fk_driver_tx_driver
        FOREIGN KEY (driver_id) REFERENCES driver (party_id),

    CONSTRAINT fk_driver_tx_reservation
        FOREIGN KEY (reservation_id) REFERENCES reservation (reservation_id),

    CONSTRAINT fk_driver_tx_reservation_item
        FOREIGN KEY (reservation_item_id)
        REFERENCES reservation_item (reservation_item_id)
) ENGINE=InnoDB;

如果允许两个字段同时为 NULL，规则就变成”至多一个非 NULL”，约束也需要相应调整。

需要注意的是，MySQL 8.0.16 之前的版本虽然会解析 CHECK，但不会执行该约束。如果使用较早版本，只能通过触发器或应用层事务保证这项规则。具体行为应以实际使用的 MySQL 版本为准。

关于 OUTER JOIN

表多并不表示设计有问题，JOIN 也不是应该优先避免的操作。规范化设计本来就需要将不同事实放在不同表中。常用查询可以通过视图封装，例如把不同结算来源统一成一个查询接口：

CREATE VIEW accounting_item_source AS
SELECT
    rdi.accounting_item_id,
    'RESERVATION' AS source_type,
    rdi.reservation_id AS source_id
FROM reservation_driver_item AS rdi

UNION ALL

SELECT
    lidi.accounting_item_id,
    'RESERVATION_ITEM' AS source_type,
    lidi.reservation_item_id AS source_id
FROM reservation_item_driver_item AS lidi;

查询某一种来源时，只需连接对应的子类型表，不必将所有表都 OUTER JOIN 一遍。只有生成跨类型报表时，才需要通过视图或 UNION ALL 汇总。

不要使用下面这种看起来简洁的多态外键：

source_type VARCHAR(32),
source_id BIGINT

如果 source_id 根据 source_type 指向不同的表，数据库就无法为它声明普通外键，删除业务对象时也很难保证引用完整性。除非系统明确接受由应用层承担全部约束，否则使用结算项目主表配合来源子类型表更稳妥。

还需约束的会计规则

基础外键和 CHECK 只能保证单行数据合法。以下规则涉及多行聚合，通常需要在同一个数据库事务中检查，或者由触发器、存储过程维护：

  • 同一付款的 allocated_amount 总和不能超过付款金额。
  • 同一结算项目的分配总额不能超过其应付金额，除非业务允许超付。
  • 付款与结算项目的币种必须一致，否则必须记录汇率和换算金额。
  • 付款方、收款方应与结算项目中的债务方、债权方一致。
  • 已记账交易不宜直接修改或删除。冲正通常应新增反向记录，并保留与原交易的审计关系。

推荐的核心关系如下：

party
  └── driver / agent / customer

payment_tx
  ├── payer_party_id
  └── payee_party_id

accounting_item
  └── 各类业务来源子表

payment_tx
  └── payment_allocation
          └── accounting_item

这种结构将”钱如何支付”、“谁付给谁”和”这笔钱结算什么”分别建模。新增业务原因时，不需要继续向核心交易表添加可空字段，同时也能处理拆分付款、合并付款和部分付款。

备注：内容仅供参考。