mysql记录历史价格,在MySQL表中存储历史价格表的最佳方法是什么？

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?'

解决方案

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.