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?
Best Answer
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: