What is the best way to store a historical price list in a MySQL table?

Question

Basically, my question is this - I have a list of prices, some of which are historical (i.e. I want to be able to search that product X was $0.99 on March 11, $1.99 on April 1, etc...). What is the best way to store this information?

I assumed I would probably have a Product table that has a foreign key to a price table. I initially thought that storing the current price would probably be the best bet, but I think I want to be able to store historical price data, so would the better route to go be to store a table like the following for the price list:

CREATE TABLE prices (
         id BIGINT auto_increment not null,
         primary key (id),
         price DECIMAL(4,2) not null,
         effectiveStartDate DATETIME NOT NULL,
         effectiveEndDate DATETIME 
);

I'm at a bit of a loss here. I'd like to be able to search products efficiently and see how the price of that product changed over time. How can I efficiently associate a set of these prices with a product? I guess what I am asking is, 'What would be the best way to index this in order to be able to provide an efficient search for queries that span a specific set of dates?'

Answer 1

Separate the need for historical data from the need for current price. This means:

1) Keep the current price in the products table.

2) When the price changes, insert the new price into the history table with only the start date. You don't really need the end date because you can get it from the previous row. (You can still put it in, it makes querying easier)

Also remember that your order history provides another kind of history, the actual purchases at a given price over time.

Answer 2

First, make sure that you really need to do this. Are you storing orders in the same database? If so, you can always view historical price trends by examining the price of the item in orders over time. This will also allow you to make correlations between price changes and changes in ordering patterns; the only case it wouldn't address is if a price change resulted in no orders being placed.

That being said, if you want an independent record of price changes, what you've presented is good. The only thing I would recommend is eliminating the end date; unless you plan on having a gap in time where the product has no price or overlapping prices, start date is sufficient and will make your logic easier.

Answer 3

The end date may be viable for more complex system where you can plan prices of product (i.e. various seasonal promotions/etc.) ahead. (oh, this is BS, should have thought more about it ... ok, you need end date only if you plan multiple prices of product at the same time, differentiated by something else ... still it's often convenient to have it inside current record, not looking at previous/next one)

Actually with most complex systems it is not uncommon to have several current prices differentiated by "dimensions" only (i.e. some kind of attribute which may be then decided by actual shipping place or customer's country, etc...)

I would also check twice your platform/language/framework/style of work before you omit the custom "id" primary key in favor of [product_id, starting_date,..?..] composite pk. The latter is somewhat more logical choice (at least I personally prefer it), but it may backfire sometimes, for example if your DB library has only limited way to work with more complex primary keys.