Re: How is the design for this complex requirement ?

krislioe_at_gmail.com wrote:
> 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

I can't tell you what you *should* do, but I see a number of things you should *not* do.

1. The idea that multiple discounts can be applied is a business rule that could change tomorrow -- just as soon as a manager decides the policy is too generous.. Don't design your schema around it.
2. Without knowing more, I'd be reluctant to assume that a customer can be classified into only a single 'group' or 'type' (whatever those are).
3. You state that 'area' is an attribute of 'orders' but you also have 'area' in your 'area hierarchy', implying that 'area' is an attribute of customer, not order.