|Subject:||(Revised) How is the design for this complex requirement ?|
|Date:||1 Sep 2006|
I have Customer hierarchy as follow :
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 :
Order Table :
Promotion Table :
PromoType (ALL / CustomerCategory / CustomerGroup / CustomerType /
PCode (ALL / 'xxxx' )
** note :
if PromoType = PCode = 'ALL', means the Discount is given to all
If PromoType = 'CustomerCategory' then PCode = Customer
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,