Handling new and old prices
-
I am looking for some advice on how to handle prices changes. The client sell numerous products each of which will have a price associated with it. However, once or twice a year prices will change.
The price a customer pays will be captured with their order, and be stored in the orders table. Should I be concerned with keeping the old price of an item in the Product table, or should I just updated the old price with the new price?
If I do keep the old price, what strategy would you recommend for differentiating between the new price and the old price?
Thank you,
-
The first thing that comes to my mind would be an audit table which would record the old price and when it was changed. Could be updated via a trigger.
Steve
-
I would suggest that your financial-type people would be very unhappy with you altering Orders table records after orders are completed
One solution is to have a ProductPrice table that looks like
ProductID, Price, EffectiveDate
which for a given product records each price it has ever had, and the date it became effective. For a given product id @ID, you can then obtain the price on @date with
(select top 1 Price from ProductPrice where ProductID = @ID and EffectiveDate <= @date order by EffectiveDate desc)
(NB: you must make sure all products have a 'day 0' ProductPrice entry)
You can then use this expression to look up the price on the order date, or the price now, as desired
-
HI
I concerning with AKM.
The best method are create a table for you save a price.
Use a table like AKM describe. Its a best pratice and you are compliance with best pratices for modeling methods.
Hildevan O Bezerra
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply