How is the design for this complex requirement ?

Hi All,

We have a Discount scheme that can be given for all possible combination of all
customer, area and product hierarchy (attached below).

for example :

Promotion No.001 =>for sales on : CustomerGroup 'A', Area 'B', and ProductBrand 'C' will get discount 2 %
Promotion No.002 =>for sales on : AllCustomer, District 'D', and Product 'E' will get discount 2.5 %
Promotion No.003 =>for sales on : CustomerType 'F', AllArea, and ProductGroup 'G' will get discount 3 %
... and any possible combination.

And it is possible for an Order / Invoice to get discount from two or more different promotion.
e.g: Order 001, get 2 % from Promotion No.001 and 3 % from Promotion No.003, so totally it gets 5% discount.

(1) Customer Hierarchy

AllCustomer
CustomerGroup
CustomerType
Customer

(2) Area Hierarchy

AllArea
Region
District
Area

(3) Product Hierarchy

AllProduct
ProductGroup
ProductBrand
Product

Our Orders Data has following attributes :   OrderNo.
  Customer
  Area
  Product
  Gross
  Discount

The question is : I am looking for the efficient way to do this,

1. How should I design the tables to accomodate all possible discount combination above ?
2. How is the query to determine the total discount that an Order should get ?

Thank you for your help,
xtanto Received on Thu Aug 31 2006 - 10:45:39 CDT