It feels too complicated primarily because you're very close to the data. Everything looks bigger when you're closer to it. As an example, you're listing the credit strategy and the sampling strategy and exceptions all at the same time, when those three items are not tightly related information.
Pull back for a minute.
The design of the database gets less confusing if you pretend for a moment that you (like me) DIDN'T know which tasks would be sampled or how the credits would be given...
YOUR BUSINESS PROCESS:
Here's the overall process:
1) (someone) will input to the system each piece of physical work done by a person.
2) The system will (somehow) give the person credit for the work done.
3) The system will (somehow) determine which physical work to sample.
4) (someone) will review the sampled physical work for errors
5) (someone) will input the errors from the sampled work
6) The system will (somehow) provide reporting on the error rate, by person as well as department
QUESTIONS:
The above is the order if credit is given for unsampled work. Otherwise, reverse steps 2 and 3.
The system is only going to report the credits, not track them being spent on anything, right?
I also have the question if sampling is a percentage of each person's work, or of your department's work?
And, bigger picture, why does your company want the worker to know which item will be sampled? That appears to be a significant violation of standard ISO quality control methodologies.
DESIGN STEPS
So, in database and application design, you determine what information you absolutely need to know about the work done, for step 1.
You determine credit rules for work of each type, for step 2.
You determine sampling rules for work of each type, for step 3.
(mostly looks like (A) start over each day or each month, and (B) what percentage to sample)
You determine how your system will tell the QC department which work to sample, for step 4.
You determine how the QC department will tell the system which sampled work had errors, for step 5.
GUESS AT TABLES
Here is a first shot at the entity tables:
Code:
tblStaff (your people)
tblWorkTypes (one code for each type of work)
tblSamplingRules (one entry per Worktype, how to sample)
tblCreditRules (one entry per Worktype, how to credit)
tblTasks (one entry per task, created when your people turn in their work)
tblSamples (one entry per task in tblTasks, with a field for "waived" if that task is not to be sampled, and a field for "error" if that task was determined to have errors)
Technically, those last two could be in the same table, but I assumed them separate in case there was a reason for only storing records for Tasks that were to be sampled.
Now, the above mock-design assumes that it's a reasonable amount of work to enter and track each task independently, and that the system should assign which tasks will be sampled. If that doesn't fit the business model, then tblSamples should be de-linked from tblTasks, and should perhaps be driven from the QC output rather than from the task completion.
Windows XP 
Access 2007
Reply With Quote
