Table Design Help (1 Viewer)

stonegold87

Member
Local time
Today, 16:31
Joined
Dec 10, 2020
Messages
37
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?
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:31
Joined
Feb 19, 2002
Messages
42,981
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

Member
Local time
Today, 16:31
Joined
Dec 10, 2020
Messages
37
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

Super Moderator
Staff member
Local time
Today, 07:31
Joined
Feb 19, 2002
Messages
42,981
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

Member
Local time
Today, 16:31
Joined
Dec 10, 2020
Messages
37
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
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:31
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom