Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
lal00: As I mentioned in one of my comments, I handle effective dating on a regular basis. Please see my answer for a simple effective date handling method. It does not require one to know the period that a price is effective when a new price row is created, nor does it require one to go back an modify the previous most current row when a new row is created. –  bit-twiddler Mar 23 '11 at 14:30
add comment

3 Answers

up vote 11 down vote accepted

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:

share|improve this answer
1  
Well, this is not what I had in mind when I asked, it's better! :) –  lal00 Mar 22 '11 at 22:40
add comment

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.

share|improve this answer
    
Justin, the expiration date makes sense. You were right, I had the SQL the other way around. The output file is not very relevant to my question, I just added as a way to exemplify that I needed to sort the products by price. –  lal00 Mar 22 '11 at 22:39
    
One does not need to include an expiration date in each row, as doing so only adds additional overhead, as one is usually not given the period a price is effective when the row is created. If we have N rows for each producer_id/product_id pair in a table, each of which is effective on a certain date, the row with the greatest date_price_effective value that is less than or equal than a given date is the price that is in effect on that date. If you look at my posting, you will see SQL code that performs this type of query. I have to deal with effective dates on a regular basis. –  bit-twiddler Mar 23 '11 at 14:21
    
@bit-twiddler - Certainly, it is not necessary to have an expiration date. However, having an expiration date generally makes querying the table much easier and more efficient. Since effective date queries are generally much more common than price changes, that's generally a trade-off I'm happy to make. –  Justin Cave Mar 23 '11 at 14:27
add comment

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 };

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.