学校费用与 Payment/Transaction 数据库设计建议

结论

建议把“应收计划”和“实际收款”分开建模，避免让 Payment 表同时承担费用汇总、分期计划和付款记录三种职责。

可以按下面的方式拆分：

• student_fee：记录向学生收取的各项费用。
• payment_plan：记录选定的付款方案、总应收金额和首付款。
• installment：记录每一期的到期日和应付金额。
• payment_transaction：记录每一笔实际发生的收款、退款或冲正。
• payment_allocation：记录一笔付款被分配到了哪些分期。

不建议只在某一期记录上增加 is_paid 字段。实际业务中可能出现少付、多付、一次支付多期、一期分多次支付、退款和交易失败等情况，单个布尔值无法准确表示这些状态。

先修正分期计算公式

如果首付款只支付一次，应先从总应收金额中扣除首付款，再把剩余金额分摊到后续各期：

remainingAmount = totalAssignedFees - downPayment
installmentAmount = remainingAmount / remainingInstallmentCount

例如，总费用为 12,000.00，首付款为 1,000.00，之后再分 11 期：

remainingAmount = 12,000.00 - 1,000.00
                 = 11,000.00

installmentAmount = 11,000.00 / 11
                  = 1,000.00

原公式：

(sumOfAllAssignedFees / paymentterm) - downpayment

会在计算每一期金额时都减去首付款，因此含义不正确。

还需要先确定“12 months”的具体含义：

• 首付款加 11 期，共 12 条计划；
• 首付款之外再支付 12 期，共 13 条计划；
• 首付款本身就是第一期。

数据库不应根据 monthly 直接认定一定要生成 12 条记录。分期数量、首次到期日和付款频率都应作为付款方案的明确配置。

推荐的数据关系

业务关系可以整理为：

student
  └── student_fee
        └── payment_plan
              └── installment
                    └── payment_allocation
                          └── payment_transaction

其中，installment 表示“应该付多少钱”，payment_transaction 表示“实际收到了多少钱”，二者通过 payment_allocation 建立对应关系。

这套设计可以处理以下情况：

• 学生一次支付两期；
• 一期分两次付清；
• 实际付款少于应付金额；
• 学生多付并形成余额；
• 交易失败或被撤销；
• 已支付款项发生部分退款。

一个可落地的表结构示例

下面的示例采用接近 MySQL 8 的语法，字段类型和约束可按实际使用的数据库调整。

学生费用明细

除了费用合计，还应保存每项费用的来源，否则以后很难解释总金额是如何算出来的。

CREATE TABLE student_fee (
    student_fee_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    school_year_id BIGINT NOT NULL,
    fee_type_id BIGINT NOT NULL,
    assigned_amount DECIMAL(12, 2) NOT NULL,
    discount_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CHECK (assigned_amount >= 0),
    CHECK (discount_amount >= 0),
    CHECK (discount_amount <= assigned_amount)
);

每项费用的净额可以这样计算：

netFee = assignedAmount - discountAmount

如果费用标准以后可能调整，student_fee 应保存分配费用时的金额快照，而不是在每次查询时读取当前的费用标准。否则收费标准一旦修改，历史账单也可能随之变化。

付款方案

CREATE TABLE payment_plan (
    payment_plan_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    school_year_id BIGINT NOT NULL,
    plan_type VARCHAR(20) NOT NULL,
    total_amount DECIMAL(12, 2) NOT NULL,
    down_payment_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    currency_code CHAR(3) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CHECK (total_amount >= 0),
    CHECK (down_payment_amount >= 0),
    CHECK (down_payment_amount <= total_amount),
    CHECK (plan_type IN ('MONTHLY', 'QUARTERLY', 'ANNUAL', 'ONE_TIME')),
    CHECK (status IN ('DRAFT', 'ACTIVE', 'COMPLETED', 'CANCELLED'))
);

cash 不适合作为付款周期。它通常表示付款方式，与 monthly、quarterly、annual 不在同一个维度。

两者应分开表达：

plan_type: MONTHLY, QUARTERLY, ANNUAL, ONE_TIME
payment_method: CASH, CARD, BANK_TRANSFER, CHECK

