Tracking price increases in database

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:

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.

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.

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…