Payments Database Design：如何记录月费与额外付款

2026-10-24支付老李

结论

不要在 DUES 表里用一个反复修改的 Credit 字段记录多付款，也不要用 PAID 布尔值表示费用状态。

建议分别记录以下数据：

月度应收：会员某个月应付的金额。
实际付款：会员付款的时间和金额。
付款分配：每笔付款抵扣了哪些月份，以及各自抵扣多少。
未分配余额：付款中还没有用于抵扣应收的金额，也就是会员当前可用的余额。

这样的设计能够处理部分付款、一次支付多个月、超额付款、退款、方案调价和历史对账。

为什么不能只使用 `PAID` 和 `Credit`

PAID 只能表示”是”或”否”，无法准确描述中间状态。例如，当月应付 $25.00，会员只支付了 $10.00，此时既不能算已付清，也不能简单归为未付款。

直接维护 Credit 字段也很容易造成数据不一致。假设会员多付了 $5.00，下个月抵扣时，程序必须同时更新应收记录和 Credit。其中任何一次更新失败，都可能导致余额错误。

更可靠的方式是保留每笔原始业务记录，再通过汇总计算余额。为了提高查询速度，可以缓存余额，但缓存不能作为唯一的数据依据。

推荐的数据结构

至少可以拆分为以下几类表。

会员和方案

CREATE TABLE members (
    member_id       BIGINT PRIMARY KEY,
    member_name     VARCHAR(200) NOT NULL
);

CREATE TABLE plan_types (
    plan_id         BIGINT PRIMARY KEY,
    plan_name       VARCHAR(100) NOT NULL,
    current_cost    DECIMAL(12, 2) NOT NULL,
    CHECK (current_cost >= 0)
);

会员可能更换方案，因此最好单独保存每个方案的有效期，而不是只在 members 表中设置一个会覆盖旧值的 plan_id。

CREATE TABLE member_plans (
    member_plan_id  BIGINT PRIMARY KEY,
    member_id       BIGINT NOT NULL,
    plan_id         BIGINT NOT NULL,
    effective_from  DATE NOT NULL,
    effective_to    DATE NULL,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (plan_id) REFERENCES plan_types(plan_id),
    CHECK (effective_to IS NULL OR effective_to >= effective_from)
);

月度应收表

每个月生成一条应收记录，并将当时的费用写入 amount_due。不要等到查询时再读取 plan_types.current_cost，否则方案价格调整后，历史月份的费用也会跟着变化。

CREATE TABLE monthly_dues (
    due_id          BIGINT PRIMARY KEY,
    member_id       BIGINT NOT NULL,
    plan_id         BIGINT NOT NULL,
    billing_month   DATE NOT NULL,
    due_date        DATE NOT NULL,
    amount_due      DECIMAL(12, 2) NOT NULL,
    description     VARCHAR(500) NULL,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (plan_id) REFERENCES plan_types(plan_id),
    UNIQUE (member_id, billing_month),
    CHECK (amount_due >= 0)
);

可以约定 billing_month 始终保存当月第一天，例如 2026-09-01。唯一约束能防止为同一会员重复生成同一个月的费用。

付款表

付款是单独发生的交易，不应直接覆盖应收记录。

CREATE TABLE payments (
    payment_id      BIGINT PRIMARY KEY,
    member_id       BIGINT NOT NULL,
    payment_date    TIMESTAMP NOT NULL,
    amount_paid     DECIMAL(12, 2) NOT NULL,
    payment_method  VARCHAR(50) NULL,
    reference_no    VARCHAR(100) NULL,
    description     VARCHAR(500) NULL,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    CHECK (amount_paid > 0)
);

付款分配表

一笔付款可以抵扣多个账期，一个账期也可能由多笔付款共同付清。两者属于多对多关系，需要用分配表建立关联。

CREATE TABLE payment_allocations (
    allocation_id    BIGINT PRIMARY KEY,
    payment_id       BIGINT NOT NULL,
    due_id           BIGINT NOT NULL,
    allocated_amount DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (payment_id) REFERENCES payments(payment_id),
    FOREIGN KEY (due_id) REFERENCES monthly_dues(due_id),
    UNIQUE (payment_id, due_id),
    CHECK (allocated_amount > 0)
);

