To Relationship Or Not, That Is The Question!

[Jennifer]

Bare with me. I'm an excel user who is trying to learn
this wonderful program. Wish me LUCK!

If I am interested in tracking production numbers (Gross,
Net, days worked, goal, etc.) for a dental office would I
make one table that had numbers for the total office
production, doctors production and hygienist production or
should I seperate out the three into 3 tables? I'm not
real confident in the relationships just yet! Thank you
for any help.

Jennifer

 

[Albert D. Kallal]

I going to say that you do want to start reading up on data table designs.

It is possible that you only need one table here.

You could certainly have something like:

WorkDate StaffMember WorkHours etc.


Perhaps, you should however built a table of staff, and then you can
designate what kind of staff member you have, like:
Hygienist
doctor
etc.

The reason why we use more then one table is to actually "model" the problem
at hand. For example, if we have a table called Staffmembers, then we can
add new types of staff over time. Perhaps a new staff type might be part
time student. With a proper table design, we can over time add new types of
staff, and NOT have to create a new table, or even create a new field when
you need a new type of staff. That is why we use multiple tables. When done
right, your table designs will NOT have to change.

Here is some reading:
ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613

 

[John Vinson]

Bare with me. I'm an excel user who is trying to learn
this wonderful program. Wish me LUCK!

If I am interested in tracking production numbers (Gross,
Net, days worked, goal, etc.) for a dental office would I
make one table that had numbers for the total office
production, doctors production and hygienist production or
should I seperate out the three into 3 tables? I'm not
real confident in the relationships just yet! Thank you
for any help.

Jennifer

You probably shouldn't be storing total office production in ANY table
at all, if it can be calculated by summing doctors production and
hygenist production. In general, your Tables should store real-life
things, persons or events - anything that can be calculated (e.g.
Gross or Net or Days Worked) should be calculated based on the data.

Without knowing more about the problem you're trying to address I
can't be completely sure, but I'd suggest one table for Staff and one
for Production:

Staff
StaffID
LastName
FirstName
Position <e.g. Hygenist, Doctor>
<perhaps Goals here, if each staff member has a goal>

Production
StaffID <link to Staff, who did it>
DateCovered <e.g. if you're entering monthly numbers, the first day
of the covered month>
Production

If "gross" and "net" can be calculated, do so; if not, you can add
fields for them.

A more "fine-grained" structure would have each patient contact (I
presume that's what you're tracking) stored in a Visits table with the
StaffID, date, and duration; you could then use totals queries to
count how many visits per month, total contact hours, etc.