I have to create a system design for a cashier system. Most of the transactions would be payments for fees in a student account.
Fees like Tuition, Books, Enrollment Fees. The problem with these fees are their special characteristics like Tuition fees being staggered in MONTHLY itemization (if that's a correct term), Books can also be staggered but payable throughout a year without itemization, and Enrollment Fees which consist of the Reg Fee and the Misc Fee (which is broken down into a bunch of items) both of these Enrollment Fees required for payed during enrollment. Other fees would be just simple fees, and some other special ones are to be payed by students who've succeeded to a certain year level like Prom Fees for 3rd & 4th year students.
The thing is they need to formatted and outputted to a Student Ledger and of course an Official Receipt.

This "subsystem" is the last part of an amateur School Management System I have to design as the "System Analyst" for the team. Other subsystems are Library, Registrar, Admission.
Yes, I am a student and this is our Finals requirement.
Accounting system design I found is in such a whole new level.
Fortunately, our scope for this one is according to our adviser "easy and simple", using terms like "only debit and credit". Specifically, our scope doesn't include more complicated accounting. Only to a couple of reporting--Student Ledger and Official Receipt generation--and handling student-to-school transaction processes. But despite this "simplicity" I am a noob after all.

There's a bit more explanation but I hate to repel people would could potentially help me with a much longer wall of text than I have now.

Currently I'm stuck trying to create an ERD for this system. I'm considering flexibility of the fees, and formatting the output in the ledger and OR.

Forgive this noobishness, everyone.
I've tried my best with a 2 month deadline and there's only 1 week left, so I really need help. These accounting stuff is all that's in the way of me passing this subject.

Thank you very much.

Edited by onixbwcrap: formatting

4 Years
Discussion Span
Last Post by adam_k

Usually you debit the cash account and credit the proper account when you receive money.
Since you are not really building an accounting system I suggest you debit your cashier (keep track of the money you've collected) and credit the student.
You can keep track of what the student has paid by a reason (code) or a transaction type.
If you need to check if a particular item has been paid, you sum based on that reason or transaction type.
Additional info (like month for tuition) can come along in separate fields, but it's not your primary focus.

You might want to also build payment transactions, for when you pay money and you need to credit your cashier.


Thanks! That really helps, confirms things.

I'm a little confused now with how to credit someone without an account, for example entrance fees which are credited to an external entity.
Should I create a flexibility with the TRANSACTION table, where there would be a Student ID foreign key in NOT NULL, and a Received From attribute also NOT NULL, so either would be the creditor?


Why not create an account for them? it can be something as simple as stu00001 with 00001 being their ID or pass number or whatever you see fit.
External entities can have their own "account" format.

if you set them both as not null, then you need to fill both. Instead use a constraint to verify that you get either one, but I doubt you need it. Like you said yourself, keep it simple. You are not building the accounting super-system, so just cover your needs.


Hey, thanks. That helps.
Oh man, about that NOT NULL thing. I meant ALLOW NULL for both. Sorry, but I understood your point. By constraint, did you mean in the coding?
Oh, and about creating an account for them.. I do have accounts for Students, my problem would be for those payments from external entities that are too common to have accounts, like Entrance Exam Fees. Somewhat like those payments in convenience stores, you don't necessarily credit a specific account for that. What do you think?

These cashier processes are actually part of the last subsystem (Student Transactions) we are given to create.

Let me share the draft I have so far, sorry about this:

Student Account
Student ID (FK)
Enrollee ID (FK)

Student Account Fees
Fee ID (FK)

Charge Amount

Fees Template
Fee ID (FK)

OR Number
Mode of Payment

Transaction ID (FK)
Fee ID (FK)

Edited by onixbwcrap


In my company, we've got customer accounts and for the 1-time sales to end-customers or something similar that we wouldn't create a customer account we use a blanket customer - "Various customers" as an account. I suggest you take this approach as well.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.