469,167 Members | 1,238 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Database Design question, Header with two detail.. pls help

Hi All,

I have a database design question, pls give me some help..

I want to define tables for salesman's sales target commission . The
commission could be given per EITHER sales amount of : Group of
Products OR Group of Brand.
e.g : the data example :
For one salesman_A :
product_1, product_2, product_3 etc.. => sales = $100 - $200 =>
commission = 5%
product_1, product_2, product_3 etc.. => sales = $201 - $400 =>
commission = 10%
Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>
commission = 2.5%
Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>
commission = 5%

Below is my table design, is this a good design or something is wrong
here ? Thank you for your help.

CREATE TABLE Sales_Commission_Header (
Sales_ID Char(4) ,
Sales_Commission_Group Char(4),
Note Varchar(30),
Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,
Sales_Commission_Group)
)
Alter Table Sales_Commission_Header Add Constraint
FK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)
References Commission_Group_Header(Sales_Commission_Group)

CREATE TABLE Sales_Commission_Detail (
Sales_ID Char(4) ,
Sales_Commission_Group Char(4),
Sales_From Decimal(12,2) ,
Sales_To Decimal(12,2) ,
Commission Decimal(5,2),
Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,
Sales_Commission_Group, Sales_From, Sales_To)
)
Alter Table Sales_Commission_Detail Add Constraint
FK_Sales_Commission Foreign Key (Sales_ID, Sales_Commission_Group)
References Sales_Commission_Header(Sales_ID,
Sales_Commission_Group)

CREATE TABLE Commission_Group_Header (
Sales_Commission_Group Char(4) Primary Key,
Note Varchar(30)
)

CREATE TABLE Commission_Group_Detail_Product (
Sales_Commission_Group Char(4),
Product_ID VarChar(10), -- This product_ID will be FK
reference to master product
Constraint Commission_Group_Detail_Product_PK Primary
Key(Sales_Commission_Group, Product_ID)
)
Alter Table Commission_Group_Detail_Product Add Constraint
FK_Commission_Group_Detail_Product Foreign Key
(Sales_Commission_Group)
References Commission_Group_Header(Sales_Commission_Group)
CREATE TABLE Commission_Group_Detail_Brand (
Sales_Commission_Group Char(4),
Brand_ID VarChar(10), -- This brand_ID will be FK
reference to master brand
Constraint Commission_Group_Detail_Brand_PK Primary
Key(Sales_Commission_Group, Brand_ID)
)
Alter Table Commission_Group_Detail_Brand Add Constraint
FK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)
References Commission_Group_Header(Sales_Commission_Group)

thank you,
Tristant
Jul 20 '05 #1
0 1194

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Lloyd Stevens | last post: by
1 post views Thread by David Horowitz | last post: by
2 posts views Thread by Jennifer | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.