Tracking price increases in database

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.
rrhandle8Asked:
Who is Participating?
 
AielloJConnect With a Mentor Commented:
rrhandle8,

I would suggest putting the prices, along with the Start and End dates in a separate table.  What you would do next, depends on the requirements of your application.  Here's a couple of ideas.

1) You can then select, or join to the right price, based on the call type and date of the call being between the start and end dates in the prices table.

2) Add an 'Active' column to the prices table, with only one price being active for any given call type.  You can then select or join based on the call type and it being 'Active.'

Cardiac, $220, 1/1/2011, 12/31/11
Cardiac, $250, 1/1/2012,  5/12/2012
Cardiac, $275, 5/13/12, NULL

There's a few ways to do this.  The best choice depends on the requirements of your application, such as, do you have to be able to report on past prices for each call type.  Do you want to pre-populate future pricing information?  I personally favor the separate table and a little bit of extra design time up front.  Most good applications get enhanced with additional features over time.

Best regards,

AielloJ
0
 
chaauConnect With a Mentor Commented:
usually when you design your transactions (or orders, EMT, etc) table you have a price column there as well. This means that suppose you will have the CallId column that will link to your CallType table, together with the price that was actual at the time of call. The CallId from your EMT table will only be used to retrieve the name of the CallType, but the actual price will come from the EMT table itself.

This option will also provide you a "feature" to have a "price override" for the "superusers" if they have this right.
0
 
rrhandle8Author Commented:
AielloJ, that is exactly how I have done it in the past, so I will assume I have been building the best possible solution all a long. :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.