Best approach for an online store which changes its product’s price over time? [Designing the DB Table]

Question

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.

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

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.

Answer 1

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.

Answer 2

If you have one thing, which has some attributes which cannot change and others which may change over time, you really have two separate things. And that means you should consider giving each "thing" its own table.

Neither of your options use the database model to make anyone's job easier. Selecting entirely different tables is an annoying practice, but occasionally needed as an implementation concern for very large data sets. Selecting different columns is a TERRIBLE idea, that screams out for further normalization.

Use a separate table for prices, with either date ranges or a "current or closest in the past" model. Anything else is a headache that you should avoid unless you have a very good reason not to.

Answer 3

A clean design is if you have a startDate and endDate for every price:

for a new product:

• insert into price set product=X, startDate=now(), endDate=infinity

for a price change:

• insert into price set product=X, startDate=now(), endDate=infinity
• insert trigger fires: update price set endDate=now() where product=X and endDate=infinity

This way you can also easily model periods when there is no price.

Advanced: This can also be used for logging prices, so the real current price is in one table and you just update it and the logging table gets automatically filled by a trigger with the startDate and endDate as described above.

Answer 4

Alternately , you can keep a price column in order details table. Like in my case, I have a order details table where I keep the record of customer , product and order # .. here I'm saving price with all order details and yes I can edit that price for particular order.

Answer 5

Before you start any work in the database, you verify what functionality your sales people want. My assumption would be: For historic purchases, you store product, quantity and historic price. In your product database you store the current price, and an optional future price with start date.

So you always have the right price for refunds. Sales can enter price changes ahead of time. When you read a price, you check if there is a “future” price that is now in the past, and in that case make it the current price. Plus you need some cleverness to make sure you don’t charge more than the displayed price when customers buy around the time of the price change. What’s helpful is to always make changes at 3:30am when nobody is buying.