分期计划

CREATE TABLE installment (
    installment_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    payment_plan_id BIGINT NOT NULL,
    installment_number INT NOT NULL,
    due_date DATE NOT NULL,
    amount_due DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_installment_plan
        FOREIGN KEY (payment_plan_id)
        REFERENCES payment_plan(payment_plan_id),

    CONSTRAINT uq_installment_number
        UNIQUE (payment_plan_id, installment_number),

    CHECK (installment_number > 0),
    CHECK (amount_due >= 0)
);

如果首付款本身也是一笔明确的应收款，可以将它作为第 1 期插入：

INSERT INTO installment (
    payment_plan_id,
    installment_number,
    due_date,
    amount_due
) VALUES (
    1001,
    1,
    '2026-06-01',
    1000.00
);

剩余 11 期使用编号 2 到 12。这样，首付款和普通分期可以共用一套支付与核销逻辑。

如果首付款在业务上有独立含义，也可以增加类型字段：

ALTER TABLE installment
ADD COLUMN installment_type VARCHAR(20) NOT NULL DEFAULT 'REGULAR',
ADD CONSTRAINT chk_installment_type
    CHECK (installment_type IN ('DOWN_PAYMENT', 'REGULAR'));

实际付款交易

CREATE TABLE payment_transaction (
    transaction_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    student_id BIGINT NOT NULL,
    transaction_type VARCHAR(20) NOT NULL DEFAULT 'PAYMENT',
    transaction_status VARCHAR(20) NOT NULL,
    payment_method VARCHAR(30) NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    external_reference VARCHAR(100),
    received_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CHECK (amount > 0),
    CHECK (transaction_type IN ('PAYMENT', 'REFUND', 'REVERSAL')),
    CHECK (
        transaction_status IN (
            'PENDING',
            'SUCCEEDED',
            'FAILED',
            'CANCELLED'
        )
    ),
    CHECK (
        payment_method IN (
            'CASH',
            'CARD',
            'BANK_TRANSFER',
            'CHECK',
            'OTHER'
        )
    )
);

只有状态为 SUCCEEDED 的交易才能计入已支付金额。失败交易也应保留，不能直接删除，因为对账和问题追踪可能还会用到这些记录。

付款分配记录

CREATE TABLE payment_allocation (
    allocation_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL,
    installment_id BIGINT NOT NULL,
    allocated_amount DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_allocation_transaction
        FOREIGN KEY (transaction_id)
        REFERENCES payment_transaction(transaction_id),

    CONSTRAINT fk_allocation_installment
        FOREIGN KEY (installment_id)
        REFERENCES installment(installment_id),

    CONSTRAINT uq_transaction_installment
        UNIQUE (transaction_id, installment_id),

    CHECK (allocated_amount > 0)
);

假设某一期应付 1,000.00，学生先支付 400.00，之后又支付 600.00，系统会产生两条交易和两条分配记录，不需要修改或覆盖原来的付款记录。

如何判断某一期是否已支付

某一期的已支付金额，应对成功交易的分配金额求和：

SELECT
    i.installment_id,
    i.installment_number,
    i.due_date,
    i.amount_due,
    COALESCE(
        SUM(
            CASE
                WHEN pt.transaction_status = 'SUCCEEDED'
                THEN pa.allocated_amount
                ELSE 0
            END
        ),
        0
    ) AS amount_paid
FROM installment AS i
LEFT JOIN payment_allocation AS pa
    ON pa.installment_id = i.installment_id
LEFT JOIN payment_transaction AS pt
    ON pt.transaction_id = pa.transaction_id
WHERE i.installment_id = ?
GROUP BY
    i.installment_id,
    i.installment_number,
    i.due_date,
    i.amount_due;

付款状态可以根据应付金额、实付金额和到期日计算：

SELECT
    x.*,
    CASE
        WHEN x.amount_paid >= x.amount_due THEN 'PAID'
        WHEN x.amount_paid > 0 AND CURRENT_DATE > x.due_date
            THEN 'PARTIALLY_PAID_OVERDUE'
        WHEN x.amount_paid > 0 THEN 'PARTIALLY_PAID'
        WHEN CURRENT_DATE > x.due_date THEN 'OVERDUE'
        ELSE 'UNPAID'
    END AS payment_status
