I run a service business whereby client's book me for my service 12 months or more in advance. As such, I guarantee prices on the products produced for the service up to 8 weeks after the service is performed. After that, prices revert to my current prices which usually have changed.
Does anyone have an idea of how to set this up in a database so that I can cater for clients on old pricelists as well as clients on current price lists.
The following is one idea I have wherby I can enter the valid_from and valid_to dates in a PRICELIST table. Then I can add pricelist items (products) into a PRICELIST_ITEMS table that is related to the PRICELIST table to figure out which prices are valid.
pricelist_id (Primary Key)
valid_from (Date the pricelist is valid from)
valid_to (Date the pricelist is valid to)
PRICELIST_ITEMS (or probably better known as products)
item_id (Primary Key)
pricelist_id (which pricelist the product belongs to for its price)
item_name (name of product)
price (price of product)
The only problem with this setup I guess, is that I need to recreate the entire pricelist if I wish to update one product. Can someone point me in the direction of a better model (is that called a schema?) for this type of thing?
Thanks for your reply. I was starting to think this afternoon that I probably should be able to adjust individual items like you've done here but didn't quite know how to go about it. This looks really good.
Just one question: What did you mean with the line: Primary key (item_id, valid_from) on the bottom of the ITEM_PRICE table? I don't understand the valid_from Primary key...is that a typo or am I missing something?
Originally Posted by andrewst
If you want to be able to update prices individually, then it sounds like you don't want a "pricelist" entity at all:
item_id Primary Key
item_id references ITEMS
valid_from (Date the price is valid from)
valid_to (Date the price is valid to)
Primary key (item_id, valid_from) <-- valid_from??
Lets say the price of my product (item_id 12345) was updated from $500 to $600 starting from 2005-07-01 as indicated in your example above.
I plan to leave the valid_to column of the new price null or 0000-00-00 as I will not know the exact date of a future increase on the price of this product...is that a bad idea?
Basically a client's booking details will have a field called "prices_confirmed_until" in order to work out what price the client has to pay for their products.
To give an example. Lets say the client booked me back in January of 2004 to do their job on 2005-02-01. I guarantee the prices until 8 weeks after this date and then prices revert to current prices. So that means their "prices_confirmed_until" field will have a value of 2005-03-29.
Looking at the scenario where a client submits their order on 2005-11-15, well past the "prices_confirmed_until" date. The plan is to check this date against the "prices_confirmed_until" field. In this case it will say current prices apply, as we only guaranteed them until 2005-03-29 (8 weeks after job date). Then it needs to find the correct price to charge. I'm guessing that I should get the program to find the null valid_to date and ensure the valid_from date is prior to the date their order was submitted.
Am I going about this arse about? Something tells me there is probably a more elegant solution to processing this data.
I would set the valid_to date for the latest price either to NULL or to a very high date (e.g. 4000-12-31). That way you can always find the relevant price for any given date using one of the following criteria:
given_date between valid_from and valid_to
given_date between valid_from and coalesce(valid_to, date '4000-12-31')
I would not always look for the price with the null or special date to find the current price, since that prevents you from setting up future prices in advance like this (current date 2005-12-07):