学生课程与费用支付的 Database 设计

结论

不要把缴费信息直接放进 student 或 course 表。学生和课程是多对多关系，应该通过 enrollment（选课记录）关联。费用和付款也应对应具体的 enrollment。

如果系统规定“一笔付款只能支付一门课程”，可以让 payment 直接引用 enrollment_id。如果一笔付款可以同时支付多门课程，则应拆成两张表：

• payment：记录实际收到的每笔款项。
• payment_allocation：记录这笔款项分别分配到了哪些选课记录。

第二种结构更灵活，能处理部分付款、合并付款、退款和财务对账。

推荐的数据关系

student
   1
   |
   N
enrollment
   N
   |
   1
course_offering
   N
   |
   1
course

payment
   1
   |
   N
payment_allocation
   N
   |
   1
enrollment

建议增加 course_offering，表示“某门课程在某个学期的一次开课”。同一门课程可能在不同学期重复开设，每次开课的价格、教师和上课时间也可能不同。

表结构示例

以下 SQL 使用通用的关系数据库语法，具体约束可以根据 MySQL、PostgreSQL 或 SQL Server 调整。

CREATE TABLE student (
    student_id BIGINT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

CREATE TABLE course (
    course_id BIGINT PRIMARY KEY,
    course_code VARCHAR(30) NOT NULL UNIQUE,
    course_name VARCHAR(200) NOT NULL
);

CREATE TABLE course_offering (
    offering_id BIGINT PRIMARY KEY,
    course_id BIGINT NOT NULL,
    academic_term VARCHAR(30) NOT NULL,
    standard_fee DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    CHECK (standard_fee >= 0)
);

CREATE TABLE enrollment (
    enrollment_id BIGINT PRIMARY KEY,
    student_id BIGINT NOT NULL,
    offering_id BIGINT NOT NULL,
    enrolled_at TIMESTAMP NOT NULL,
    fee_amount DECIMAL(12, 2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (offering_id) REFERENCES course_offering(offering_id),
    UNIQUE (student_id, offering_id),
    CHECK (fee_amount >= 0)
);

course_offering.standard_fee 保存标准价格，enrollment.fee_amount 保存该学生实际应付的金额。这样既能保留开课时的标准费用，也能处理奖学金、折扣、重修费或个别价格调整。

付款相关表可以这样设计：

CREATE TABLE payment (
    payment_id BIGINT PRIMARY KEY,
    student_id BIGINT NOT NULL,
    paid_at TIMESTAMP NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    payment_method VARCHAR(30),
    reference_no VARCHAR(100),
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    CHECK (amount > 0)
);

CREATE TABLE payment_allocation (
    allocation_id BIGINT PRIMARY KEY,
    payment_id BIGINT NOT NULL,
    enrollment_id BIGINT NOT NULL,
    allocated_amount DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (payment_id) REFERENCES payment(payment_id),
    FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id),
    UNIQUE (payment_id, enrollment_id),
    CHECK (allocated_amount > 0)
);

例如，学生一次支付 1,500 元，其中 1,000 元用于课程 A，500 元用于课程 B：

• 在 payment 中写入一条金额为 1500.00 的记录。
• 在 payment_allocation 中写入两条记录，金额分别为 1000.00 和 500.00。

查询指定课程已经支付的金额

查询某名学生对某次开课已经支付的金额：

SELECT
    e.student_id,
    e.offering_id,
    e.fee_amount,
    COALESCE(SUM(
        CASE
            WHEN p.status = 'COMPLETED'
            THEN pa.allocated_amount
            ELSE 0
        END
    ), 0) AS paid_amount,
    e.fee_amount - COALESCE(SUM(
        CASE
            WHEN p.status = 'COMPLETED'
            THEN pa.allocated_amount
            ELSE 0
        END
    ), 0) AS outstanding_amount
FROM enrollment AS e
LEFT JOIN payment_allocation AS pa
    ON pa.enrollment_id = e.enrollment_id
LEFT JOIN payment AS p
    ON p.payment_id = pa.payment_id
WHERE e.student_id = :student_id
  AND e.offering_id = :offering_id
GROUP BY
    e.student_id,
    e.offering_id,
    e.fee_amount;

LEFT JOIN 会保留尚未付款的选课记录，COALESCE 则把空值转换为 0。查询只把状态为 COMPLETED 的付款计入实付金额。

如果要生成该学生全部课程的缴费报告，可以使用：

SELECT
    c.course_code,
    c.course_name,
    co.academic_term,
    e.fee_amount,
    COALESCE(SUM(
        CASE
            WHEN p.status = 'COMPLETED'
            THEN pa.allocated_amount
            ELSE 0
        END
    ), 0) AS paid_amount,
    e.fee_amount - COALESCE(SUM(
        CASE
            WHEN p.status = 'COMPLETED'
            THEN pa.allocated_amount
            ELSE 0
        END
    ), 0) AS outstanding_amount
FROM enrollment AS e
JOIN course_offering AS co
    ON co.offering_id = e.offering_id
JOIN course AS c
    ON c.course_id = co.course_id
LEFT JOIN payment_allocation AS pa
    ON pa.enrollment_id = e.enrollment_id
LEFT JOIN payment AS p
    ON p.payment_id = pa.payment_id
WHERE e.student_id = :student_id
GROUP BY
    c.course_code,
    c.course_name,
    co.academic_term,
    e.fee_amount
ORDER BY
    co.academic_term,
    c.course_code;

如果业务规则非常简单

如果系统能保证每笔付款只用于一门课程，可以省略 payment_allocation，直接在 payment 中保存 enrollment_id：

CREATE TABLE payment (
    payment_id BIGINT PRIMARY KEY,
    enrollment_id BIGINT NOT NULL,
    paid_at TIMESTAMP NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    payment_method VARCHAR(30),
    reference_no VARCHAR(100),
    status VARCHAR(20) NOT NULL,
    FOREIGN KEY (enrollment_id) REFERENCES enrollment(enrollment_id),
    CHECK (amount > 0)
);

这种结构允许一门课程分多次付款，但无法自然表达“一笔收款同时支付多门课程”。如果以后可能涉及财务对账、批量付款或统一收据，仍建议使用 payment 和 payment_allocation 两张表。

设计时需要注意的问题

不要只关联 student_id 和 course_id

缴费记录应关联 enrollment_id，而不是分别保存 student_id 和 course_id。否则，数据库很难保证该学生确实选择了这门课程，也无法区分同一课程在不同学期产生的选课和付款。

不要用浮点数保存金额

金额应使用 DECIMAL 或数据库提供的精确定点类型，不要使用 FLOAT 或 DOUBLE，否则汇总时可能出现精度误差。

不要把已付款总额作为普通字段反复更新

paid_amount 通常应由付款分配记录汇总得出。如果在 enrollment 中直接保存并持续修改已付款总额，遇到付款撤销、退款或并发操作时，很容易出现数据不一致。

如果因为性能需要缓存汇总金额，应把它明确视为派生数据，并通过事务、触发器或可靠的应用逻辑维护。

必须校验付款分配

数据库约束或业务逻辑至少要保证：

• 一笔付款的分配总额不能超过 payment.amount。
• 分配目标必须属于付款记录中的同一名学生。
• 已取消或失败的付款不能计入实付金额。
• 是否允许超额付款，需要有明确的业务规则。
• 付款记录和分配记录必须在同一个数据库事务中写入。

“分配总额不能超过付款金额”涉及跨多行汇总，通常无法只靠简单的 CHECK 约束实现，需要通过事务中的应用逻辑、存储过程或触发器校验。

退款不要直接删除历史付款

财务记录通常需要保留审计轨迹。可以单独建立 refund 和 refund_allocation 表，也可以使用明确的反向交易模型。不要直接修改原付款金额或删除原始付款记录，否则之后很难解释报表金额为什么发生变化。

目前没有实际数据库设计图，无法逐项检查现有字段和外键。如果当前设计已经通过独立的 enrollment 表关联学生和开课记录，整体方向就是正确的。费用和付款继续围绕 enrollment_id 建模，就能按学生、课程和学期稳定统计应付、实付及欠费金额。

备注：内容仅供参考。