CODEWITHSUNDEEP
database-designrelational-databaserdbmsdatabase-schema
Bishnu Paudel
Bishnu Paudel

Reputation: 2079

Database design for school fee processing

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?

Upvotes: 2

Views: 13828

Answers (3)

Muhammad Usman
Muhammad Usman

Reputation: 1

If you need detailed tracking, such as when the payment was made, how much was paid, and to keep a record of multiple payments, then creating a separate payment table would be a more effective and scalable solution.

And Separate table field like be: payment_id student_id(FK) amount_paid payment_date status etc..

Upvotes: 0

Aqeel Haider
Aqeel Haider

Reputation: 633

I think you should also add column of Due Date of Fee in fee_student.

If the School have 10 thousand student how you will add paid information for all the students????

Upvotes: 0

Joel Brown
Joel Brown

Reputation: 14388

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.

Upvotes: 1

Related Questions