Table Design Help

[stonegold87]

Dear Members
I have a question regarding table design i m trying to develop a simple Student Fee Management System
I have student table
Student ID
Name etc

Fee Detail table
Fee detail ID
student ID (FK)
Fee ID (FK)
Discount

Fee Account table
Fee ID
Tuition Fee (monthly)
Registeration Fee (one time)

Is this table design correct?
Or should i add tution Fee and Registeration Fee in Fee detail table?

My next goal will be to charge student Fee every month and receive Fee against that month plz guide me i am still learning access
Regards

 

[Ranman256]

Also in tFeeDetail,
issueDate
PayDate

Then every month a routine runs append query to add the monthly fee to all students.

 

[G37Sam]

How many different kind of fee's do you have? Do you want to allow your user to modify fee types in the future?

 

[stonegold87]

Thx for ur reply Ranman could u plz eleborate issue date and paid date a bit more and also about append routine

 

[stonegold87]

Thx g37sam yes there are three more Fee types and i want users to modify

 

[G37Sam]

Then it should look like this

Fee Account table
Fee ID
Fee Description
Fee Amount

 

[jdraw]

There are a number of generic data models at Barry Williams' site. Here is one re Students Fees and Banks that may be useful to you. You can pick the pieces that are relevant to your situation and add/remove as necessary.

I recommend you do not use names with embedded spaces.

 

[stonegold87]

Thx Jdraw i look at that diagram and thr is one confusion
Why does tblstudentFeeDue and tblstudentFeecollected both tables have
Student fee due date and
Student fee collected date fields ?

 

[stonegold87]

Due should be in one table and collected in the other but I don't like the model anyway. In fact, I've never seen a model on that site that I actually liked but I keep looking

Simplistically, I would use a single table for fees with due and paid date. If I wanted to allow partial payments, I would use a child table that allowed me to have multiple payments for each fee. Then we get to a more complex requirement which is a many-to-many situation where any payment might apply to one or more fees and might be full or partial.

You need to clearly define your requirements and we'll help you build a model to satisfy those. 1-m with one row for each fee and many payments for that fee is probably as much flexibility as you would need. If someone sent in a big check for several fees, the data entry person would be responsible for breaking it up and assigning it appropriately. Or perhaps you might get one check from a parent for multiple students. If that is likely, then you need the third table to hold payments and the child table would be used to apply some of all of a payment to a particular fee.

Thx Pat ur reply is really encouraging ok i try to explain
1. Payments will be made full for particular Fee month no partial payments etc
2. Types of Fee
a.Registeration Fee ( one time) / year
b.boarding Fee ( one time ) / year
c.transportation Fee (one time) / year
d.Tution Fee ( Every Month)
3. Plz guide me how do i charge these Fees to students should i have fields for these Fee types in FeeDetailtbl or should i use a foreign key related to these Fee types
Not sure about assign these Fees to students

 

[stonegold87]

Thx again Pat for such detailed response plz look at this test File i m trying to clear my concept
Plz explain Fee ID (FK) in tblStudentFees 1.what if thr are multiple Fees applicable?
2.should i create a child table for Additional Fees?
3.or should i create extra tables for Tuition Fee and Fine?
Basically how to handle multiple Fees in tblFeeAccount

 

[mike60smart]

You next need at apply Referential Integrity as shown in the attached Diagram.

 

[stonegold87]

Pat i m having confusion regarding multiple Fees i understood row and column Description i just want to clarify if i charge Registeration Fee and Tuition Fee both have their own Row so how do i join these two rows with tblStudentFees with single (FK)

 

[stonegold87]

Thank you so much Pat now things are much clearer
would u be kind enough to share any example or guide me on this test file to how to create query and Form to handle many to many relations

 

[stonegold87]

Thanks Pat this is really helpful

 

[stonegold87]

Ok understood everything up till now next question is what if every class has seperate Fee structure should i include class id in tblFeeAccount ? Also how to make relation with tblInvoice?

 

[stonegold87]

Thx Pat helpful as always
School has many Classes with different Fees structures what is the best approach to handle this situation
Suppose a student admitted in kindergarten
Has different
Registeration Fee
Tution Fee
When he gets promoted to next class fee structure vl change

 

[stonegold87]

Thx @Pat Hartman plz explain the idea of another junction table you suggested before for annual and monthly Fees in Post #16 in detail
What would be the next step which tables to join with junction table ?
TblFeeAccount with tbl Invoice? And which (Fk's) to include ?
Kindly explain this last bit of invoice

 

[stonegold87]

Waiting for your response @Pat Hartman