Results 1 to 9 of 9

Thread: Changing Prices

  1. #1
    Join Date
    Dec 2005
    Posts
    7

    Changing Prices

    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.
    PHP Code:
    PRICELIST
        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.

    Brad

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If you want to be able to update prices individually, then it sounds like you don't want a "pricelist" entity at all:
    Code:
    ITEMS
        item_id Primary Key
        item_name
    
    ITEM_PRICE
        item_id references ITEMS
        valid_from (Date the price is valid from)
        valid_to (Date the price is valid to)
        price
        Primary key (item_id, valid_from)

  3. #3
    Join Date
    Dec 2005
    Posts
    7
    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.
    Quote 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:
    Code:
    ITEMS
        item_id Primary Key
        item_name
    
    ITEM_PRICE
        item_id references ITEMS
        valid_from (Date the price is valid from)
        valid_to (Date the price is valid to)
        price
        Primary key (item_id, valid_from) <-- valid_from?? 

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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:
    Code:
    Item_Id  Valid_From  Valid_To    Price
    =======  ==========  ----------  -----
    12345    2005-01-01  2005-06-30  500.00
    12345    2005-07-01  2005-13-31  600.00
    To identify a single row from that table you need to know the item_id and the valid_from date.

  5. #5
    Join Date
    Dec 2005
    Posts
    7
    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
    Last edited by bradles; 12-05-05 at 09:23.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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:

    Code:
    CREATE TABLE mytable
    ( col1 ..., col2, ...,
      CONSTRAINT mytable_pk PRIMARY KEY (col1, col2)
    );

  7. #7
    Join Date
    Dec 2005
    Posts
    7
    Thanks Andrew.
    Code:
    Item_Id  Valid_From  Valid_To    Price
    =======  ==========  ----------  -----
    12345    2005-01-01  2005-06-30  500.00
    12345    2005-07-01  0000-00-00  600.00
    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.
    Code:
    Item_Id  Valid_From  Valid_To    Price
    =======  ==========  ----------  -----
    12345    2005-01-01  2005-06-30  500.00
    12345    2005-07-01  2005-10-31  600.00
    12345    2005-11-01  0000-00-00  700.00
    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

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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):
    Code:
    Item_Id  Valid_From  Valid_To    Price
    =======  ==========  ----------  -----
    12345    2005-01-01  2005-06-30  500.00 -- This year's price
    12345    2006-01-01              600.00 -- Next year's price
    Here the "current" price is 500.00, but it isn't the row with the null/special end date.

  9. #9
    Join Date
    Dec 2005
    Posts
    7
    Quote Originally Posted by andrewst
    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)
    Good point. I hadn't thought of that.
    Thanks for this Andrew.

    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •