# Thread: Normalise Order System - Price

1. ## 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,

2. Originally Posted by -Ice-php
Is this approach a logical step?
yes, it's a very common solution.
Originally Posted by -Ice-php
Are there any other ways of solving this problem?
none that are more elegant.
Originally Posted by -Ice-php
Assuming the rest of my tables are normalised, would this second price be breaking the rules of normalisation?
no.

3. 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. Originally Posted by Dr John
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. Originally Posted by longneck
i would just use NULL instead.
Between startDate and endDate probably would not return anything , so you need the distant future date.

6. Code:
```where now() between startdate and enddate
or (startdate < now() and enddate is null)```

7. fair enough.

#### Posting Permissions

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