如何计算应付、已付和可用余额

某个月的尚欠金额，等于当月应收减去已经分配到该账期的付款：

SELECT
    d.due_id,
    d.member_id,
    d.billing_month,
    d.amount_due,
    COALESCE(SUM(a.allocated_amount), 0) AS amount_applied,
    d.amount_due - COALESCE(SUM(a.allocated_amount), 0) AS amount_outstanding
FROM monthly_dues AS d
LEFT JOIN payment_allocations AS a
    ON a.due_id = d.due_id
WHERE d.member_id = :member_id
GROUP BY
    d.due_id,
    d.member_id,
    d.billing_month,
    d.amount_due;

会员的可用余额，也就是已经收到但尚未分配的付款，可以这样计算：

SELECT
    p.member_id,
    SUM(p.amount_paid) - COALESCE(SUM(x.allocated_amount), 0) AS available_credit
FROM payments AS p
LEFT JOIN (
    SELECT
        payment_id,
        SUM(allocated_amount) AS allocated_amount
    FROM payment_allocations
    GROUP BY payment_id
) AS x
    ON x.payment_id = p.payment_id
WHERE p.member_id = :member_id
GROUP BY p.member_id;

available_credit 直接根据付款和分配记录计算，无须人工增加或扣减 Credit 字段。

超额付款的处理示例

假设会员九月份应付 $25.00，实际支付了 $40.00：

在 monthly_dues 中记录九月份应收 $25.00。
在 payments 中记录实际付款 $40.00。
在 payment_allocations 中把其中的 $25.00 分配给九月份应收。
剩余的 $15.00 暂时不分配，它会自然成为可用余额。

十月份生成 $25.00 的应收记录后，可以把原付款剩余的 $15.00 分配给十月份。分配完成后，十月份仍欠 $10.00。

INSERT INTO payment_allocations (
    allocation_id,
    payment_id,
    due_id,
    allocated_amount
) VALUES (
    :allocation_id,
    :september_payment_id,
    :october_due_id,
    15.00
);

如果业务允许预付未来月份，可以在收到付款时提前生成未来应收并立即分配。如果不允许，就让多付的金额保持未分配状态，等下个月的应收生成后再抵扣。

状态不必直接存储

PAID 可以作为查询结果生成，不必持久化：

amount_applied = 0：未付款。
0 < amount_applied < amount_due：部分付款。
amount_applied = amount_due：已付清。
amount_applied > amount_due：数据异常，需要通过约束或业务逻辑阻止。

例如：

CASE
    WHEN amount_applied = 0 THEN 'UNPAID'
    WHEN amount_applied < amount_due THEN 'PARTIALLY_PAID'
    WHEN amount_applied = amount_due THEN 'PAID'
    ELSE 'OVER_ALLOCATED'
END

如果出于报表性能考虑，需要保存状态字段，就必须在同一个数据库事务中同步更新，并定期根据应收和分配记录重新核对。

实现时需要注意

所有金额都应使用 DECIMAL，或者使用以最小货币单位保存的整数。不能使用 FLOAT 或 DOUBLE，否则可能产生舍入误差。

创建付款分配时，还需要验证以下条件：

分配总额不能超过该笔付款尚未分配的金额。
分配后，该账期的已分配金额不能超过应收金额。
payment.member_id 必须与 monthly_dues.member_id 相同。
付款、分配和余额更新应放在同一个事务中，并锁定相关记录，避免并发操作重复使用同一笔余额。

退款、撤销和减免也不适合通过删除或覆盖历史数据来处理。可以分别记录负向交易、退款记录或调整记录，同时保存操作时间、原因和关联交易，方便后续审计。

这种设计把”应收”和”实收”作为两类独立事实，再通过付款分配建立联系。多付金额无须使用特殊字段手工维护，它就是实际付款总额减去已分配总额后的结果。