473,714 Members | 2,351 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)

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

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

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

Similar topics

4
6167
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 learned that I have to use the following method:
17
4017
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
7
2770
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
7
2031
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
2
2727
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 navigation to the next record using code below: Me.BindingContext(DsTrailers1,...
26
2167
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 consulting/temporary embedded and systems.
10
2076
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 the project is actually fairly straightforward, but after several meetings, emails and telephone...
10
7635
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, which can be edited, deleted or created "on the fly". The most sensible solution would seem to be...
10
1863
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?
0
8808
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8712
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9316
Oralloy
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9177
jinu1996
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6637
isladogs
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5951
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 into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4727
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3159
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2524
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.