(Revised)Databa se Design question, Header with two detail.. pls help

[Krist]

Hi All,
There is some additional info I forget on this same topic I just posted.

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_Commissio n_Header (
Sales_ID Char(4) ,
Sales_Commissio n_Group Char(4),
Note Varchar(30),
Constraint Sales_Commissio n_Header_PK Primary Key(Sales_ID,
Sales_Commissio n_Group)
)
Alter Table Sales_Commissio n_Header Add Constraint
FK_Sales_Commis sion_Header Foreign Key (Sales_Commissi on_Group)
References Commission_Grou p_Header(Sales_ Commission_Grou p)

CREATE TABLE Sales_Commissio n_Detail (
Sales_ID Char(4) ,
Sales_Commissio n_Group Char(4),
Sales_From Decimal(12,2) ,
Sales_To Decimal(12,2) ,
Commission Decimal(5,2),
Constraint Sales_Commissio n_Detail_PK Primary Key(Sales_ID,
Sales_Commissio n_Group, Sales_From, Sales_To)
)
Alter Table Sales_Commissio n_Detail Add Constraint FK_Sales_Commis sion
Foreign Key (Sales_ID, Sales_Commissio n_Group) References
Sales_Commissio n_Header(Sales_ ID, Sales_Commissio n_Group)

--------------------------------------------

** ALTERNATIVE _1 :

CREATE TABLE Commission_Grou p_Header (
Sales_Commissio n_Group Char(4) Primary Key,
Note Varchar(30)
)

CREATE TABLE Commission_Grou p_Detail_Produc t (
Sales_Commissio n_Group Char(4),
Product_ID VarChar(10), -- This product_ID will be FK
reference to master product
Constraint Commission_Grou p_Detail_Produc t_PK Primary
Key(Sales_Commi ssion_Group, Product_ID)
)
Alter Table Commission_Grou p_Detail_Produc t Add Constraint
FK_Commission_G roup_Detail_Pro duct Foreign Key (Sales_Commissi on_Group)
References Commission_Grou p_Header(Sales_ Commission_Grou p)

CREATE TABLE Commission_Grou p_Detail_Brand (
Sales_Commissio n_Group Char(4),
Brand_ID VarChar(10), -- This brand_ID will be FK
reference to master brand
Constraint Commission_Grou p_Detail_Brand_ PK Primary
Key(Sales_Commi ssion_Group, Brand_ID)
)
Alter Table Commission_Grou p_Detail_Brand Add Constraint
FK_Commission_G roup_Detail_Bra ns Foreign Key (Sales_Commissi on_Group)
References Commission_Grou p_Header(Sales_ Commission_Grou p)

** ALTERNATIVE _2 :

CREATE TABLE Commission_Grou p_Header (
Sales_Commissio n_Group Char(4),
Group_Type Char(1), -- 'B': Brand Group 'P': Product Group
Note Varchar(30),
Constraint Commission_Grou p_Header_PK Primary Key(Sales_Commi ssion_Group,
Group_Type)
)

CREATE TABLE Commission_Grou p_Detail (
Sales_Commissio n_Group Char(4),
Group_Type Char(1), -- 'B': Brand Group 'P': Product Group
Product_Brand_I D VarChar(10),
Constraint Commission_Grou p_Detail_PK Primary Key(Sales_Commi ssion_Group,
Group_Type, Product_Brand_I D)
)
Alter Table Commission_Grou p_Detail Add Constraint
FK_Commission_G roup_Detail Foreign Key (Sales_Commissi on_Group)
References Commission_Grou p_Header(Sales_ Commission_Grou p, Group_Type)

The PROBLEM here is : with Product_Brand_I D , I CAN NOT make foreign key
into both Master Product and Master Brand.

So which one is better design ?
split the Commission_Grou p_Detail into Two tables, product and brand , and
make the FOREIGN KEY
to master product and master brand (previous mail)
OR
combile Commission_Grou p_Detail for Product and Brand into one table like
above
and NOT make any FK to master Product or Brand ?
Thank you for your help,
Tristant

[Erland Sommarskog]

Krist (xt****@hotmail .com) writes:

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

For some reason Krist also mailed me about this - please do not both
post and mail! - and I gave him suggestions. I'm leaving this post
here as a bookmark, to mark this question as answered.

Briefly, I first suggested that in his first details table to replace
Sales_From and Sales_To with a nullable column Upperlimit. For the key
I suggested to have RowNo a running number within the Product_ID.
A trigger may be required to ensure that Upperlimit grows with RowNo.
For updates it's best to flush all rows for the product and reload.

For the other question about Product_Brand_I D, I suggested that one
alternative would be to create a supertable with both ids in them.
Another alternative would be two have two columns, and a check
constraint requiring that exactly one of them be non-NULL. This would
call for a surrogate key to be used in the table with the commission
levels.

Since I did not have full information of the meaning of brands and
products, none of these suggestions may make sense.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp