title: What is the best way to store a historical price list in a MySQL table?
body: <p>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?  </p>
  
  <p>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:</p>
  
  <pre><code>CREATE TABLE prices (
           id BIGINT auto_increment not null,
           primary key (id),
           price DECIMAL(4,2) not null,
           effectiveStartDate DATETIME NOT NULL,
           effectiveEndDate DATETIME 
  );
  </code></pre>
  
  <p>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?'</p>