Table Design Help

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

 

[Pat Hartman]

My how the scope has expanded

I suppose you could add an optional field as a FK to the class table but why? What would be the point? You won't be joining the two tables. I'd have to think long and hard about this and would need to know more about the entire app before deciding on what would be best. It's one thing to have multiple fees but now you've attached some fees to other objects.

 

[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

 

[Pat Hartman]

Sorry @stonegold87 , you can't ask people to give you a proposed schema without providing a COMPLETE specification. YOU are the one who is interviewing the users, it is you who have to drag the details out of them. Document what you know so far and show us how you think it should be laid out. We're happy to jump in and offer opinions but remember we don't know the details. We only know what you tell us and what we have learned from life experience, i.e. having sent children to schools and paid those fees.

Just FYI, Each School year would have separate records for fees. In year 1, you might have registration and tuition but in year 2 you might have tuition, sports, and transportation. That's another reason for not putting the fees as columns in records but as rows.

I mentioned needing school year in the fee table earlier.

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

 

[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

 

[Pat Hartman]

As I said earlier, without a complete spec, I'm not sure how I can help you. I created a schema to get you started and I posted a working example of using man-many relationships. At some point, you'll need to jump in and give it a whirl.