StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POBilling module (Fee) for School Management Software
primarykey
Id
9338869
data
AcceptedAnswerId
0
AnswerCount
2
ClosedDate
2018-03-07T14:33:35.527
CommentCount
2
CommunityOwnedDate
CreationDate
2012-02-18T05:30:54.870
FavoriteCount
0
LastActivityDate
2018-03-07T12:09:00.580
LastEditDate
LastEditorUserId
0
OwnerUserId
1216285
ParentId
0
PostTypeId
1
Score
-1
ViewCount
5972
LastEditorDisplayName
text
Body
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. <pre><code>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 </code></pre> 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 <pre><code>transMaster tid -> Primary key purpose -> purpose of transaction, monthly fee amount -> amount of transaction type -> transaction mode / cash / cheque / dd created -> date </code></pre> The details of this transaction are stored in another table as <pre><code>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 </code></pre> This is the current architecture of the module. There is no accounting practices involved and hence creates lots of problems for our accounting deptt. <ul> <li>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.</li> <li>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 <code>studentFeeDetails</code> table to generate the pending report.</li> <li>There is no proper segregation of fee heads under this system.</li> </ul> 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.
Tags
<database-design><architecture><database-schema><billing><accounting>
Title
Billing module (Fee) for School Management Software
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USEhs4n
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. COComplex topic and I feel this is far too broad for SO. Neverthess, one thing seems obvious to me, I'd normalize the `feeMaster` table. Your column `otherFee` already indicates this, I'd create a table like `line_item` referring to a certain `fee_type`.
 singulars
 PostPostId
 POBilling module (Fee) for School Management Software
 UserUserId
 UShome
2. COI have once worked with this type of problem. I am willing to share the database with you... again.
 singulars
 PostPostId
 POBilling module (Fee) for School Management Software
 UserUserId
 USRaouf Athar

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.