tag:blogger.com,1999:blog-6393051114813114443.post6898648863633286930..comments2024-03-27T14:52:51.318-05:00Comments on Lee's Blog: Surrogate vs Natural Primary Keys – Data Modeling Mistake 2 of 10Lee Richardsonhttp://www.blogger.com/profile/01314803491511307042noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-6393051114813114443.post-83827300694712131362013-09-10T10:43:08.674-05:002013-09-10T10:43:08.674-05:00Roy Hann wrote a pretty good set of articles that ...Roy Hann wrote a pretty good set of articles that argue *against* surrogate keys. Here is the first one: <a href="http://community.actian.com/forum/blogs/rhann/124-surrogate-keys-part-1-best-practice-wrong-headed-dogma.html" rel="nofollow">http://community.actian.com/forum/blogs/rhann/124-surrogate-keys-part-1-best-practice-wrong-headed-dogma.html</a><br /><br />I used surrogate keys a lot in the past, but with time I started using natural keys more and more because simply I like being able to **understand** data in the database, not just seeing numbers...Dejan Lekichttp://dejan.lekic.orgnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-57167823218320068702013-09-09T19:47:24.694-05:002013-09-09T19:47:24.694-05:00Stumbled on this article, and glad I did, very int...Stumbled on this article, and glad I did, very interesting discussion!<br /><br />I learned a lot, thanks! This post here is good too on natural keys:<br /><br />http://www.programmerinterview.com/index.php/database-sql/natural-key-in-database/Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-51087228676092403392012-12-07T10:32:16.298-05:002012-12-07T10:32:16.298-05:00Sorry sir, if you came up with a surrogate key the...Sorry sir, if you came up with a surrogate key the answer to your dilema, it´s only because you either failed to model your real world entity, or just wanted to make things easier for you by throwing away a couple of good design practices. <br />On your side, you should also take into consideration that surrogate keys migth even be a real world element that you are failing to identify.<br />In the long run, nothing but good design practices can make your application sustainable.<br />Respectfully,SBhttps://www.blogger.com/profile/03653121374769879434noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-64160239094668230712012-01-25T10:15:17.608-05:002012-01-25T10:15:17.608-05:00Surrogate keys work well in this type of situation...Surrogate keys work well in this type of situation where a natural key is ambiguous, at best. However, when the natural key is concrete and static, using natural keys is the way to go. For example in the case of a vehicle, each vehicle has a VIN number, houses have addresses, or lot numbers assigned them via the county.<br /><br />I guess keeping to one dogma or the other is just like a belief system where you can get rigidly stuck in one train of thought, unable to see benefits of the other and the shortcomings of yours.<br /><br />What this comes down to is the data model that you are currently designing; we are all professionals and should be able to decide when to use which design scheme!nobodyhttps://www.blogger.com/profile/01727384263510143626noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-60246823205804966022010-04-07T10:54:05.851-05:002010-04-07T10:54:05.851-05:00Great post. I am currently in the process of tryin...Great post. I am currently in the process of trying to persuade many people that surrogates are better than naturals. They present the *exact* same arguments to me that you mention here.<br /><br />Another nasty that I've come across when end users get their grubby paws on a table with a meaningful lookup FK (by this I mean a child table with a column referring to a parent lookup table with a natural PK) is that since they 'do not need' to look at the parent lookup table (since the key is meaningful) they will quite happily perform a "select distinct " from the child table to get all possible variants of that column.<br /><br />Horrible horrible.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-66555562952572567412009-09-23T13:23:36.967-05:002009-09-23T13:23:36.967-05:00I got issued the same SSN as someone else and was ...I got issued the same SSN as someone else and was required to change it (12 years later =)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-28197995184347726312009-07-08T04:46:20.920-05:002009-07-08T04:46:20.920-05:00Just use both for god's sake. There are places...Just use both for god's sake. There are places in databases where Composite keys are much much better. Others were using a surrogate is far superior.<br /><br />Is the use of one over the other exclusive? I think you'll find it isn't.<br /><br />Member or people details should be surrogate. ID's, auto generated. I agree. It would be stupid otherwise. But using a Surrogate on a forum relationship would be stupid.<br /><br />New post would be a surrogate, person who read such a post would use a composite between post_id and member_id. A surrogate in that situation would be wasteful and stupid.Luke Knellernoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-53686055968949475412009-01-22T07:13:00.000-05:002009-01-22T07:13:00.000-05:00nice postnice postAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-46895021736675761202007-09-04T08:34:00.000-05:002007-09-04T08:34:00.000-05:00Surrogate/ Natural Keys is an argument that will n...Surrogate/ Natural Keys is an argument that will never die. In fact, I would go as far to say that the 2 camps are as strident as a belief system. Or, to use another word: A religion.<BR/><BR/>Just as it is extremely rare* for a fervent believer in one religion to convert to another so it is almost impossible to convince a natural key Data Modeller to use surrogate keys or vice versa.<BR/>*Note: I state it is not impossible, just rare. <BR/>Note 2: I don't mean non-believers picking a non-standard religion either: I am talking hard core christian becoming muslim or similar<BR/><BR/>Personally I am a natural key guy. Saying that, and this is where your example loses credibility, practical experience has shown that for a person there is <B>no</B> natural key, so I create a surrogate. However, if I am creating an entity that has a true unique identifier, that doesn't change and meets all the other requirements then I will use it.<BR/><BR/>Now, many of those unique identifiers are in fact not 'natural' because they are artificially generated e.g. a legal Document ID but they are generated <I>outside my system</I> and as such I will not create an additional surrogate inside. This means that my PK may be a different data type in different entities (string vs number) but that is a price I am willing to pay<BR/><BR/>So, I consider myself to be a natural key modeller, but I will use a surrogate key where practical. <BR/><BR/>But then I don't believe in God either<BR/><BR/>Ian<BR/>maguffyn@yahoo.co.ukAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-24537390577794070252007-08-30T11:47:00.000-05:002007-08-30T11:47:00.000-05:00Getting the next value of a surrogate key really s...Getting the next value of a surrogate key really should not be considered a disadvantage in my opinion. It is simple enough to provide the SCOPE_IDENTITY() as an output parameter of the stored procedure performing the insert(s).<BR/><BR/>If getting the next key value is that much of a stumbling block for a developer who *must* set the key client-side, fine...then you just use GUIDs as the surrogate key.Unknownhttps://www.blogger.com/profile/04390949880830454310noreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-29621643455738104152007-08-23T16:33:00.000-05:002007-08-23T16:33:00.000-05:00To the previous about SHA1. Isn't that what the U...To the previous about SHA1. Isn't that what the Unique Identifier/GUIDs are for?<BR/><BR/>There is one area that ALL DBAs need to value highly (And few do) with software development (The primary consumer of all DBs) and that's change. Specifically ease of change.<BR/><BR/>My experience (We rarely have a single application not change dramatically atleast every couple of months) has been that the fewer primary keys in a table the easier to refactor the data-model as requirements change.<BR/><BR/>We've found that just using one column for a primary key per any given table meets the needs to maintain integrity while allowing change and migration to be far less painful in the long run. Even if the index is completely arbitrary and has nothing to do with the actual data.<BR/><BR/>Even the most complex of Data-migration/normalization programs only require a few thousand lines of code to create, but if the DB is enforcing relationships so tightly a Nun would be envious, say good bye to easy change.<BR/><BR/>I also can't stress enough how many times I've been bit in the butt by the assumption that a particular chunk of data will always be unique just to have a change request come along later to allow duplicate entries for that same chunk of data.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-80774693200656058482007-08-23T13:18:00.000-05:002007-08-23T13:18:00.000-05:00"if you use surrogate keys today it will definitel..."if you use surrogate keys today it will definitely make your life easier in the long run."<BR/><BR/>But not if you use sequence numbers generated by the database server. <BR/><BR/>Because in the long run all databases and their applications are expected to be distributed on the network.<BR/><BR/>And sequence numbers used as unique resource identifiers (surrogate primary keys) cannot practically be distributed in a network, locking your applications in the single database server model ... and its implementation.<BR/><BR/>One way to have best of both world is to use a standard hash function (SHA1 for instance) and let your network applications generate surrogate primary keys, not in the database server.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-56788123326683662242007-08-21T16:35:00.000-05:002007-08-21T16:35:00.000-05:00Not to mention that in some circumstances, a perso...Not to mention that in some circumstances, a person can change his or her SSN number. For example, if it's been used fraudulently.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6393051114813114443.post-45996496532460334932007-08-21T08:46:00.000-05:002007-08-21T08:46:00.000-05:00I put a lot of time into this response. Unfortunat...I put a lot of time into <A HREF="http://www.nearinfinity.com/blogs/page/lrichard?entry=surrogate_keys_data_modeling_mistake#comment1" REL="nofollow">this response</A>. Unfortunately it went late into the night and a few details are off. Hopefully it will still be of some use.<BR/><BR/>SethAnonymousnoreply@blogger.com