2 questions aboutCustomer Discounts with percentages

I have been asked to create a database that creates orders, debits stock etc for my company. Now I have been asked to put in some kind of discount table with percentages for each individual item for each individual customer. What would the best way of doing this?

I figured in the Customer Table I would have a code such as 'CustomerADiscount' and 'CustomerBDiscount' etc. and then in my table maybe

Product A - CustomerADiscount - 10%
Product A - CustomerBDiscount - 20%

Product B - CustomerADiscount - 5%
Product B - CustomerBDiscount - 7%

This link these values up within a query? Would this be the best way of doing it?

Is Access able to determine a percentage in "10%" or "5%" or do I need some type of formula for it instead?

This would need to be linked to the orders table so you would need a id field to do the calculation on in a query.

percentages for each individual item for each individual customer

Click to expand...

If that is what you need, then it requires a completely new table and you don't need to do anything to your current Customers table. This would be the structure of that table (along with sample data):


Discounts
CustID, ProductID, Discount
13, 44, .05
22, 198, .12
13, 29, .03
39, 198, .09

I would store the Discount as a Double, not a percentage (.05 and not 5%), because you will be using it to do multiplication and saving it as a percentage will require you to convert it to a decimal anyway.

Have you considered what will happen if/when the discount for a particular customer and product changes? You may need to identify the DiscountValidFromDate, or identify when and for how long a DiscountValue is in Effect. You should record Quantity,AgreedToPrice(Discount) in the OrderDetail. Will you be able to change the Discount for a Customer/Product combination without affecting all existing Orders?
Can you have a loyalty program or a "clearance sale" or a "revised/special discount" without affecting existing records?
Just some things to consider.