Re: How is the design for this complex requirement ?

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

I don't know enough of your business rules to say for sure it is bad but my initial impression is that it is wholly inadequate. Just as one example among many I don't see a 1:M relationship between orders and discounts and yet you claim that is an essential element.

This forum is not an appropriate place to teach you entity-relationship diagraming so my recommendation would be that you appear unqualified to be doing what has been assigned to you and you need to either find an internal mentor or ask management for a DBA to work with you on the project.

-- 
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