October 7, 2009 at 5:45 am
hi
i have two queries regarding Normalization.i have created two tables as below
both custid and custname have same data types on both tables.
Query 1:
table1:
custId
custname
deliveryperson
delivarydetails
deliverydate
.
.
.
.
here custid and custname is composite primary key.
table 2:
custid
custname
custaddress
custphone
.
.
.
here also custid and custname is composite primary key.
i want to create a foreign key of custid and custname from table 1 to custid and custname of parent table(table2). both have unique values
i know we can create foreign key between these tables. is it comes under proper normalization.
Query 2:
table 3:
custid
custname
custaddress
custphone
.
.
here custid and custname is composite primary key.
table 4:
custid
deptid
deptname
deprtloc
.
.
so onnnnnnnn
here deptid is a primary key.
i want to create a foreign key of custid from table4 to parent table ie.table3 (custid). my doubt is custid is a composite primary key in table 3.
is it correct to map tables like these?
please excuse if i am wrong
Thanks
Rock..
October 7, 2009 at 6:58 am
Looks sort of hokey to me. In my opinion, if you are going to have a custid it should be a unique PK in and of itself. Having to drag the name along with it in a composite key and then propagate that across other tables as a foreign key is ugly and violates basic normalization principles. Besides... what if the name can change? (not an unheard of situation if a person gets a divorce or simply decides to change their name..).
In my years of experience most of the problems I have had to deal with were the unfortunate result of poor or improper database design.
The probability of survival is inversely proportional to the angle of arrival.
October 7, 2009 at 7:04 am
Definately not. The key is the key is the key. Whatever you have as the primary key in the parent table, that should be the foreign key in the related child table. Anything else is going to get into odd behavior.
How is the ClientId generated? It's not unique by itself, but needs the name to make it unique?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 7, 2009 at 8:17 am
Query 1:
I agree with your comments, But the custid is unique with custname (custid is unique values generated from application and custname may duplicate with other custid). some customers may have same name right. that's why i make them as composite primary key.
Can i make custid in table2 as primary key and create a composite unique key of custid and custname.then i can map this custid to table1.
is it correct? if not can you give me some idea for the proper normalization.
Thanks
Rock.
October 7, 2009 at 8:27 am
rockingadmin (10/7/2009)
Query 1:both the tables will have unique values. that's y i make them as primary key.
can i make custid in table2 as primary key and create a composite unique key of custid and custname.
then i can map this custid to table1.
is it good or not?
When you say both tables, do you mean both values?
If CustId and CustName are both unique, why do you want to make them into a compound key for the PK or a unique index?
But to answer your question, yes, you can do this. I'm still not sure why you want to, but it will function.
[/quote]
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 7, 2009 at 8:29 am
rockingadmin (10/7/2009)
Query 1:both the tables will have unique values. that's y i make them as primary key.
can i make custid in table2 as primary key and create a composite unique key of custid and custname.
then i can map this custid to table1.
is it good or not?
Yes, if you make custid your PK then you can set up the FK relationship to custid in table2... that would make sense. The separate unique key on custname is fine (if there can never be two people with the same same in your database) however, including custid along with custname in a composite unique key serves no purpose since it is a PK and will therefore always be unique.
The probability of survival is inversely proportional to the angle of arrival.
October 7, 2009 at 8:59 am
I agree with your valuable comments for Query 1.
I have a scenario like
table 1: custid
levelid
levelname
fromlength
tolength
.
In table 1, i have to define the length for the levels to that customer.so levelid and levelname must be unique for that custid.so here composite pk comes into picture.
table 2: custid
levelid
levelname
levelvalues
level description
.
In this table2, same as above, here we are defining the level values for that custid,levelid,levelname.
table 3: custid (as you said i make custid as primary key)
custname
custaddress
custphone
.
then how can i map custid from table1 and table 2 to table 3.
please suggest me
Thank you very much for the support
Regards
Rock..
October 7, 2009 at 9:11 am
is there is a one to many relationship between custid -----> levelid ? In other words, can a single customer have many levelid's associated with him? You haven't made that clear (at least to me.)
I think if you provided a bit more background information on the purpose and use of the data we could give you some better recommendations. Without understanding the problem it is difficult (if not impossible) to design a good solution.
The probability of survival is inversely proportional to the angle of arrival.
October 7, 2009 at 10:04 am
i apologize, please go through the below sample data for the tables.
table1:
Custid levelid levelname fromlength tolength
ABC 1 levelname1 0 3
ABC 2 levelname2 5 10
ABC 3 levelname3 11 20
BCD 1 levelname1 0 3
BCD 2 levelname2 4 7
.
.
.
table2:
Custid leveid levelname levelvalue leveldescription
ABC 1 levelname1 Newyork testdesc
ABC 1 levelname1 Texas testdesc
ABC 1 levelname1 Chicago testdesc
ABC 2 levelname2 streets in Newyork testdsc
ABC 2 levelname2 streets in Texas testdesc
...
..
..
BCD 1 levelname1 Usa testdesc
BCD 1 levelname1 Canada testdesc
BCD 2 levelname 2 Newyork cities in usa
BCD 2 levelname2 toronto city in canada
..
..
.
table 3:
Custid custname custaddress
ABC company name1 address of this company
BCD companyname2 address of this company
like that i have to store the data.
i think now you will be very clear.
please suggest me on this.
Thanks
Rock
October 7, 2009 at 11:04 am
rockingadmin (10/7/2009)
table1:
Custid levelid levelname fromlength tolength
ABC 1 levelname1 0 3
ABC 2 levelname2 5 10
ABC 3 levelname3 11 20
BCD 1 levelname1 0 3
BCD 2 levelname2 4 7
.
.
.
This table implies that each customer can have multiple "levels" associated with it, one to many.
rockingadmin (10/7/2009)
table2:Custid leveid levelname levelvalue leveldescription
ABC 1 levelname1 Newyork testdesc
ABC 1 levelname1 Texas testdesc
ABC 1 levelname1 Chicago testdesc
ABC 2 levelname2 streets in Newyork testdsc
ABC 2 levelname2 streets in Texas testdesc
...
..
..
BCD 1 levelname1 Usa testdesc
BCD 1 levelname1 Canada testdesc
BCD 2 levelname 2 Newyork cities in usa
BCD 2 levelname2 toronto city in canada
..
..
.
This table says the same thing, that each customer can have multiple levelid's associated with it. The levelname appears to occur in both tables and would seem to indicate lack of normalization.
If levelid and levelname are globally unique and always associated with eachother, then a table containing just levelid (PK) and levelname should be used. levelname is then removed from table1 and table2 and just levelid is there to link back to levelid PK in the names table.
If the goal is to simply split level information (value, from length, to length and description) into two tables linked by levelid then at least eliminate the duplicate levelname column from one of the tables. Another possibility is to simply add the from length and to length columns to table 2 and eliminate table 1.
Hopefully I am understanding your data correctly.
The probability of survival is inversely proportional to the angle of arrival.
October 7, 2009 at 11:06 pm
levelid and levelname are not globally unique. because Custid will have levelid as 1 and levelname as newyork. and every customer will starts his levelid from 1 only (in this case levelid and levelname can not be globally unique and levelname may be duplicate with other customer levelname).
under custoid, levelid and levelname have to define.
hope you understand the scenario.
October 10, 2009 at 1:03 pm
A foreign key constraint has to reference a key constraint of the parent table. Since your key is a composite key the foreign key would have to be composite as well.
ALTER TABLE table2 ADD CONSTRAINT fk1
FOREIGN KEY (custId,custname) REFERENCES Table1 (custId,custname);
As far as normalization is concerned there's nothing wrong with composite keys in principle. Hope that answers your questions.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply