Hi Experts,
I would like to normalize the attached table but starting to get confused when touching on some fields. Can you please have a look at the attached tables and see where i can improve it. Also i would like to have discounts for students who are siblings but not sure how i should arrange it into the tables.
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
You have three tables (tblStudent and tblGuardian and tblEmployee) which are almost identical (with a couple of fairly small differences). I would create a single table (e.g tblPerson), to hold the common data and then you could have tblStudent to hold the student spefic data,tblGuardian to hold the Guardian specific data,and tblEmployee to hold the Employee specific data. This would the require some re-thinking as to how you would differentiate between students, guardians and employees. You might even create a Family table, to facilitate applying the multi-student discount. Do you allow for students who are children of employees?
AW
Arthur_Wood
also, you have 'repeating' fields in tblStudent, and tblEmployee (for Phone numbers) which really should be held in a separate table (with a field to indicate the Phone_Number_Type - Home, Business or Mobile).
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
In very broad erms, could you describe the nature of the problem that you are trying to address. What is the nature of the business that this application is intended to model?
A set of tables is not enough for us to be able to give you any realistic assistance. The same tables might serve to model several different business situations, which might involve a different set of 'normalized' tables.
AW
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ricky Nguyen
ASKER
Hi Experts,
Thank you all for the many responses. To answer some questions mentioned from above, I run a small music coaching school and currently have 4 employees. We have students at many levels and do allow for students whom are children of employees.
We anticipate for the school to double in size by the end of this year and would like to factor in this expansion as part of our plan. At the moment, our business structure is very basic with employees taking on multiple rolls but will anticipate for segregation to occur shortly.
As part of our marketing campaign we will continue to give discounts to siblings, 10% for second child, 15% for third etc... but may want to include other factors for future discounts.
I had broken created tblPerson as AW had mentioned. When I tried looking at the EmployeeInfo table, suggested by, 8080_Diver, would JobID and EmployeeInfoID represent the same thing?
Also, when look at the tblInvoice, how do i pull the students info into it? At present i believe the table will only allow me one studentID per invoiceID, but what if i wanted to invoice two students in the one invoice?
Also AW, how should the family table look? Wouldn't it just repeat the students table?
Many thanks in advance.
Regards
Ric
Ricky Nguyen
ASKER
Hi Experts,
I've attached a revised edition of the tables.
AW