How to store prices that have effective dates?
Posted
by
lal00
on Programmers
See other posts from Programmers
or by lal00
Published on 2011-03-22T21:48:19Z
Indexed on
2012/03/23
11:39 UTC
Read the original article
Hit count: 268
design-patterns
|database
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?
© Programmers or respective owner