如何将 bill、invoice 与 payment 表关联

2026-10-24支付阿杰

结论

不建议在 payment 表中同时设置可为空的 bill_id 和 invoice_id，也没必要复制两套字段几乎相同的付款表。

更合适的设计是将支付记录与核销关系分开：

payment 记录实际发生的收付款。
bill 和 invoice 记录应付、应收单据。
中间表记录每笔 payment 核销了哪些单据，以及各自的核销金额。

如果现有模型允许调整，可以再增加统一的单据主表 financial_document，把 bill 和 invoice 作为不同类型的业务单据。这样，核销表只需要一个 document_id，不用维护两个可空外键。

还要先区分两个概念：bill 不是付款记录，invoice 也不是收款记录。它们分别是应付和应收凭证，实际资金流动应记录在 payment 中。

为什么不建议直接使用两个可空外键

下面的结构看上去很简单：

payment (
    id,
    bill_id NULL,
    invoice_id NULL,
    amount
)

实际使用时会遇到这些问题：

必须保证 bill_id 和 invoice_id 有且只有一个有值。
一笔付款不方便核销多张 bill。
一张 invoice 分多次收款时，容易产生重复数据。
部分付款、合并付款和预付款等情况很难自然表达。
将来增加退款、贷项通知单等类型时，还要继续增加可空字段。

可以用 CHECK 约束保证两个字段只能选一个，但这种结构仍然把支付记录和核销关系混在了一起。

推荐方案：统一单据主表

先建立统一的财务单据表：

