How to store prices that have effective dates?
- by lal00
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?
