Order Entry DB, Volume Discount Calculations

[golfnooch]

I am developing a order entry database for a customer. One product line has discounts when the customer buys a certain volume. they are not consistent in % or $, rather, there is a different price break point and discount amount for each product in this category. I would like to have the entry form dynamically update the new price each time the quantity ordered changes. I was thinking of either keeping a table that had the price levels in it, or doing some sort of module. Either way, im not sure how to configure the action efficiently. Any suggestions on how to accomplish this?

 

[JerryDennison]

I would recommend using a table for the price break levels. It would basically be a one-many link to your product where you can define the minimum or maximum quantity and price for that quantity. The tricky part is building the link between the quantity ordered and it's associated price break. It would probably be easier done if you store both the minimum and maximum quantities associated with a given price but this would mean more complex validation during data entry. If you define only the cutoff point for your quantities(either min or mx) it would be best to build a function that determines which price break level each quantity ordered is eligible for. You would then use the function to determine the price break and store that value into your order table(this is one of those rare instances where it is acceptable to store a calculated or dependent value).