Re: How is the design for this complex requirement ?

From: <krislioe_at_gmail.com>
Date: 31 Aug 2006 17:07:43 -0700
Message-ID: <1157069263.656315.168570@h48g2000cwc.googlegroups.com>

Hi All,
Thanks for your response.

Additional info :
- Oracle version : 10g
- If multiple discounts are applied, e.g: two discount, 10% each, then the total discount is 20%, if gross = 100, then netto is 100 * 80% = 80.

The design that comes to my mind is like below, please give me correcttion and recommendation :
The Promotions is defined in master-detail :

- one master table
- three detail tables (one for each possible hierarchy)
- one additional Tabel to store detail of discount an Order get,

because it can get 2 or more discount.

PromoHeader :

- PromoNumber
- Description
- StartDate

PromoDetail1 :

- PromoNumber
- PType (ALL / CustomerGroup / CustomerType /  Customer)
- PCode (ALL / 'xxxx' )

  ( note : 'xxxx' can be Customer Group Code, Customer Type Code or Customer Code )

PromoDetail2 :

- PromoNumber
- PType (ALL / Region / District /  Area)
- PCode (ALL / 'xxxx' )

  ( note : 'xxxx' can be Region Code, District Code or Area Code )

PromoDetail3 :

- PromoNumber
- PType (ALL / ProductGroup / ProductBrand /  Product)
- PCode (ALL / 'xxxx' )

  ( note : 'xxxx' can be ProductGroup Code, ProductBrand Code, Product Code )

PromoByOrder

- OrderNumber
- PromoNumber
- PType
- PCode
- Discount (%)

(if an Order get two discount, there will be two records on this table for thet order)

The question is :
1) is this a 'good enough' design ?
2) if I do go with this design, how is the efficient query / plsql to calculate the discounts for an Order.

Pls help..

Thank you very much,
xtanto

DA Morgan wrote:
> 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
Received on Thu Aug 31 2006 - 19:07:43 CDT