Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1415161718»»

Do You Really Need Surrogate Keys? Part 1 – Concepts and Methods Expand / Collapse
Author
Message
Posted Thursday, November 4, 2010 8:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, November 27, 2010 10:41 PM
Points: 58, Visits: 113
ta.bu.shi.da.yu (11/4/2010)
I am really curious to know why it's a good idea to not enforce the immutability of the key!
This has already been covered in excruciating detail. First of all, a surrogate reduces the likelihood of an update, but it doesn't eliminate it entirely. More importantly, immutability is a desirable quality of key, but its not a requirement of a key. There are other desirable qualities that natural keys have (in some cases), and surrogates do not (again, in some cases). Neither is best in any and all cases.

No, you are an anonymous poster who claims that he knows a great deal about databases, and not only that make claims about normal forms that aren't true.
Such as what? Your little myth that "no normal form affects more than one table" has already been roundly debunked.
Post #1015899
Posted Thursday, November 4, 2010 8:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 10, 2011 11:42 AM
Points: 25, Visits: 37
David Portas (11/4/2010)
Michael Wang (11/4/2010)
Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place.

A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world.


No, this is not a helpful distinction in any way.
....


Either you see it or you don't: the distinction is to separate your model world from the real world. It's where the real line is drawn.



Post #1015901
Posted Thursday, November 4, 2010 8:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
David Portas (11/4/2010)

The phrase "unique key" is obviously a tautology and doesn't explain anything useful. Key = Candidate Key = Primary Key. There is no difference.


Hi David, I'm a bit confused by what you mean here! Are you referring to a key that is a candidate key that is a primary key?


Random Technical Stuff
Post #1015903
Posted Thursday, November 4, 2010 8:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Michael Ebaya (11/4/2010)
ta.bu.shi.da.yu (11/4/2010)
I am really curious to know why it's a good idea to not enforce the immutability of the key!
This has already been covered in excruciating detail. First of all, a surrogate reduces the likelihood of an update, but it doesn't eliminate it entirely. More importantly, immutability is a desirable quality of key, but its not a requirement of a key. There are other desirable qualities that natural keys have (in some cases), and surrogates do not (again, in some cases). Neither is best in any and all cases.

No, you are an anonymous poster who claims that he knows a great deal about databases, and not only that make claims about normal forms that aren't true.
Such as what? Your little myth that "no normal form affects more than one table" has already been roundly debunked.


Actually, it hasn't. As has been pointed out to you a number of times, the formal definition of the normal forms relates to the tables themselves. You seem to be confused with how the normal forms work.

Note that I'm not saying that what you say is a bad idea - if you have two tables that do the same thing, then that is quite silly and you should of course rationalize the table design.


Random Technical Stuff
Post #1015908
Posted Thursday, November 4, 2010 8:27 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:32 AM
Points: 451, Visits: 3,470
Michael Wang (11/4/2010)
David Portas (11/4/2010)
Michael Wang (11/4/2010)
Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place.

A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world.


No, this is not a helpful distinction in any way.
....


Either you see it or you don't: the distinction is to separate your model world from the real world. It's where the real line is drawn.


Sure but in the words of Chris Date it's a "purely psychological" distinction, calling a key "primary" being just an aide-mémoire or a convenient label. It's only as important as you want it to be. What I objected to was your implication that this was somehow a definition of a primary key - it obviously isn't because the name does not define any special type of key.


ta.bu.shi.da.yu (11/4/2010)
Hi David, I'm a bit confused by what you mean here! Are you referring to a key that is a candidate key that is a primary key?


I'm referring to the fact that a primary key is just any one candidate key. All candidate keys are equal in the relational model and in SQL so the "primary" key doesn't imply anything fundamentally different from any other key.


David
Post #1015916
Posted Thursday, November 4, 2010 8:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 10, 2011 11:42 AM
Points: 25, Visits: 37
David Portas (11/4/2010)
Michael Wang (11/4/2010)
David Portas (11/4/2010)
Michael Wang (11/4/2010)
Two problems keep surfacing from this thread: confusion between a primary key and unique key constraints, and more fundamentally why the two are required in the first place.

A primary key is required to identify a data item - it's purely physical and has nothing to do with the real world but everything to do with the data storage. This is where the physical model is insulated from the real world.


No, this is not a helpful distinction in any way.
....


Either you see it or you don't: the distinction is to separate your model world from the real world. It's where the real line is drawn.


Sure but in the words of Chris Date it's a "purely psychological" distinction, calling a key "primary" being just an aide-mémoire or a convenient label. It's only as important as you want it to be. What I objected to was your implication that this was somehow a definition of a primary key - it obviously isn't because the name does not define any special type of key.

OK, I accept your criticism: by primary key I meant a surrogate key and by unique key I meant natural unique key.



Post #1015920
Posted Thursday, November 4, 2010 8:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, November 27, 2010 10:41 PM
Points: 58, Visits: 113
ta.bu.shi.da.yu (11/4/2010)
[quote]the formal definition of the normal forms relates to the tables themselves. You seem to be confused with how the normal forms work.

Note that I'm not saying that what you say is a bad idea - if you have two tables that do the same thing, then that is quite silly and you should of course rationalize the table design.
Good god, did they stop teaching data modelling entirely in college? Or have people just stopped going in the first place?

I've already not only explained why this is false, I even gave examples of normalization that required modifications to multiple tables simultaneously. I suggest you read them....or just read a basic primer on normalization.
Post #1015922
Posted Thursday, November 4, 2010 8:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Michael Ebaya (11/4/2010)
ta.bu.shi.da.yu (11/4/2010)
I am really curious to know why it's a good idea to not enforce the immutability of the key!
This has already been covered in excruciating detail. First of all, a surrogate reduces the likelihood of an update, but it doesn't eliminate it entirely. More importantly, immutability is a desirable quality of key, but its not a requirement of a key. There are other desirable qualities that natural keys have (in some cases), and surrogates do not (again, in some cases). Neither is best in any and all cases.


Still trying to see what desirable qualities that natural keys have. Could you point out some examples?


Random Technical Stuff
Post #1015924
Posted Thursday, November 4, 2010 8:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Michael Ebaya (11/4/2010)
ta.bu.shi.da.yu (11/4/2010)
[quote]the formal definition of the normal forms relates to the tables themselves. You seem to be confused with how the normal forms work.

Note that I'm not saying that what you say is a bad idea - if you have two tables that do the same thing, then that is quite silly and you should of course rationalize the table design.
Good god, did they stop teaching data modelling entirely in college? Or have people just stopped going in the first place?


Which particular normal form do you believe helps you rationalize particular tables? I'm seriously interested in which one you refer to. Please, just either answer:

a. 1st normal form,
b. 2nd normal form,
c. 3rd normal form

I won't go into further normal forms, unless you want to, that is.

Also, which basic primer on normalization would you recommend? I'm curious which one supports your idea.


Random Technical Stuff
Post #1015926
Posted Thursday, November 4, 2010 8:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:32 AM
Points: 451, Visits: 3,470
ta.bu.shi.da.yu (11/4/2010)
Still trying to see what desirable qualities that natural keys have. Could you point out some examples?


A natural key enforces a data integrity rule that stops some meaningful data being duplciated. A surrogate key does not. I already discussed the example of a unique login name where it's desirable to have a key that is unique but changing. Not really sure what other example you need.


David
Post #1015942
« Prev Topic | Next Topic »

Add to briefcase «««1415161718»»

Permissions Expand / Collapse