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.

 

[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.