Tables Structure Help

[ZEEq]

Hello Everyone
I need your help and opinion on my current database design , i m having some problem dealing with discounts that are applied during the year
here is my current relationships report

 

[jdraw]

The picture of tables and relationships is really a reflection of your business rules. For readers to comment on the graphic, they need a description of the business and the associated rules.

 

[ZEEq]

The picture of tables and relationships is really a reflection of your business rules. For readers to comment on the graphic, they need a description of the business and the associated rules.

thanks @jdraw for your reply
Every Year thr are two types of Discounts
1. Standard Discount ( Includes Sibling Discounts , Scholarship's etc )
2.Additional Discount ( Depends on Students Parents Financial situation or in some special circumstances)

 

[plog]

SessionYearT seems unnecessary. Just store the SessionYear value in SessionYearTypeT not Session_ID.

You also have a field called AcademicYear--does this really differ from SessionYear? You deliniate years in 2 different ways?

MonthID in StudentFeesDueT is unnecessary as well. Don't store a number that relates to another number--just store that other number. I am certain whatever table MonthID comes from is unnecessary the same way SessionYearT is.

IsCurrent in SessionYearTypeT seems unnecessary to. You have a StartDate and EndDate so you know if a SessionYear is current. You don't store values you can calculate with other data.

Your explanation about discounts make it seem they are dependent on the individual students. However none of your Discount tables link to a Student table. Seems your discount tables are at the wrong level--they should be linked to some Student table.

The same about discounts can be said about fees/dues. None of the fees/dues tables go to a specific student but to a StudentClassT.

 

[ZEEq]

SessionYearT seems unnecessary. Just store the SessionYear value in SessionYearTypeT not Session_ID.

You also have a field called AcademicYear--does this really differ from SessionYear? You deliniate years in 2 different ways?

MonthID in StudentFeesDueT is unnecessary as well. Don't store a number that relates to another number--just store that other number. I am certain whatever table MonthID comes from is unnecessary the same way SessionYearT is.

IsCurrent in SessionYearTypeT seems unnecessary to. You have a StartDate and EndDate so you know if a SessionYear is current. You don't store values you can calculate with other data.

Your explanation about discounts make it seem they are dependent on the individual students. However none of your Discount tables link to a Student table. Seems your discount tables are at the wrong level--they should be linked to some Student table.

The same about discounts can be said about fees/dues. None of the fees/dues tables go to a specific student but to a StudentClassT.

Thanks @plog for such detailed reply
Fees and discounts will change every year when student gets promoted to new class that's why i associated with class, academic year is actually sessionYear my bad i named it wrong

 

[Pat Hartman]

There is a m-m relationship between the discounts and the students to which they apply. Your schema defines the discounts for the session but does not connect them to specific students. That needs a junction table. StudentFeesT looks like it might be the junction table for SessionFeeStructureT but there is no relationship defined.

StudentFeesDueT does not need StudentID or AcademicYear. It also seems to point to SessionFeeStructureT but with no relationship defined. StudentAdditionalDiscount MIGHT be the junction table for SessionDiscountT but there is no relationship defined and the FK name is wrong if that's what it is.

Making two tables, one for session and one for additional discounts is just plain wrong. There should be ONE table for discounts and it should have a type code so if the student gets both discounts, there would be two rows for the student in the junction table.

It is so much easier to verify a relationship diagram when you use discipline in how you name the fields. Your naming is inconsistent, both in the actual names and in the use of the underscore. Personally, I dislike using the underscore. I finding annoying to type and jarring to look at but if you use it, do it consistently. It will plague you and anyone who has to modify the app later forever.

I also can't identify a table that defines the standard fees for the Session.

 

[ZEEq]

thanks @Pat Hartman for the suggestions
I corrected names lets just leave discount for the moment
1.SessionFeestructureT ( includes all the Fees for the session e.g Enrollment Fee, Transportation , Tuition Etc)
2.StudentFeeT ( Includes Fees applicable to that particular student)
and what changes do i need to make in studentClassFeeT ?

 

[Pat Hartman]

Remove EntryDate. You do NOT copy any data fields from other tables. All you need is the FK to the related table.

 

[ZEEq]

i removed that field please tell me any changes required in StudentFeeDueT
should i include Discount in StudentFeeDueT?

 

[Pat Hartman]

Certain design flaws are obvious. Some parts of a design are generic. Duplicating data is an obvious one if you have used the same names or even names that suggest the same value. Making up your business rules is beyond what we can do. If there were always standard solutions, you would always just start with a template. You are creating custom software and using a custom schema.

I'm guessing that the rows in StudentFeeDueT are added weekly, monthly, or annually depending on what the billing cycle is for that particular fee. As long as the feeAmt is standard, I don't see anything obvious that is missing.