How to store prices that have effective dates?

Question

I have a list of products. Each of them is offered by N providers.

Each providers quotes us a price for a specific date. That price is effective until that provider decides to set a new price. In that case, the provider will give the new price with a new date.

The MySQL table header currently looks like:

provider_id, product_id, price, date_price_effective

Every other day, we compile a list of products/prices that are effective for the current day. For each product, the list contains a sorted list of the providers that have that particular product. In that way, we can order certain products from whoever happens to offer the best price.

To get the effective prices, I have a SQL statement that returns all rows that have date_price_effective >= NOW(). That result set is processed with a ruby script that does the sorting and filtering necessary to obtain a file that looks like this:

product_id_1,provider_1,provider_3,provider8,provider_10...
product_id_2,provider_3,provider_2,provider1,provider_10...

This works fine for our purposes, but I still have an itch that a SQL table is probably not the best way to store this kind of information. I have that feeling that this kind of problema has been solved previously in other more creative ways.

Is there a better way to store this information other than in SQL? or, if using SQL, is there a better approach than the one I'm using?

Answer 1

For items that vary based on time (such as being able to answer things like "what was the price of X on date D" or "which cow was in feedlot Q on date E") I recommend reading the book "Developing Time-Oriented Database Applications in SQL." While this book is out of print, the author has graciously made available the PDF of the book as well as the associated CD on his website.

http://www.cs.arizona.edu/~rts/publications.html (look for the first item under "books").

For a brief introduction online, see:

• http://talentedmonkeys.wordpress.com/2010/05/15/temporal-data-in-a-relational-database/
• http://martinfowler.com/eaaDev/TemporalProperty.html

Answer 2

I'd certainly store the effective date in the database. After all, it's likely that people are going to want to be able to run queries to see how the price has changed over time or to cross-check oddities in orders against the historical product price table. Depending on the type of queries you are running and the frequency of price changes, it may make sense to have separate tables for the current price and the historical prices.

In most systems that store prices, you would want an expiration date column in addition to the effective date to make it easier to determine which price is currently in effect since it saves you the trouble of having to look at the prior or next row to figure out which price was in effect at a given point in time. I'm not clear on what your NOW() >= date_price_effective condition is doing-- presumably, that returns the current price along with all prior historical prices which seems odd to me. I would think that the "effective price" would be the current price which would be defined by something like NOW() BETWEEN date_price_effective AND date_price_expired

I'm also not sure what your file is supposed to look like. It's not clear to me what provider_1 represents-- the provider_id=1 price?-- or how you're ordering the provider data-- why does provider_1 appear first for product_id_1 and third for product_id_2.

Answer 3

If I understand your problem statement correctly, you need a way to handle generational data (i.e., the table contains multiple rows for each provider_id/product_id pair each of which is date sensistive). In that case, you are looking for the most recent price for a product with an date_price_effective value that is less than or equal to today. This type of situation is easy handled using an SQL subselect.

 SELECT 
   provider_id, product_id, price, date_price_effective 
 FROM 
   price_table a 
 WHERE 
   date_price_effective = 
     (
       SELECT 
         MAX(date_price_effective) 
       FROM 
         price_table b 
       WHERE 
         b.provider_id = a.provider_id AND 
         b.product_id = a.product_id AND
         b.date_price_effective <= NOW() 
     );

A price is effective as long as it has the greatest date_price_effective value that is less than or equal to the date that the query is executed. A date_price_effective value that is greater than today is a future effective date. The code listed above returns the row data for each provider_id/product_id pair that has the date_price_effective value that is the closest to, but not later than the date the query is executed. The solution automatically brackets prices into effective date ranges. The primary key for this table would be the triple { provider_id, product_id, date_price_effective };