PAYATHON 2026

如何设计支持定期费用与部分付款的 payments database？

支付阿杰

结论

现有设计的方向没有完全错，但 SCHEDULED_PAYMENTS 和 LEDGER 的职责不够明确，难以准确回答三个问题：应该收什么、实际收了多少，以及这笔钱支付了哪些费用。

建议把收费系统拆成四个核心概念：

  1. 报名关系（enrollment）：学生何时加入、何时退出某个课程。
  2. 应收费用（charge）：某月学费或一次性报名费，应收多少、何时到期。
  3. 实际收款（payment）：学校实际收到的钱。
  4. 付款分配（payment_allocation）：一笔收款分别冲抵了哪些应收费用。

charge 和 payment 不能合并。部分付款、一笔钱支付多个月的费用、退款和费用减免，都需要用单独的分配记录或调整记录来表示。

推荐的数据模型

下面的示例使用 PostgreSQL 风格的 SQL，并假设系统目前只使用一种货币。如果需要支持多币种，应在费用表和付款表中增加 currency_code，同时禁止不同币种之间直接冲抵。

CREATE TABLE student (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        varchar(200) NOT NULL
);

CREATE TABLE course (
    id                  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name                varchar(200) NOT NULL,
    inscription_fee     numeric(12, 2) NOT NULL CHECK (inscription_fee >= 0),
    monthly_fee         numeric(12, 2) NOT NULL CHECK (monthly_fee >= 0)
);

CREATE TABLE enrollment (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id      bigint NOT NULL REFERENCES student(id),
    course_id       bigint NOT NULL REFERENCES course(id),
    enrolled_on     date NOT NULL,
    withdrawn_on    date,
    status          varchar(20) NOT NULL
                    CHECK (status IN ('active', 'completed', 'withdrawn')),
    CHECK (withdrawn_on IS NULL OR withdrawn_on >= enrolled_on)
);

enrollment 不能省略。同一个学生可能退出后重新报名，也可能重复修读同一门课程。因此，费用应关联某一次具体的报名，而不能只关联 student_id 和 course_id。

每一笔已经确定的应收费用都单独保存：

CREATE TABLE charge (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    enrollment_id   bigint NOT NULL REFERENCES enrollment(id),
    charge_type     varchar(20) NOT NULL
                    CHECK (charge_type IN ('inscription', 'monthly', 'other')),
    period_start    date,
    period_end      date,
    due_on          date NOT NULL,
    description     varchar(500) NOT NULL,
    original_amount numeric(12, 2) NOT NULL CHECK (original_amount >= 0),
    status          varchar(20) NOT NULL DEFAULT 'open'
                    CHECK (status IN ('open', 'cancelled')),
    created_at      timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (
        (period_start IS NULL AND period_end IS NULL)
        OR
        (period_start IS NOT NULL
         AND period_end IS NOT NULL
         AND period_end >= period_start)
    )
);

学校实际收到的钱记录在 payment 中：

