I am working on a School Management software which includes module for billing of students i.e, fee collection from students.
The fee is collected on monthly basis (12 times a year) and the total monthly fee is combination of various charges, fixed as per class and month. Some of the charges include Tuition Fee, Bus Fee, Printing Charge or others. A fixed amount of late fee is also charged on per month basis starting from the date, which can vary from month to month. Bus fee is charged as per the bus category of a particular student. There is a provision for partial payments as well.
My current approach is like this:
A master table to store fee settings, which include month & class and the fee settings.
feeMaster fid -> Primary key month_year -> Stores Month Year stu_class -> Class of Student tuition_fee -> Tuition fee for that class tuition_fee_percent -> Percentage of Tuition fee to take, defaults to 100% bus_fee_percent -> Percentage of bus fee late_fee_start -> Day of month from which to charge late fee late_fee -> fixed late fee on per month basis printing_charge -> Printing charge if any other_fee -> Other fee if any other_fee_reference -> Other fee reference
Every time a student comes to pay his/her fee, calculation is made and a transaction takes place in the system. The details of the transaction are stored in two tables.
Transaction master table is used to store the transactions
transMaster tid -> Primary key purpose -> purpose of transaction, monthly fee amount -> amount of transaction type -> transaction mode / cash / cheque / dd created -> date
The details of this transaction are stored in another table as
studentFeeDetails sfid -> unique id tid -> transaction id from transMaster table fid -> fee id from fee settings table feeMaster tuition_fee -> calculated tuition_fee bus_fee -> calculated bus_fee printing_charge -> calculated printing_charge other_fee -> calculated other_fee late_fee -> calculated late_fee total_fee -> total fee calculated discount -> discount if given any amount_payable -> net amount payable amount_paid -> paid amount balance -> balance - if paid amount is greater or lesser than the original one, it is stored here status -> status - true if partial fee else false created -> date of creation
This is the current architecture of the module. There is no accounting practices involved and hence creates lots of problems for our accounting deptt.
To report the total payable fee for a month, every time, the system runs calculation algorithm on all the students and comes up with figure. To find the pending fee for a class, the system again first checks the receivable fee for that class and removes the entries found in studentFeeDetails table to generate the pending report. There is no proper segregation of fee heads under this system. Now the current system needs to be converted into proper accounting system which can track the advances and balances.
I am thinking of a system where every month a posting process debits every student's account with the fee due for that month and on every late fee start date another process debits late fee to a student's account, if the fee is still pending.
This approach can keep a check on receivables, pending and received fee.
Please help, if the approach is correct and how to go with it. I am stuck with the db schema part and its implementation.