database design

need some guru advice on database design.

okay, say i have a database something like this

- customers
- sales
- salesLineItems
- products

so when the customers buy, a sale is created with several salesLineItems, each linked to a product. simple.

the problem comes when i need to change the details of the product, after the sale has been transacted. for example, i increase the price of a product by $1

in this case, my transaction history (the records in my sales and salesLineItem) will not be reflecting the correct information, because it is linked to the product... the sales reports calculated at the end of the month may not tally with the actual amount of money i collected from the transactions, becasue the records now reflect the new product price.

normally, what is done? do i make a copy of all the product information on each salesLineItem record?

The problem you are describing is due to the fact that products can have multiple prices over time, but you've only allocated a single spot to store the price. You need to be able to store multiple prices, since one product can have many prices.

I'd keep "products" and "prices" in separate tables, with rows in the "prices" table pointing to a particular product. A product can have many prices, a price can have only one prodcut. The Prices table would have columns such as "StartDate", "EndDate", "Region", "Amount".

That way, you could have prices change as time goes by but not forget what the price used to be in the past. Each "sale" on the sale table would have a TransactionDate. You would match the transaction date and product and region to find what price the customer should be charged.

You could add other attributes to "prices" such as "couponCode" so if a person had a coupon for the particular product, they'd get a special price. The coupon would be recorded in the Sale table, or in a separate UsedCoupon table that points to the Sale table.

So bottom line is start thinking of Prices as being complex objects, and belonging to a product, rather than just being a simple attribute of a product.