recurring fine payment 的 Database 设计方案

2026-10-24支付阿杰

结论

调整后的三层结构总体可行，但字段设计还需要规范：

Fine Master 保存罚款规则或罚款计划。
Fine 保存各期实际应缴账单，建议改名为 fine_installment 或 fine_charge。
Payments 保存支付交易及其状态。

Monthly、Yearly 和非 recurring fine 可以由同一套模型管理，不必分别设置 for_month 和 for_year。每期账单统一使用 period_start、period_end 和 due_date 表示所属周期。

成功和失败的支付记录不建议拆成两张表。它们都是一次“支付尝试”，可以统一存入 payment 表，再通过 status 区分 PENDING、SUCCEEDED、FAILED、REFUNDED 等状态。这样更便于查询、审计和重试。

is_paid 通常不应作为独立的事实字段保存。某期账单是否付清，应根据成功支付金额、退款金额和应缴金额计算。只有在计算确实影响性能时，才考虑缓存支付状态，而且缓存必须与支付记录在同一事务中更新。

建议的表结构

下面是一种通用设计，具体数据类型需要根据所用数据库调整。

CREATE TABLE fine_plan (
    fine_id         VARCHAR(32) PRIMARY KEY,
    user_id         BIGINT NOT NULL,
    fine_type       SMALLINT NOT NULL,
    amount          DECIMAL(12, 2) NOT NULL,
    recurrence_type VARCHAR(16) NOT NULL,
    start_date      DATE NOT NULL,
    end_date        DATE NULL,
    status          VARCHAR(16) NOT NULL DEFAULT 'ACTIVE',

    CHECK (amount > 0),
    CHECK (recurrence_type IN ('NONE', 'MONTHLY', 'YEARLY')),
    CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED'))
);

每期应缴款单独保存：

CREATE TABLE fine_installment (
    installment_id VARCHAR(32) PRIMARY KEY,
    fine_id         VARCHAR(32) NOT NULL,
    period_start    DATE NOT NULL,
    period_end      DATE NOT NULL,
    due_date        DATE NOT NULL,
    amount_due      DECIMAL(12, 2) NOT NULL,
    status          VARCHAR(16) NOT NULL DEFAULT 'OPEN',

    FOREIGN KEY (fine_id) REFERENCES fine_plan(fine_id),

    CHECK (amount_due > 0),
    CHECK (period_end >= period_start),
    CHECK (status IN ('OPEN', 'PARTIALLY_PAID', 'PAID', 'VOID', 'OVERDUE')),

    UNIQUE (fine_id, period_start, period_end)
);

payment 表保存每次交易尝试：

CREATE TABLE payment (
    payment_id      BIGINT PRIMARY KEY,
    installment_id VARCHAR(32) NOT NULL,
    amount          DECIMAL(12, 2) NOT NULL,
    paymode         VARCHAR(20) NOT NULL,
    txn_id          VARCHAR(100) NOT NULL,
    status          VARCHAR(16) NOT NULL,
    paid_at         TIMESTAMP NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (installment_id)
        REFERENCES fine_installment(installment_id),

    CHECK (amount > 0),
    CHECK (status IN (
        'PENDING',
        'SUCCEEDED',
        'FAILED',
        'CANCELLED',
        'REFUNDED'
    )),

    UNIQUE (txn_id)
);

能否直接把 txn_id 设为全局唯一，取决于支付渠道的约束。如果不同渠道可能生成相同编号，应增加 provider 字段，并建立组合唯一约束：

UNIQUE (provider, txn_id)

如何生成 recurring fine

不要使用 fine_plan.rec_status 判断整笔罚款是否已经支付。对于 recurring fine，需要判断的是每一期账单是否付清。

以 Monthly 计划为例：

fine_plan
F001 | amount=100 | recurrence_type=MONTHLY | status=ACTIVE

系统每月生成一条账单：

fine_installment
RF001 | F001 | 2018-02-01 | 2018-02-28 | amount_due=100
RF004 | F001 | 2018-03-01 | 2018-03-31 | amount_due=100

Yearly 计划使用相同的结构，只是周期跨度为一年：

RF002 | F003 | 2018-01-01 | 2018-12-31 | amount_due=10000

非 recurring fine 只生成一条账单：

RF003 | F002 | 2018-12-02 | 2018-12-02 | amount_due=10000

账单可以由定时任务生成，也可以在创建罚款时预先生成。通常更适合按周期提前生成，以免无限创建未来账单。

以下唯一约束需要保留：

UNIQUE (fine_id, period_start, period_end)

即使定时任务重复执行，这个约束也能防止系统为同一计划的同一周期创建两条账单。

不要使用 `for_month` 和 `for_year`

下面这种设计不够稳妥：

for_month = 022018
for_year  = NULL

它存在几个问题：

022018 的含义依赖应用内部约定。
月度和年度账单需要查询不同字段。
难以扩展到季度、周或自定义周期。
无法清楚表示账单的起止边界。
字符串或数字格式容易引发排序和日期计算错误。

统一使用标准日期更可靠：

period_start = 2018-02-01
period_end   = 2018-02-28

这种设计也可以直接支持按日期范围查询。

