Very Computer

Hi!
        What's the best design to handle changing prices for products?
O:-)  Different customers will have different discounts, prices may
change for special offers, etc...
        Changing a price shouldn't affect previous orders of that same
product, besides, I need to audit those changes.
        Any ideas? O:-)

TIA

//-----------------------------------------------
//      Fernando Rodriguez Romero
//
//      frr at mindless dot com
//------------------------------------------------

Quote:> Hi!
>    What's the best design to handle changing prices for products?
> O:-)  Different customers will have different discounts, prices may
> change for special offers, etc...
>    Changing a price shouldn't affect previous orders of that same
> product, besides, I need to audit those changes.

I don't believe there is one single standard which is applicable to
everyone.  You've pretty well stated your needs, so you'll need to
figure it out from there.

The first thing to do is to figure out what criteria controls
individual prices.  For example, if you need specific prices for
different customers, you may want to determine that each inventory item
has different price types (such as R for retail, W for wholesale) and
then your price table will be controlled by inventory item and price
type, such as:

Stock Number
Price Type
Price

If, however, you need different prices for different currencies for
different customers, you may need something like this:

Stock Number
Currency
Price type
Price

Or, if everyone has the same price, you may need to have:

Stock Number
Price

And separately, in your customer table, define the discount percent
that applies to each particular customer.

Or, perhaps you need some combination of these?

Finally, you'll need to copy the price criteria (whatever it is) from
the price table to the order detail table (for each item on the order)
so that if the price table changes, you can go back to the order detail
table to see which prices were used at the time the order was placed.

Sorry this isn't more help; There just isn't one answer that works for
every situation.

--Kevin

http://www.precisonline.com
http://www.precisonline.com/gold.htm

Sent via Deja.com http://www.deja.com/
Before you buy.

Hi!

If a change must not affect previous orders, you'll have to record
"historical" data with the order. Simply add a column for the price to
the relevant tables of the order.

To audit changes of prices, you might want to use update triggers that
record the old and the new price together with date of change and
perhaps the user in a separate audit table.

Heinz