CREATE TABLE payment (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id      bigint NOT NULL REFERENCES student(id),
    received_on     date NOT NULL,
    amount          numeric(12, 2) NOT NULL CHECK (amount > 0),
    payment_method  varchar(30),
    reference_no    varchar(100),
    status          varchar(20) NOT NULL DEFAULT 'posted'
                    CHECK (status IN ('posted', 'voided')),
    created_at      timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

一笔付款可以冲抵多笔费用，一笔费用也可以通过多次付款结清，所以两者是多对多关系：

CREATE TABLE payment_allocation (
    payment_id      bigint NOT NULL REFERENCES payment(id),
    charge_id       bigint NOT NULL REFERENCES charge(id),
    amount          numeric(12, 2) NOT NULL CHECK (amount > 0),
    PRIMARY KEY (payment_id, charge_id)
);

例如，某月学费为 1,000 元，学生先支付 400 元，之后再支付 600 元。这时可以写入两条 payment，并分别创建对应的 payment_allocation。费用记录不需要增加“部分付款”状态，因为余额可以直接计算出来。

如何处理费用减免

不要修改课程的标准价格。course.monthly_fee 表示课程的默认收费标准，并不等于某个学生最终需要支付的金额。

费用减免通常有两种建模方式。

简单方案：生成费用时直接使用优惠后的金额

如果只需要知道学生最终应付多少，可以让 charge.original_amount 直接保存减免后的金额，再额外记录标准金额和减免原因：

ALTER TABLE charge
    ADD COLUMN list_amount numeric(12, 2)
        CHECK (list_amount IS NULL OR list_amount >= 0),
    ADD COLUMN discount_reason varchar(500);

例如，标准月费为 1,000 元，减免 200 元：

list_amount     = 1000.00
original_amount = 800.00
discount_reason = '家庭经济困难减免'

这种方式实现简单，但不便于记录多次调整、减免撤销或完整的审计历史。

可审计方案：单独记录费用调整

如果财务记录需要保留完整的变更历史，可以增加调整表：

CREATE TABLE charge_adjustment (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    charge_id       bigint NOT NULL REFERENCES charge(id),
    amount          numeric(12, 2) NOT NULL CHECK (amount <> 0),
    reason          varchar(500) NOT NULL,
    created_at      timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

具体约定如下：

  • 正数表示增加应收。
  • 负数表示减免或贷记。
  • 不直接修改原始费用。
  • 每次调整都通过新增记录完成。

某笔费用的实际应收金额为：

original_amount + SUM(charge_adjustment.amount)

定期费用如何生成

“每月收费”是一条生成规则，而每个月实际产生的应收费用是独立的业务事实。不要只保存一个 monthly_fee，然后在每次查询时根据日历临时推算全部欠费。

可以按下面的流程处理：

  1. 学生报名时生成一次性报名费。
  2. 根据学校日历生成每个收费周期的月费。
  3. 每条月费都有自己的 period_start、period_end 和 due_on。
  4. 使用唯一约束，防止定时任务重复生成费用。

例如：

CREATE UNIQUE INDEX uq_monthly_charge
ON charge (enrollment_id, charge_type, period_start)
WHERE charge_type = 'monthly' AND status <> 'cancelled';

生成一笔月费的示例：

INSERT INTO charge (
    enrollment_id,
    charge_type,
    period_start,
    period_end,
    due_on,
    description,
    original_amount
)
VALUES (
    42,
    'monthly',
    DATE '2026-09-01',
    DATE '2026-09-30',
    DATE '2026-09-10',
    '2026 年 9 月课程费用',
    1000.00
);

如果收费周期与自然月不同，应以学校日历为准。最好再建立 billing_period 表，用来保存每个学期的收费周期和截止日期，不要依赖数据库按自然月计算。

查询哪些费用已付、未付或逾期

可以先建立费用余额视图，将付款分配和费用调整聚合起来：

CREATE VIEW charge_balance AS
SELECT
    c.id AS charge_id,
    c.enrollment_id,
    c.charge_type,
    c.period_start,
    c.period_end,
    c.due_on,
    c.description,
    c.status,
    c.original_amount
        + COALESCE(a.adjustment_amount, 0) AS amount_due,
    COALESCE(p.paid_amount, 0) AS amount_paid,
    c.original_amount
        + COALESCE(a.adjustment_amount, 0)
        - COALESCE(p.paid_amount, 0) AS balance
FROM charge c
LEFT JOIN (
    SELECT charge_id, SUM(amount) AS adjustment_amount
    FROM charge_adjustment
    GROUP BY charge_id
) a ON a.charge_id = c.id
LEFT JOIN (
    SELECT pa.charge_id, SUM(pa.amount) AS paid_amount
    FROM payment_allocation pa
    JOIN payment p ON p.id = pa.payment_id
    WHERE p.status = 'posted'
    GROUP BY pa.charge_id
) p ON p.charge_id = c.id;

查询某个学生的全部费用和付款情况：

SELECT
    cb.charge_id,
    co.name AS course_name,
    cb.description,
    cb.due_on,
    cb.amount_due,
    cb.amount_paid,
    cb.balance,
    CASE
        WHEN cb.status = 'cancelled' THEN 'cancelled'
        WHEN cb.balance <= 0 THEN 'paid'
        WHEN cb.amount_paid > 0 AND cb.due_on < CURRENT_DATE THEN 'partially_paid_overdue'
        WHEN cb.amount_paid > 0 THEN 'partially_paid'
        WHEN cb.due_on < CURRENT_DATE THEN 'overdue'
        ELSE 'unpaid'
    END AS payment_state
FROM charge_balance cb
JOIN enrollment e ON e.id = cb.enrollment_id
JOIN course co ON co.id = e.course_id
WHERE e.student_id = 123
ORDER BY cb.due_on, cb.charge_id;

只查询已经到期但尚未结清的费用：

SELECT
    cb.charge_id,
    cb.description,
    cb.due_on,
    cb.amount_due,
    cb.amount_paid,
    cb.balance
FROM charge_balance cb
JOIN enrollment e ON e.id = cb.enrollment_id
WHERE e.student_id = 123
  AND cb.status = 'open'
  AND cb.due_on <= CURRENT_DATE
  AND cb.balance > 0
ORDER BY cb.due_on;

查询学生尚未分配的付款余额：

SELECT
    p.id,
    p.received_on,
    p.amount,
    p.amount - COALESCE(SUM(pa.amount), 0) AS unallocated_amount
FROM payment p
LEFT JOIN payment_allocation pa ON pa.payment_id = p.id
WHERE p.student_id = 123
  AND p.status = 'posted'
GROUP BY p.id, p.received_on, p.amount
HAVING p.amount - COALESCE(SUM(pa.amount), 0) > 0;

学生中途退出时如何处理

学生退出时，应更新对应的 enrollment，不要删除学生、报名记录或历史付款：

UPDATE enrollment
SET withdrawn_on = DATE '2026-10-15',
    status = 'withdrawn'
WHERE id = 42;

之后再按照学校的退费规则处理相关费用：

  • 停止生成尚未产生的未来月费。
  • 已经生成但尚未生效的月费，可以标记为 cancelled。
  • 已经到期的费用通常继续保留。
  • 如果需要按比例收费，则新增调整记录，冲减不需要支付的部分。
  • 如果学生已经多付，可以记录退款，也可以将多付金额保留为未分配余额。

不要直接删除已经入账的费用、付款或分配记录，否则以后很难解释余额变化的原因。实际系统在处理作废、退款和冲正时，通常会使用反向记录或明确的状态字段来保留审计轨迹。

还需要注意的约束

数据库应防止分配金额超过付款金额，也应避免将一笔付款分配给其他学生的费用。这些约束涉及多张表，普通 CHECK 无法实现，通常需要在事务中校验，必要时还可以使用触发器。

一次付款分配的完整操作应放在同一个事务中：

BEGIN;

-- 锁定付款，避免并发重复分配
SELECT id, amount
FROM payment
WHERE id = 9001
  AND status = 'posted'
FOR UPDATE;

-- 应用层在这里校验：
-- 1. payment 与 charge 属于同一学生
-- 2. 本次分配不超过付款的未分配余额
-- 3. 本次分配不超过费用的未结余额

INSERT INTO payment_allocation (payment_id, charge_id, amount)
VALUES (9001, 501, 400.00);

COMMIT;

金额应使用 numeric/decimal，或者用整数保存最小货币单位，不能使用 float。以下字段也应建立索引：

CREATE INDEX ix_enrollment_student
    ON enrollment (student_id);

CREATE INDEX ix_charge_enrollment_due
    ON charge (enrollment_id, due_on);

CREATE INDEX ix_payment_student_date
    ON payment (student_id, received_on);

CREATE INDEX ix_allocation_charge
    ON payment_allocation (charge_id);

这套设计不需要增加一个复杂的“万能账本”，重点是清楚区分应收、实收和冲抵关系。定期费用表现为多条独立应收，部分付款表现为部分分配，费用减免通过调整记录表达，学生退出则由报名状态和截止日期控制。大多数余额和逾期查询都可以建立在统一的 charge_balance 视图上。

备注：内容仅供参考。