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 ?

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

Best practice is to use ONLY letters (uppoer/lower case), numbers, and the underscore "_" when forming object names. Other characters will always be a problem once you start writing code. VBA does not support any characters except those above. Also, you need to be mindful of property names and functions and avoid them when naming objects. i.e. every object has a Name property so does Me.Name refer to the name of the object or your column named "Name"? Date, Year, Month, are function names and also can cause confusion in code. I use compound names like DueDate or I abbreviate to DueDT.

Your fee table should be similar to @G37Sam 's suggestion. table to associate the fee with a student.

tblFeeAccount
FeeID (autonumber, PK)
FrequencyCD (Month, Annual)
FeeDesc
FeeAmt

The next table associates a fee with a student. This may be overkill but it gives you flexibility When not all fees apply to all students. For example, some may provide their own transportation or walk. As you add a new student, add an entry for each fee he will be charged. Include a discount % or default to 0 for no discount. This table also gives you ONE place to put the discount rather than entering it for each invoice. If the discount applies to ALL fees, then remove it from this table and put it in the student table.

tblStudentFees
StudentFeeID (autonumber, PK)
studentID (FK)
FeeID (FK)
DiscountPct

This table contains the actual invoice and payment detail. You need to run an annual and a monthly process to generate invoices. If you have other frequencies, each will need its own process. You can kick these off manually to begin with and automate them later. The PaidDT is updated when the invoice is manually marked as paid.

tblFeeInvoice
FeedetailID (autonumber, PK)
DueDT
FeeAmt (copy from tblFeeAccount because Fee may change over time) - include discount amount
PaidDT

 

[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.

 

[Pat Hartman]

Plz explain Fee ID (FK) in tblStudentFees 1.what if thr are multiple Fees applicable?

Each fee will be in a separate row. FeeID is the FK to the field table so you know what this fee is for. The concept of a relational database is - once you have more than one of something (fees), you have many. Therefore, we don't put each fee in a separate column because that would mean that if we add a new fee, we would need to add a new column PLUS modify all related queries, forms, reports, and code. We put "many" in one row each That allows us to manage them more easily and to add new types without making ANY application or schema changes.

If you were trying to model a family, would you create a table with columns named mother, father, Mary, John, Paul. That is essentially what you are doing by using columns to hold fees although you would probably back up when it got to the children's names and use child1, child2, etc which is no better.

I modified the database to include the relationships and I changed a couple of names to be consistent. PK and FK names should match except when you need to use the FK more than once in the same table as you do in a self-referencing table.

Although it is not wrong, I would not use prefixes on column names. You will just find them annoying. They add three meaningless characters to every field name you have to type and when you are looking at a table/query in DS view all you will see is fld. in every column. Not useful.

 

[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)

 

[Pat Hartman]

The table tblStudentFees is what is commonly called a junction table. Junction tables are used to implement a many-many relationship - i.e. many students have many fees. In junction tables are two FK's. In this case, one pointing to Students and the other pointing to FeeAccount. Not all junction tables have intersection data but here we have DiscountPct. We then have a second junction table because some of our fees will have multiple invoices. So the annual fees will have one row in tblFeeAccount for the fee/student/Schoolyear combination but the monthly table will have one row for each school month.

That explanation just jogged me. We also need SchoolYear in tblStudentFees AND we need a compound index that includes StudentID, FeeID, AND SchoolYear.

I'm also not sure why FeeType is in a separate table. I think it belongs in FeeAccount unless there is something I don't understand.

I attached an update.

 

[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

 

[Pat Hartman]

The junction tables will be subforms and combos are used to pick the "other" FK. Here's a sample.

 

[stonegold87]

Thanks Pat this is really helpful

 

[Pat Hartman]

You're welcome. The example show two ways to view the many-side. On uses a subform, the other a pop up form.