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

Not enough information provided to help you.

1. What is the Oracle version number?
2. If multiple discounts are applied how are they applied? For example lets say there are two 10% discounts.

100 * 0.90 = 90
  90 * 0.90 = 81

or

100 * 0.80 = 80?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org