By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.