Question about multiple rates tables

[Tony]

Hi All,

I'm at an impasse with this. I've got a series of work records that get
labor rates applied to them, with the labor rates being updated yearly.
Ideally, I'd like to store the rates in tables (year1rates, year2rates,
etc.) and have a field in the work records that identifies which rates table
to use for that particular record. Overwriting the rates table is not an
option since users will need to continue to run reports against the old
records/rates. And, I suspect it's a bad idea to store the rates in the
labor table to conform to normalization.

So, I essentially need a way to 'point' each work record to the appropriate
labor rates table.

Any suggestions?

Thanks & Ciao,

Tony

 

[Les]

Do you have multiple records for rates each year? If so,
what else determines which rate is applied to a work
record?

Assuming you do have another field: Create a rates table
using year and this field as the key. You can save all
rates into 1 table.
Add a new field onto your work records table, for year.
Join the tables by year and other field.

 

[Tony]

Hi Les,

I may or may not have multiple sets of rates each year. And, I was grossly
negligent in forgetting to mention that I've got to account for multiple
clients and overtime & doubletime rates (neither of which is calculated
based on the base rate) that are based on different labor types ...

After working on this today, I think what I really need to do is to keep
separate tables for each combination of client & rate year. So, I'd have:

tblClient1Year1
tblClient1Year2
tblClient2Year1
tblClient2Year2

Then in the work record, I'd have a field (applicableRate) that would store
a 'pointer' to the appropriate table. Setting up the tables and most of the
relationships is done, but I just can't figure out this last piece, whether
it's a relationship or something that's pulled together in a query.

Hope that explains things a bit better.

Thanks again,

Tony

 

[Van T. Dinh]

If you have multiple factors determining the rate applicable for each work
Record, it may be simple to store the ApplicableRate in the work Record.

Note that you are storing *historical* data and it is generally acceptable
to store historical data even though it is may duplicate the data in the
tblRates.

 

[Tony]

Van,

Thanks for the suggestion. I thought about doing this early on but thought
it violated normalization rules. Any thoughts in that regard?

Thanks,

Tony

 

[Van T. Dinh]

There are cases where you need to store what looks like duplicate data.
These cases are often related to the passage of time or historical data.

Luke Chung of FMS mentioned this briefly in one of his article:

http://www.fmsinc.com/tpapers/datanorm/index.html