另外，示例中的 Fine Master.entry_date 是 2018-12-01，对应的账单周期和支付时间却在 2018-02。如果这不是演示数据，日期之间就存在业务矛盾。需要分别明确 entry_date、start_date 和账单周期的含义，并增加相应校验。

如何判断某一期是否付清

仅检查 payments 中是否存在记录并不可靠，原因包括：

记录状态可能是 FAILED 或 PENDING。
支付金额可能低于应缴金额。
一期账单可能分多次支付。
成功交易之后可能发生退款。
同一次支付请求可能因重试产生重复回调。

更可靠的方法是汇总有效的成功支付金额：

SELECT
    fi.installment_id,
    fi.amount_due,
    COALESCE(
        SUM(
            CASE
                WHEN p.status = 'SUCCEEDED' THEN p.amount
                WHEN p.status = 'REFUNDED' THEN -p.amount
                ELSE 0
            END
        ),
        0
    ) AS amount_paid
FROM fine_installment fi
LEFT JOIN payment p
    ON p.installment_id = fi.installment_id
WHERE fi.installment_id = 'RF001'
GROUP BY fi.installment_id, fi.amount_due;

再判断：

amount_paid >= amount_due

如果系统允许部分退款，最好单独设计 payment_refund 表，因为一笔支付可能发生多次部分退款。此时不能简单地把整条支付记录改成 REFUNDED。

也可以直接查询尚未付清的账单：

SELECT
    fi.installment_id,
    fi.fine_id,
    fi.due_date,
    fi.amount_due,
    COALESCE(SUM(
        CASE
            WHEN p.status = 'SUCCEEDED' THEN p.amount
            ELSE 0
        END
    ), 0) AS amount_paid
FROM fine_installment fi
LEFT JOIN payment p
    ON p.installment_id = fi.installment_id
GROUP BY
    fi.installment_id,
    fi.fine_id,
    fi.due_date,
    fi.amount_due
HAVING COALESCE(SUM(
    CASE
        WHEN p.status = 'SUCCEEDED' THEN p.amount
        ELSE 0
    END
), 0) < fi.amount_due;

`is_paid` 是否应该保存

优先考虑不保存 is_paid，而是根据支付记录计算。这样可以避免出现以下矛盾：

is_paid = TRUE

但实际并没有成功支付记录，或者支付已经退款。

如果支付量很大，实时汇总确实影响性能，可以在 fine_installment 中缓存 status 和 amount_paid：

amount_paid DECIMAL(12, 2) NOT NULL DEFAULT 0

采用缓存后，需要满足以下条件：

支付状态变化和账单汇总更新必须在同一数据库事务中完成。
支付回调必须具有幂等性。
定期执行对账任务，修正缓存与明细不一致的问题。
不允许客户端直接提交 is_paid。
状态应由金额推导，不能由调用方任意设置。

例如：

amount_paid = 0                  -> OPEN
0 < amount_paid < amount_due    -> PARTIALLY_PAID
amount_paid >= amount_due       -> PAID

与单独的布尔值相比，status 加 amount_paid 可以正确表达部分支付、作废和逾期等情况。

支付失败记录的处理

把失败交易单独建表并非完全错误，但通常没有必要。成功和失败记录都有相同的核心属性：

对应哪一期账单；
请求支付的金额；
使用的支付方式；
第三方交易号；
当前交易状态；
创建及完成时间；
失败原因。

统一放入 payment 表，更容易跟踪一笔交易从 PENDING 到最终状态的变化：

UPDATE payment
SET status = 'SUCCEEDED',
    paid_at = CURRENT_TIMESTAMP
WHERE txn_id = 'TXN001'
  AND status = 'PENDING';

还可以增加失败信息字段：

failure_code    VARCHAR(50) NULL,
failure_message VARCHAR(500) NULL

如果需要完整保留支付网关的每次回调及状态变化，可以再增加支付事件表：

CREATE TABLE payment_event (
    event_id    BIGINT PRIMARY KEY,
    payment_id  BIGINT NOT NULL,
    status      VARCHAR(16) NOT NULL,
    event_time  TIMESTAMP NOT NULL,
    raw_payload TEXT NULL,

    FOREIGN KEY (payment_id) REFERENCES payment(payment_id)
);

还需要注意的边界情况

Admin 将 recurring 计划设为 COMPLETED 后，应停止生成未来账单，但通常不应删除已经生成且尚未支付的账单。
如果“完成”也表示免除历史欠款，应明确把相关账单改为 VOID，不能直接删除。
修改计划金额时，需要明确修改只影响未来账单，还是也会调整当前账单。历史已支付账单一般不应被覆盖。
支付回调可能重复到达，应使用 provider + txn_id 或支付请求的幂等键防止重复入账。
金额应使用 DECIMAL，不要使用 FLOAT 或 DOUBLE。
如果涉及多币种，应增加 currency，并要求账单和支付使用相同币种。
rec_status 更适合改名为计划级别的 status。账单是否支付属于 fine_installment 层级，不应放在计划主表中。

修改后的设计可以继续使用“三张表”的整体结构，但第二张表应明确建模为“应缴期次”，通过日期区间统一 Monthly、Yearly 和非 recurring 场景，并把所有支付尝试集中存入同一张支付表。