473,569 Members | 2,900 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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)



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)


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,

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)
combile Commission_Grou p_Detail for Product and Brand into one table like
and NOT make any FK to master Product or Brand ?
Thank you for your help,
Jul 20 '05 #1
1 3270
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

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
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Shufen | last post by:
Hi, I'm a newbie that just started to learn python, html and etc. I have some questions to ask and hope that someone can help me on. I'm trying to code a python script (with HTML) to get values from a html form that consists of about 10 checkbox and a textbox where user have to key in a value to perform a search. From python tutors, I...
by: Shailesh Humbad | last post by:
I just posted an article I wrote called ASP Speed Tricks. It covers techniques to optimize output of database data in HTML, for both simple tables and complex tables. More advanced ASP authors might be interested in the complex table optimizations. Please check it out at: http://www.somacon.com/aspdocs/ Hope you enjoy, Shailesh
by: Harlan Messinger | last post by:
How far back in their version history did Netscape and Internet Explorer support — and – codes for em and en dashes in text? In ALT attributes? In TITLE tags? I notice that Netscape 4.7 and 6 and IE 6 all display these correctly as dashes even when the charset is specified as ISO-8859-1. Is that supposed to happen? -- Harlan Messinger
by: Jan Roland Eriksson | last post by:
I'm posting a revised version of the meta FAQ for this NG. Beware that there are a few links in there that does not have a resource available for them yet but, over and all, this following document should be usable as presented. Rip it apart at your own discretion... ===== Archive-name: www/stylesheets/newsgroup-faq
by: Jeff Brown | last post by:
I have my form loading correctly and when i use navigation buttons they work correctly, but the nav buttons would be a pain when the number of records increased. What i am trying to do is code the combo box's SelectedIndexChanged event so that when the index is changed the other records change? does this make sense? i have been doing...
by: CBFalconer | last post by:
I have modified my ggets utility, to simplify the code and reduce the requirements on the standard library. The external action is totally unchanged, so there is no real need for anyone to upgrade. Available at: <http://cbfalconer.home.att.net/download/> -- Chuck F (cbfalconer@yahoo.com) (cbfalconer@maineline.net) Available for...
by: Paul H | last post by:
I am trying to get the spec for a database. The trouble is the client frequently blurts out industry jargon, speaks insanely quickly and is easily sidetracked. They are currently using around 30 different spreadsheets and they want me to refine those spreadsheets in to an Access DB. The current system is a complete mess. My gut feeling is...
by: teddysnips | last post by:
My clients have asked me to maintain a database that was developed in- house. It's pretty good, considering the developer isn't a "programmer". The first thing they want me to do is to split it into a Front End/ Back End, which is very sensible. However, there are a number of ad hoc queries that are required to be available to all users,...
by: _Who | last post by:
Seems to me that I should be able to do something like: ....runat="server" change from white.css to black.css... Of course if I could have found out how, I wouldn't be bothering you. Is something like that possible, so that I can let the users select the color scheme they like?
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.