(Revised) How is the design for this complex requirement ?

Giganews Newsgroups
Subject: (Revised) How is the design for this complex requirement ?
Posted by:  krisli…@gmail.com
Date: 1 Sep 2006

Hi All,

I have Customer hierarchy as follow :
  CustomerCategory
  CustomerType
  CustomerGroup
  Customer

Discount can be given to a CustomerCategory, or CustomerType, or a
CustomerGroup, or a Customer , or to ALL Customer. Discount is defined
in a Promotion Table

Customer Table :
  CustomerCode
  CustomerName
  CustomerCategoryCode
  CustomerTypeCode
  CustomerGroupCode

Order Table :
  OrderNo
  OrderDate
  CustomerCode
  OrderGross
  Discount

Promotion Table :
  PromoNumber
  PromoType (ALL / CustomerCategory / CustomerGroup / CustomerType /
Customer)
  PCode (ALL / 'xxxx' )
  Discount
  ** note :
      if PromoType = PCode = 'ALL', means the Discount is given to all
customer.
      If PromoType = 'CustomerCategory'  then PCode = Customer
Category Code
      If PromoType = 'Customer'  then PCode = Customer Code
      and so on...
  ** by doing this I avoid to define discount in four table.

The question is :

1) Is there any better way to define the promotion table

2) If I do design the Promotion table like above, how is the efficient
query to find the discount for an Order ??

Thank you for your help,
xtanto

Replies