May we ask about design basics here?

[stuartnz]

Stuart, this is how I'd approach it.

-- tom

Thanks, Tom, that was just the ticket. After reading that, then carefully reading the other threads on M:M sets, it's more or less fallen into place.

I'm now trying to figure out another design issue. In order to monitor how much the interviewers cost the company in wages, I've created a "payrates" table. Each interviewer is on one of three tiers, standard, intermediate and senior. Each of those tiers has two rates, one for telephone work, the other for face-toface work. In the "jobs" table, I've added a "jobtype" field, (logical, with "T" for telephone and "F" for face-to-face). At the moment, my "payrates" table consists of an autoincrement ID field, a "ratename" field, and a numeric "rate" field, which will contain the actual $-per-hr rate.

Since there will be 6 different pay rates, but each interviewer will only ever be linked to a maximum of two of those rates, I'm trying to work out what the most efficient way to link them is.

I already have the "assignments" linking table setup, with just the "staffID" and "JobID" keys, and the relevant 1:M and 1:1 links setup. I'm thinking that If I added the "rateID" key, that would serve my purpose, since I would then have a unique combination of Staff-job-rate. Does that sound feasible?

[Tom Cone Jr]

To me it sounds too complicated.

The design should assure that the "transaction cost" doesn't change for previously entered transactions if the pay rate changes in the future. If you needed to go back and re-print an activity report (after a pay rate changes) you need to be able to reproduce the actual charges that were incurred under the former pay scale.

I tend to think in simpler terms. There's no reason to include in the data entry set design another two tables just to determinie the telephone or face to face pay rate for any give piece of work . I'd probably store the two pay rates in each person's record in the interviewers table, using a different table as the lookup source to fill in the values. This lookup table would have the 6 records you mention.

But I'd also store the computed transaction (job?) cost for each activity (transactions) in whatever table you're using to record the employee's activities each day. My approach tends to work better in small organziations since the number of changes that would have to be done if a pay rate changes is more manageable. Saved update operations can be used for larger numbers of interviewers.

In sum. Your design should accommodate future pay rate changes. While preserving the abiliity to report accurately the actual cost (pay) in earlier work. In this case, given the small number of possible pay rates, I would not link the rate tables into the set design. Instead, I think of them as separate supporting tables, sort of like the IRS tax tables. They're used for "lookup" and autofill, but aren't needed in the actual set used to support the data entry form.

HTH,

-- tom

[stuartnz]

To me it sounds too complicated.

The design should assure that the "transaction cost" doesn't change for previously entered transactions if the pay rate changes in the future. If you needed to go back and re-print an activity report (after a pay rate changes) you need to be able to reproduce the actual charges that were incurred under the former pay scale.
HTH,

-- tom

It certainly does! This is exactly the thing that was gnawing at me, how to record the historical pay rates. Your response has given me a very clear example of what a "lookup" table is, and in so doing helped clear up some similar issues with other elements of the design. Thanks again.