Timeline for How to store prices that have effective dates?
Current License: CC BY-SA 2.5
4 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 23, 2011 at 14:27 | comment | added | Justin Cave | @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. | |
| Mar 23, 2011 at 14:21 | comment | added | bit-twiddler | 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. | |
| Mar 22, 2011 at 22:39 | comment | added | edmz | 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. | |
| Mar 22, 2011 at 22:02 | history | answered | Justin Cave | CC BY-SA 2.5 |