SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict -Ice-php's Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Normalise Order System - Price

    Hey,

    I have created an order system for an ecommerce site. My problem comes when I am trying to store the price. Firstly I have the product price in the products table. The problem occurs if the product price changes after a customer has placed an order. All invoices etc... will now have different totals if the price has changed.

    The solution I came up with was to place the sale price at that time in the Order Line table for each product in the order. Is this approach a logical step? Are there any other ways of solving this problem? Assuming the rest of my tables are normalised, would this second price be breaking the rules of normalisation?

    Cheers,
    -Ice

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by -Ice-php View Post
    Is this approach a logical step?
    yes, it's a very common solution.
    Quote Originally Posted by -Ice-php View Post
    Are there any other ways of solving this problem?
    none that are more elegant.
    Quote Originally Posted by -Ice-php View Post
    Assuming the rest of my tables are normalised, would this second price be breaking the rules of normalisation?
    no.

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    An item has many prices = new table

    table - itemPriceDate
    itemID------PK
    startDate---PK
    endDate
    price

    Assumption: prices are valid for an entire day, new prices take over the next day. Otherwise you need to add endDate to the PK

    Pull out the price where saleDate between startDate and endDate for that itemID
    Before the price is updated, endDate = now() + 100 years, then when you get a new price, you set the old one to what the real end date was.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John View Post
    Before the price is updated, endDate = now() + 100 years, then when you get a new price, you set the old one to what the real end date was.
    i would just use NULL instead.

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck View Post
    i would just use NULL instead.
    Between startDate and endDate probably would not return anything , so you need the distant future date.

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    where now() between startdate and enddate
       or (startdate < now() and enddate is null)

  7. #7
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    fair enough.


Bookmarks

Posting Permissions

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