CREATE TABLE financial_document (
    document_id BIGINT PRIMARY KEY,
    document_type VARCHAR(20) NOT NULL,
    document_number VARCHAR(50) NOT NULL,
    party_id BIGINT NOT NULL,
    document_date DATE NOT NULL,
    currency_code CHAR(3) NOT NULL,
    total_amount DECIMAL(18, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,

    CONSTRAINT chk_document_type
        CHECK (document_type IN ('BILL', 'INVOICE')),

    CONSTRAINT chk_document_amount
        CHECK (total_amount >= 0)
);

bill 和 invoice 各自特有的字段可以放在扩展表中：

CREATE TABLE bill (
    document_id BIGINT PRIMARY KEY,
    vendor_reference VARCHAR(100),
    expense_category VARCHAR(50),

    CONSTRAINT fk_bill_document
        FOREIGN KEY (document_id)
        REFERENCES financial_document(document_id)
);

CREATE TABLE invoice (
    document_id BIGINT PRIMARY KEY,
    customer_purchase_order VARCHAR(100),
    sales_order_id BIGINT,

    CONSTRAINT fk_invoice_document
        FOREIGN KEY (document_id)
        REFERENCES financial_document(document_id)
);

具体约定如下：

financial_document.document_type = 'BILL' 表示应付单据。
financial_document.document_type = 'INVOICE' 表示应收单据。
两三项不同的字段保留在各自的扩展表中。
公共字段只保存一次。

普通外键通常无法保证 bill 所对应的主表记录一定属于 BILL 类型。这项规则需要由触发器、存储过程或应用服务校验，具体方式取决于使用的数据库。

Payment 表只记录资金流动

收款和付款的大部分字段相同，可以共用一张表：

CREATE TABLE payment (
    payment_id BIGINT PRIMARY KEY,
    payment_direction VARCHAR(10) NOT NULL,
    party_id BIGINT NOT NULL,
    payment_date DATE NOT NULL,
    currency_code CHAR(3) NOT NULL,
    amount DECIMAL(18, 2) NOT NULL,
    payment_method VARCHAR(30),
    reference_number VARCHAR(100),
    status VARCHAR(20) NOT NULL,

    CONSTRAINT chk_payment_direction
        CHECK (payment_direction IN ('IN', 'OUT')),

    CONSTRAINT chk_payment_amount
        CHECK (amount > 0)
);

其中：

IN 表示从 customer 收到款项。
OUT 表示向 vendor 支付款项。

不建议只靠金额的正负值判断收付方向。明确设置 payment_direction 后，查询和校验都更直接，也能避免不同业务模块对金额符号作出不同解释。

使用核销表建立多对多关系

一笔 payment 可以核销多张单据，一张单据也可能分多次付款，因此两者是多对多关系：

CREATE TABLE payment_allocation (
    payment_id BIGINT NOT NULL,
    document_id BIGINT NOT NULL,
    allocated_amount DECIMAL(18, 2) NOT NULL,

    PRIMARY KEY (payment_id, document_id),

    CONSTRAINT fk_allocation_payment
        FOREIGN KEY (payment_id)
        REFERENCES payment(payment_id),

    CONSTRAINT fk_allocation_document
        FOREIGN KEY (document_id)
        REFERENCES financial_document(document_id),

    CONSTRAINT chk_allocated_amount
        CHECK (allocated_amount > 0)
);

例如，一笔 1,000 元的付款核销两张 bill：

INSERT INTO payment (
    payment_id,
    payment_direction,
    party_id,
    payment_date,
    currency_code,
    amount,
    status
) VALUES (
    5001,
    'OUT',
    101,
    DATE '2026-09-01',
    'CNY',
    1000.00,
    'POSTED'
);

INSERT INTO payment_allocation (
    payment_id,
    document_id,
    allocated_amount
) VALUES
    (5001, 2001, 600.00),
    (5001, 2002, 400.00);

下面的查询用于计算单据的已核销金额和未结金额：

SELECT
    d.document_id,
    d.document_number,
    d.total_amount,
    COALESCE(SUM(pa.allocated_amount), 0) AS paid_amount,
    d.total_amount - COALESCE(SUM(pa.allocated_amount), 0) AS outstanding_amount
FROM financial_document AS d
LEFT JOIN payment_allocation AS pa
    ON pa.document_id = d.document_id
LEFT JOIN payment AS p
    ON p.payment_id = pa.payment_id
   AND p.status = 'POSTED'
WHERE d.document_id = 2001
GROUP BY
    d.document_id,
    d.document_number,
    d.total_amount;

如果需要排除未入账或已经作废的 payment，聚合时只能计算有效支付。上面的查询还要结合具体数据库和状态定义进行调整，避免在 LEFT JOIN 后误将无效核销计入结果。

暂时不能重构现有表时

如果 bill 和 invoice 已经是两张独立表，短期内又无法增加统一主表，可以保留一张 payment 主表，再建立两张核销表：

CREATE TABLE payment_bill_allocation (
    payment_id BIGINT NOT NULL,
    bill_id BIGINT NOT NULL,
    allocated_amount DECIMAL(18, 2) NOT NULL,
    PRIMARY KEY (payment_id, bill_id)
);

CREATE TABLE payment_invoice_allocation (
    payment_id BIGINT NOT NULL,
    invoice_id BIGINT NOT NULL,
    allocated_amount DECIMAL(18, 2) NOT NULL,
    PRIMARY KEY (payment_id, invoice_id)
);

这个方案与问题中的 option 2 接近，区别在于两张明细表只保存关联信息和核销金额，不重复保存付款日期、支付方式、参考号等公共字段。这些数据统一放在 payment 中。

这种结构虽然多了一张关联表，但外键约束更清楚，通常比在 payment 中设置 bill_id 和 invoice_id 两个可空字段更可靠。

必须增加的业务校验

基础外键无法独立保证财务数据正确。写入核销记录时，至少要检查以下规则：

OUT payment 只能核销 BILL。
IN payment 只能核销 INVOICE。
payment 与单据必须属于同一往来方。
payment 与单据的币种必须相同。如果允许跨币种，还要保存汇率和本位币金额。
单笔 payment 的核销总额不能超过其可用金额。
单据的累计核销金额不能超过其余额，除非业务允许超付。
payment、核销记录和单据状态的更新必须放在同一个数据库事务中。

例如，在完成核销后提交事务：

BEGIN;

INSERT INTO payment_allocation (
    payment_id,
    document_id,
    allocated_amount
) VALUES (
    5001,
    2001,
    600.00
);

-- 在此处校验付款可用余额和单据未结余额，
-- 必要时使用行锁防止并发超额核销。

COMMIT;

如果系统存在并发入账，只在应用层先查询余额再插入记录并不够。应在事务内锁定相关记录，或使用数据库触发器、存储过程等方式完成原子校验。

长期来看，建议采用 financial_document + payment + payment_allocation。如果现有结构暂时不便重构，可以使用一张 payment 主表加两张核销关系表。不要直接在 payment 表中放置 bill_id 和 invoice_id 两个可空外键。