Avatar of Ricky Nguyen
Ricky Nguyen
Flag for Australia asked on

Access 2007 - Help to Normalize Table

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.

Thanks in advance.
Ric
FMEC-Tables.xlsx
Microsoft AccessDatabases

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Luke Chung

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
Arthur_Wood

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).
 
AW
SOLUTION
8080_Diver

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
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.
ask a question
Arthur_Wood

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.

Thanks
Ric
FMEC-Tables.xlsx
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.