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 (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!