Database Design – Best Approach for Online Store Price Changes

database-design

I am currently creating a website which will allow people to buy consumer products (deodorant, shampoo, toothpastes, etc…) from a store. Those products tend to change price and other details twice per year or more. And I want to show past purchases’ prices and other details to the client.
And I wanted to know what the best approach is.
I’ve come up with the following approaches. Are they any good?

Thanks!

1st. Approach: I have a Product table, with the columns that tend to change, appended with the time of change as a suffix.

1st Approach.

2nd Approach: In this one, I have the price and other details in a separate column.
2nd. Approach

I did make some research:
How to store prices that have effective dates?
http://talentedmonkeys.wordpress.com/2010/05/15/temporal-data-in-a-relational-database/

But couldn't find them appropriate for my problem.

Best Answer

Instead of creating a separate table for each date, create a single details table and index by product id and effective date:

ProductDetails:
   productId 
   dateEffective 
   price
   ...

This way, you just insert a new row each time the details change.

insert into ProductDetails( productId, dateEffective, price )
values ( 1234567, '10/06/2014', 1.25 );

So you get the price based on the product id and the date; this way, you can get the current price for new orders, and get the price for past orders based on the date. 

select price, dateEffective from ProductDetails where productId = 1234567 and
dateEffective between date1 and date2;

You really don't want to create a bunch of separate tables for different time periods, and you really don't want to keep adding columns to the Product table every time details update.

Related Topic