history of records in lookup tables

i hope the term 'lookup table' is well chosen, what i mean is for example a rate table (lookup) with the following rates:

cheap: $15,-

Medium: $30,-

expensive: $45,-

we're at the situation that for a given entity (we call it 'fault', it is a malfunction of a device, airco, elevator, krane, toilet etc.) a constructor is hired to fix that device. that constructor has these three (made up) rates: cheap, medium and expensive.

When the constructor fixes the fault, he enters the hours worked and the rate (when a senior has done the job, 'expensive', and when a junior has done the job, 'cheap')

technically, we then add a FK from the Fault table to the Rates table.

So when the invoice has to be printed, we get the rate via the FK and the hours worked from the fault record.

Problem is that when the constructor changes his rates, and you recalculate an old invoice months later, other amounts are calculated for the invoice because the record has changed.

So we have to construct some kind of history, and that's the question: how is that done?

what i've come up with is 2 different situations, and the question is: is one of these a good one are there better ways?

1 add a valid-from and valid-until field at the rate table, so when you edit a value, you in fact create a new record with new valid dates. downside is you have to always get the rates with a specific date in mind, which for the current situation 开发者_C百科(the actual rate at this moment) is not neccessary.

2 don't put a FK from fault to rate, but when you set a rate at a fault, you just copy the VALUE from rate to fault. downside is that when the fault is still editable, when you edit the rate, the fault's rate is not updated. And, when you edit a fault, you get a dropdown box with 3 values to choose from, non of which are the same of the current values.

At this point thanks already for reading this entire post!

I don't like #2; I never like replacing relationships with actual values (denormalizing) if I can help it. Also, it makes auditing a lot harder; if there's a weird value in for the rate, where did it come from?

The problem with #1, though, is that if for some reason you change the date of the invoice, it should probably still have the same rate that it had when it was originally created.

For these reasons, I'd recommend doing the part of #1 where a rate change always created a new row, but then link from each fault to the rate that was actually applied (i.e. rather than relying on the date to join to a rate, actually store a rate id with the fault).

One approach to finding the current rate is just to look for the one that has no end date. Or alternately, don't use end dates at all (the start date of the next rate is treated as the end date of the previous rate), and just sort by date and take the last one.

There was a good discussion of this over on Programmers.SE

How to Store Prices That Have Effective Dates

It's a well-known problem and using effective dates is the best way to do it.

I'd suggest keeping a table of contractor rates, ordered by date. When a contractor's rates change, instead of changing the existing rate add a new entry. When you need to get the current rate, sort by the timestamp descending and limit 1. Add the date entry for the current rate entry to each job record and then you can perform a simple join to get all the information at once.