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

The answer to question 2 depends on how you design your tables, which is your question 1.

To answer question 1 you really should not design your tables based on solving this one problem but rather you should design them based on the data (entities and attributes) and the relationships between data elements. Some times you can devise more than a single valid design and you may choose one over the other based on the desired end result, but trying to design for the solution to a specific narrow problem is likely asking for trouble meeting other requirements.

One method of calculating the discount might be to perform a process that checks the order against the requirments for each promotion and if elgible adds the discount to the value. By cycling through all promotions the column value would then hold the cumulative sum of the discounts. You might be able to do this as a function on insert or via a regular batch process depending on how orders are handled in your environment.

HTH -- Mark D Powell -- Received on Thu Aug 31 2006 - 12:23:57 CDT