Hi All,

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.
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.

Brad

If you want to be able to update prices individually, then it sounds like you don't want a "pricelist" entity at all:

Hi Tony,

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?

Thanks again.

Brad.

I mean that the primary key of ITEM_PRICE consists of the two columns item_id and valid_from. The data may look like this:
To identify a single row from that table you need to know the item_id and the valid_from date.

That looks pretty cool.
How do you have two primary keys? Can it be done in Access and MySQL?

[EDIT] Just realised I could highlight two rows in table design and assign them both as primary key.
Unsure if this can be done in MySQL.

Brad

I don't know mySQL, but I'd be very surpised if it couldn't cope with compound keys. The SQL syntax would be something like:

Thanks Andrew.
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.

Thanks.

Brad

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):
Here the "current" price is 500.00, but it isn't the row with the null/special end date.

Good point. I hadn't thought of that.
Thanks for this Andrew.

Brad