Table Structure help needed (1 Viewer)

Hello everyone!


My experience with Access has been a learn-as-I-go approach so my learning has been all over the place for the most part. I currently have a project I am working on that I am struggling with the table design. I have looked at a few different approaches to structuring these tables to the point I have now confused myself and am now second guessing my design. I was hoping for some assistance with this as I’m really not sure anymore whether I’m going about this correctly or not.


The idea of the database is to track employee metrics. The metrics of each employee can fall in to one of 5 categories:
Claim Quality, Call Quality, Attendance, Discussion, and CAP


Each metric category has its own table. None of the metric categories have the same type of information. An employee can have any number of metrics in any or all of the categories above throughout the year.



Ideally, I would like to allow the user to select the employee and enter the metric needed for that employee into the appropriate table.


That said, I’ve attached my current table design. My biggest point of confusion is linking each of the Metric Categories to the employee which is the reason I think the junction table is needed? Are there any tables that aren’t necessary?


Thank you for any help!

I would have 1 metric table, not 5.
PersonID,metric,date,etc

Where's the other 3 metric tables?
How come Attendance and Discussion or tied together? I thought metrics were indepdent of each other.
What do users have to do with this?
Are users employees?

Also an elementary explanation of the real world process this models would be good. Pretend its career day at a grade school--explain to the kids what it is you do, specifically this piece of it. No database jargon, just an explanation of the real world process this supports.

plog said:

Where's the other 3 metric tables?
How come Attendance and Discussion or tied together? I thought metrics were indepdent of each other.
What do users have to do with this?
Are users employees?

Also an elementary explanation of the real world process this models would be good. Pretend its career day at a grade school--explain to the kids what it is you do, specifically this piece of it. No database jargon, just an explanation of the real world process this supports.

Click to expand...

Thank you for the reply, Plog. The idea is to track the employees' production and attendance within a call center. So, for example, if they call out, or need to leave early, the user can track it by entering an attendance record.

My apologies for the other metric tables not being displayed. I got through building the first 2 and that's when I started getting flustered.

Each metric category would be independent due to each having their own set of information. I didn't think it was wise to put all the metric data into one table. I was under the impression I still needed to link them somehow to bridge them back to the Employee.

The Users table was there for login purposes and to track who is entering issues. Users are not the employees being tracked.

Your follow up questions have got me thinking in a new direction and is making me see mistakes with my thinking.

As I said, I really got myself all messed up. Am I drilling this down too much?

Thank you!

I think you are over structuring it so far. I mean, you've got 3 tables between Employees and their attendance. That can't be right. I really don't understand what those 3 tables are doing for you.

Seems like every metric should be tied directly to the Employee table.

Since you are still in the design phase, I think my Old Programmer's Rules are appropriate.

Rule #1: If you can't do it on paper, you can't do it in Access.

That means, in practical terms, that you need a printed roadmap of where you are going. (Without a roadmap, how would you ever know you got there?) You need to drill down to the point that you have identified the various types of data you will track, the nature of the operations performed, where things go, and what you will produce.

With regard to the metrics, you need one central table for data about the people being measured. The individual metrics, if they are all independent of each other, are still dependent on the staff members you are evaluating. I.e. each metric entry relates to one person. To my mind, that probably means five child tables that are one to many as person to metric.

When drawing it all out on paper, remember this simple principle: Like goes with like; unlike probably needs a new table.

Now the more technical version: If you have a datum, it belongs somewhere. So the question is, does that datum relate ONLY to a person independent of the metrics? In which case it goes with the Person table. OR does it relate to some facet of a metric, such as the ID of the person making entry, the date, the category, the observation? In which case it goes in one of the Metric tables. IF the answer is "neither" then you have possibly discovered something that requires a new table. To decide that, you must decide how you want to use it. For instance, code translation tables to drive combo or list boxes might pop up as an "other" case.

Rule #2: Access won't tell you anything you didn't tell it first.

In practice, this means that when you are building your roadmap (see rule #1), be sure that everything in your list of desired outputs has a source. If you are going to report on W, then be sure you capture W in an input. If you are going to report on XYZ, then be sure that you capture XYZ - OR if you capture X, Y, and Z, then assure that Access knows the formula to combine them.

This sometimes means that you work backwards through your logic to assure either capture or computability for every element of output. Tedious, but worth it in not having to scramble to retrofit the world because you left out something major.

Thanks plug and Doc Man for your responses! I think I have sorted everything out