Billing Module (Fee) for School Management System

Question

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.

Answer 1

In addition to seconding Joel Brown's comment I would like to suggest a couple things to help.

• If this is for internal use only start with open source accounting and ERP db's. For example, LedgerSMB or Adempiere. Also do this if you are releasing open source. If you are not releasing open source, consider interoperating with such software for billing. I would be happy to help (conflict of interest alert).

• Look specifically at batch processing and review requirements. This allows human review as to how much money is owed before the money hits the books.

One thing we are doing in LedgerSMB is to have the possibility of "template transactions" which can then be copied out and filled in (possibly by automatic processes).

Accounting is a large field but if you are handling billing you really need to know it.

Along these lines, I don't really see anything wrong with what you have posted but it is quite incomplete. You need line items for the invoices, and you need line items for the payments. In this way it becomes a lot simpler to track how much is owed. You may also want to separate out students from customers in case one parent has multiple children enrolled in the school.

Answer 2

Check out the design/schemas of some of the open source subscription e-commerce plugins out there such as Drupal's Ubercart uc_recurring.

Personally, I'd recommend Wordpress + WooCommerce + WooCommerce Subscriptions, which is not free ($100ish). It is well made, and can also talk to Quickbooks.