Solved

Tracking price increases in database

Posted on 2013-01-29
3
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how the fundamental information of how to create a table.

734 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