Database design for school fee processing

Question

I am designing a DBMS for a school. After designing courses and exams tables, I have come to the fees module tables:

fee_type
    fee_type_id PRIMARY KEY
    fee_type    TYPE OF FEE (MONTHLY, WEEKLY,ANNUAL,ONE TIME)

fees
    fees_id PRIMARY KEY
    fee_heading (eg. TUITION FEE,LAB FEE, HOSTEL FEE,SPORTS FEE)
    amount      (CURRENT CHARGE OF THE FEE, could change with time)   
    class_id    (GRADE ID, GARDE 4, GARDE 5, GRADE 6)
    fee_type    TYPE OF FEE (MONTHLY, WEEKLY, ANNUAL, ONE TIME)
    archived    (FEE HEADING ARCHIVED FOR USE)

fee_student
    fee_id      (RELATED fee_id (FK))
    student_id  (RELATED student_id(FK))
    effective_from (DATE FROM WHEN THE FEE APPLIES TO THE STUDENT)
    amount      (CHARGE AT THE TIME OF FEE ASSIGNMENT (applicable to particular student))
    discount    (DISCOUNT HONORED TO STUDENT IF ANY)
    status      (ACTIVE OR INACTIVE)

transaction
    id PRIMARY KEY
    date        (date and time when transaction takes place)
    fee_id      (PAYMENT FOR)
    student_id  ({TO BE} PAID BY)
    amount      (AMOUNT PAID/APPLIED)
    description
    cr          (yes or no)
    dr          (yes or no)
    remarks

transaction will store all the payments by a student as well as the amounts charged for that student.

I am thinking of storing amounts charged to students in transaction according to fee_type: if the fee is of type WEEKLY, one record per week will be automatically added to transaction and the amount is marked as debit or credit.

Suggestions?

Answer 1

Your design is on the right track. A couple of comments:

• fees.fee_type should probably be fees.fee_type_id - assuming you want to use a natural join nomenclature.

• Instead of transaction.cr and transaction.dr you should establish a convention for the sign of the amount and just have a single amount field which is interpreted as credit or debit based on which side of zero the amount is on. Your current design allows an amount to be both credit and debit (unless you have a constraint that forbids this).

• One thing your design won't accomodate is "unapplied cash". In your current design, a payment from a student must be for a specific fee_student. What if a student makes an advanced deposit, receives a scholarship, or simply writes one cheque for multiple fees (tuition, lab, sports)? In your current model, you don't track that single (or unapplied) payment. You should have a transaction table that accepts payments from students and then use an intersection table (your current transaction table) to apply payments to specific fees. This allows you to have unpaid balances and unapplied amounts - both of which are common in real-world accounts payable applications.