June 13, 2008 at 3:22 am
I found an article entitled "Ten Common Database Design Mistakes[/url]" and there was a comment about using identity columns in foreign key references.
The "purist" design approach is to use IDENTITY columns as a primary key but don't allow foreign key references to it. Make all foreign key references to the "natural" key and use the identity column as a surrogate.
What are your thoughts about this issue? :w00t:
June 13, 2008 at 6:55 am
Purist anything in databases is usually a sign for disaster. There are places where an identity column as a PK and as FK's in all the related tables not only works, but works extremely well. There are other instances where it's stupid and will cause no end of problems. I prefer a situational approach. Although I have a bias towards identity columns, I'm not wed to them. If it makes more sense in a given situation based on business requirements, performance requirements or some other issue, I'll go all natural keys, but I would be able to justify the decision, not say that it's a "pure" design.
----------------------------------------------------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
June 13, 2008 at 7:00 am
Grant Fritchey (6/13/2008)
Purist anything in databases is usually a sign for disaster. There are places where an identity column as a PK and as FK's in all the related tables not only works, but works extremely well. There are other instances where it's stupid and will cause no end of problems. I prefer a situational approach. Although I have a bias towards identity columns, I'm not wed to them. If it makes more sense in a given situation based on business requirements, performance requirements or some other issue, I'll go all natural keys, but I would be able to justify the decision, not say that it's a "pure" design.
Cool, great answer, I guess I should add a 3rd "Depends on the the situation" option. Thanks.
July 16, 2008 at 5:46 am
Yes, you can use it, but some cases it may raise issue in future..
I can say, Its purely depends on the application requirements..
Cheers!
Sandy.
--
July 16, 2008 at 6:18 am
Natural keys have one big disadvantage... they can change.
If your natural key to identify a person is the National Insurance No (UK equivalent of the Social Security Number in the US), and you base any foreign keys on this, what happens when you realise later on that the NI No for a person is incorrect... you have an awful lot of places to change this.
Unless the Natural key will never change, I would choose a surrogate key (nothing wrong with an Identity column) as the primary key and base all FK's on this, but also place a unique constraint on any natural keys.
July 16, 2008 at 7:57 am
And that, of course, is the absolute strongest argument for using nothing but artificial keys. But, even so, there are business definitions that can allow for the use of natural keys with a very low likelihood (notice, I didn't say impossible) chance of them changing that make more sense for the primary key of a given table. I'm just not convinced that there's an absolute you must always or you must never in either direction for natural or primary keys. Instead, you need to make intelligent and informed decisions that you can support over time.
----------------------------------------------------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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply