I would like to know the best way to deal with the following problem.
In a database, I need to record EMT calls. The reason for the call dictates the price (cardiac: $250, trauma: $400, etc)
Creating a table with a type and corresponding price is simple enough, but the problem is how to handle future price increases. If the price of a cardiac call goes up to $275, I want the EMT calls that are already in the database to stay at $250. And, if I have to enter a call from over a year ago when the price of a cardiac call was let's say $220, I want it to be $220 in the database.
In the past I have handled this type of problem by entering a Start and Finish date for each change in the price. This gives me a table with enteries like this:
Cardiac, $220, 1/1/2011, 12/31/11
Cardiac, $250, 1/1/2012, 5/12/2012
Cardiac, $275, 5/13/12, NULL
In this case the price of a Cardiac call was $220 up to 1/1/2012 when it increased to $250, and it again increased on 5/13/2012 to $275.
This solution will work, but I sense there is a better way to handle this.