Solved

Tracking price increases in database

Posted on 2013-01-29
3
273 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

19 Experts available now in Live!

Get 1:1 Help Now