Solved

Tracking price increases in database

Posted on 2013-01-29
3
272 Views
Last Modified: 2013-01-29
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.
0
Comment
Question by:rrhandle8
3 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 150 total points
ID: 38833377
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
 
LVL 13

Accepted Solution

by:
AielloJ earned 250 total points
ID: 38833602
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
 

Author Closing Comment

by:rrhandle8
ID: 38833645
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

Featured Post

How your wiki can always stay up-to-date

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

Join & Write a Comment

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now