Managing product prices per Customers/Quantities

[KitaYama]

I'd appreciate some advice on how to manage product prices.

1. Prices of the same product MAY differ for different customers
2. Prices of the same product MAY differ for different purchased quantities.
3. The change of prices of the same product MAY differ for different customers. For example the change of prices of Product1 will be 1-50-100-500 quantities for customer1 and 1-100-500-1000-5000 for customer2.

I'm working on a design as following, where a top 1 query finds the latest product price for a specific customer according to the quantity of the order.

While I appreciate any correction on the design of the tables, my main question at this point is:
1- Some products have fixed prices for all customers.
2- Some products have fixed prices regardless of the purchased quantity.
Should I add 2 checkboxes to tblProducts to isolate these two types of products and keep the prices in a different table (which I think having two tables is not correct), or simply having only one record in tblPrices is enough? (Which I don't know if leaving CustomerFK blank in tblPrices is correct)

Any kind of advice is welcomed.
Thanks

 

[CJ_London]

I use the null route for customerfk - that does not impact referential integrity

it does mean a slightly more complex query and may require a separate form for managing the data.

other factors to consider -

do all prices change for all products for all customers on the same day?

Are quantity discount levels the same for each product/customer, so just the price varies?

So you may want a ‘header’ table between product/customer and prices

edit: you might also want to consider from to quantities rather than just to - makes the required query simpler. Although you can create a query view to do this

 

[KitaYama]

do all prices change for all products for all customers on the same day?

No.

Are quantity discount levels the same for each product/customer, so just the price varies?

No. They are absolutely different.

you might also want to consider from to quantities rather than just to - makes the required query simpler.

I will consider this.

Thanks for the advice.

 

[Pat Hartman]

Maintaining different prices for customers as well as different group breaks increases the complexity of managing products and prices. The simplest solution would be to assign different product codes. So, the generic widget is A2433 but the special price widget is A2433S. That still leaves the onus on the person taking the order to "know" that this customer gets special pricing and so he has to choose the correct product code.

Another solution is to keep a separate pricing table for custom prices. This table would include the CustomerID as part of the PK or unique index. Then when an item is added to an order, the code would need to look at the custom price table and if the combination of customer/product exists, get the price/qty bread from that table and copy it into the order details table. Otherwise, pick up the price the standard way and place it in the order details table. If you use this method and your business process allows price changes up to the time the item ships, it becomes more complicated to determine which unshipped orders need to be updated.

Unless you have now and will always have a small product list and small customer list, I would not use the schema you suggested. It will require too much maintenance and will greatly annoy the users. They will want you to develop complex processes to propagate price changes so that using some logic, your queries decide which customers will be affected by a price change.

I really hate to have multiple logic paths but I think you will find the maintenance of price changes when you use the single path where the price ALWAYS is specific to a customer becomes too onerous.

I would try to find a different way of giving special customers price breaks. Maybe keep track of annual orders and after a customer spends x dollars, he gets a 10% discount on remaining orders for the year.

 

[KitaYama]

@Pat Hartman
You gave me too much and I need a little time to think about the given solution to see if I can digest all that.
Thanks for the advice.