FROM (
    SELECT
        i.installment_id,
        i.due_date,
        i.amount_due,
        COALESCE(
            SUM(
                CASE
                    WHEN pt.transaction_status = 'SUCCEEDED'
                    THEN pa.allocated_amount
                    ELSE 0
                END
            ),
            0
        ) AS amount_paid
    FROM installment AS i
    LEFT JOIN payment_allocation AS pa
        ON pa.installment_id = i.installment_id
    LEFT JOIN payment_transaction AS pt
        ON pt.transaction_id = pa.transaction_id
    GROUP BY
        i.installment_id,
        i.due_date,
        i.amount_due
) AS x;

简单系统也可以在 installment 中保存 status，但这个字段属于可推导数据。如果选择保存，就必须在每次付款、退款和冲正时同步更新，否则状态很容易与交易明细不一致。

一笔付款如何支付多期

假设学生支付 2,500.00，系统按照到期日从早到晚依次核销：

• 第一期分配 1,000.00；
• 第二期分配 1,000.00；
• 第三期分配 500.00。

对应的交易数据可以写成：

INSERT INTO payment_transaction (
    student_id,
    transaction_status,
    payment_method,
    amount,
    currency_code,
    external_reference,
    received_at
) VALUES (
    2001,
    'SUCCEEDED',
    'BANK_TRANSFER',
    2500.00,
    'CNY',
    'BANK-20260901-0001',
    CURRENT_TIMESTAMP
);

取得新交易的 transaction_id 后，再写入分配记录：

INSERT INTO payment_allocation (
    transaction_id,
    installment_id,
    allocated_amount
) VALUES
    (5001, 3001, 1000.00),
    (5001, 3002, 1000.00),
    (5001, 3003, 500.00);

这些写入应在同一个数据库事务中完成，以免交易已经创建，分配记录却只写入了一部分。

金额取整不能忽略

货币金额应使用 DECIMAL，不要使用 FLOAT 或 DOUBLE。

剩余金额不能被期数整除时，需要提前确定尾差处理规则。例如：

remainingAmount = 10,000.00
installmentCount = 3

不能直接生成三个 3,333.33，因为三期合计只有 9,999.99。一种常见的处理方式是：

第 1 期：3,333.33
第 2 期：3,333.33
第 3 期：3,333.34

创建全部分期后，应校验：

sum(installment.amount_due) = payment_plan.total_amount

如果首付款也记录在 installment 中，这个合计已经包含首付款，不要再次相加。

费用变更也需要留下记录

学年中可能新增费用、减免费用或取消收费。已经确认的历史金额不应直接修改而不留下记录。

至少要明确以下规则：

• 付款方案确认后，原费用能否修改；
• 新增费用应加入现有方案，还是生成新的应收项目；
• 减免和退款应修改原记录，还是新增负向调整记录；
• 已核销的分期能否重新计算；
• 付款方案取消后，已有交易如何处理。

如果对账要求较高，可以增加 fee_adjustment、refund 或账务流水表，通过追加记录来表达变化，避免覆盖历史数据。

其他约束

实现时还应注意：

• 为所有外键建立索引。
• 金额字段应同时保存 currency_code，不要默认所有记录始终使用同一种货币。
• external_reference 可以保存银行流水号或支付平台订单号，并按业务要求增加唯一约束，防止重复入账。
• 不要删除成功的付款交易。错误应通过退款或冲正记录修正。
• 分配总额不能超过交易的可用金额。
• 是否允许某一期的有效分配总额超过应付金额，需要制定明确规则。
• 服务层应验证付款交易与分期属于同一学生，并且使用同一种币种。
• 创建交易、写入分配记录和更新必要的汇总字段时，应使用数据库事务并锁定相关记录，防止并发导致重复核销。

费用表保存收费依据，分期表保存应收计划，交易表保存真实的资金动作，分配表负责连接“收到的钱”和“应付的款”。按照这个边界设计后，“某一期是否已支付”不再依赖手工标记，而是可以根据实际付款记录计算。

备注：内容仅供参考。