Database/Schema design question regarding updates to items_for_sale over time

Question

I understand the obvious answer to my question is: "it depends". With that out of the way, I'm hoping one (or more) of you will help me see common ways to approach this...

I'm designing a database of widgets for sale. Over time the prices for each item will inevitably change. My question is should we be tracking these changes and if so, how? related table of pricing & dates? Is it common to track the change in price so you could, say, look back 3 years from now and see what changed and when. I could see how that could be valuable but is it worth the overhead?

The orders table will capture the current price on the date of the sale.

thoughts?

thanks!

Answer 1

Your intuition is right on. Don't ever lose information if you can help it!

Create a table named something like this, using SQL Server syntax:

CREATE TABLE PriceHistory (
PK_PriceHistory int IDENTITY(1,1) PRIMARY KEY, 
FK_Item int, -- foreign key to your item table
Price money,
ChangedAt datetime  -- captured at the time of change
)

You can populate the table from a trigger on your Price (or Item?) table so that when a price is changed you can automatically log the old price to your price history table. This will allow you to query the price history table to determine an historical price if ever required. I'm not a fan of triggers but this is a classic case for one...

Answer 2

Adding to Tahbaza's answer:

The importance isn't just in keeping track of historic prices, but also in easing price changes in the future. Without a "Changed At", or in this case actually a "Valid from" date to go with the price, you would always have to change the prices in the database on the exact day from which they should be applied. Having a valid from date allows you to enter new prices whenever you see fit and have them become the "current" price at the date you specified.