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

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 Received on Fri Sep 01 2006 - 11:34:55 CDT