choosing primary key
-
jith - iii wrote:
Some where I read that SSN(social security number) can not be considered as primary key in complex applications. I also heard that if a person died after 50 years there is a possibility for the SSN being reassigned. Is it right?
SSNs are not reused. See http://www.ssa.gov/history/hfaq.html Q20: Are Social Security numbers reused after a person dies? A: No. We do not reassign a Social Security number (SSN) after the number holder's death. Even though we have issued over 415 million SSNs so far, and we assign about 5 and one-half million new numbers a year, the current numbering system will provide us with enough new numbers for several generations into the future with no changes in the numbering system. There have been instances of accidental duplication though, and some instances of numbers getting invalidated due to their use in advertising.
Regards, Nish
Nish’s thoughts on MFC, C++/CLI and .NET (my blog)
My latest book : C++/CLI in Action / Amazon.com linkOk.But I have read somewhere(could not get that article though I'm searching it for an hour) that in some cases like a missing identity or something like that the SSN may be reused. Like we may see more than one person with the same SSN in a 100 year period,if other guy had expired or missing long before.
-
Some where I read that SSN(social security number) can not be considered as primary key in complex applications. I also heard that if a person died now,after say 50 years there is a possibility for the SSN being reassigned. Is it right?
-
A primary key should never represent any real value like a SSN. Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith -
Vikram A Punathambekar wrote:
So you fall into the "PKs should only be GUIDs" camp?
Absolutely. :) Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith -
jith - iii wrote:
Ok..but can it be used as a unique key.
Ah, for indexing? That I don't know. Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith -
I've been doing db related business apps for...well forever and I've come to learn that it's a wise person who uses Guid's as primary keys. Besides all the uniqueness reasons and ease of combining databases etc it's a huge performance benefit because you can generate them at the client and do single trips to the db server for just about any operation.
"110%" - it's the new 70%
-
Ok.But I have read somewhere(could not get that article though I'm searching it for an hour) that in some cases like a missing identity or something like that the SSN may be reused. Like we may see more than one person with the same SSN in a 100 year period,if other guy had expired or missing long before.
jith - iii wrote:
Ok.But I have read somewhere(could not get that article though I'm searching it for an hour) that in some cases like a missing identity or something like that the SSN may be reused. Like we may see more than one person with the same SSN in a 100 year period,if other guy had expired or missing long before.
That article was incorrect then. The site I linked to is the official govt website.
Regards, Nish
Nish’s thoughts on MFC, C++/CLI and .NET (my blog)
My latest book : C++/CLI in Action / Amazon.com link -
Vikram A Punathambekar wrote:
"PKs should only be GUIDs"
Yes.
Regards, Satips.
-
Some where I read that SSN(social security number) can not be considered as primary key in complex applications. I also heard that if a person died now,after say 50 years there is a possibility for the SSN being reassigned. Is it right?
-
jith - iii wrote:
Ok..but can it be used as a unique key.
Ah, for indexing? That I don't know. Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh Smith -
Some where I read that SSN(social security number) can not be considered as primary key in complex applications. I also heard that if a person died now,after say 50 years there is a possibility for the SSN being reassigned. Is it right?
Aside from the duplication issue, it would probably be a poor choice of primary key for a couple of other reasons. First, A person could decline to give you their SSN because of privacy or fraud concerns. If this were the case, you would have to generate a key value that does not duplicate any of the possible SSNs. The process for doing so would almost certainly be more complex than just generating a unique key up front. Second, by using SSN as a primary key, you introduce privacy requirements into your schema and processing. You would have to make sure that some output does not accidently expose the SSN to unauthorized recipients either explicitly or accidently, e.g. as part of some network communication. I would think that using SSN as a primary key would be more trouble than it is worth.
-
A primary key should never represent any real value like a SSN. Marc
People are just notoriously impossible. --DavidCrow
There's NO excuse for not commenting your code. -- John Simmons / outlaw programmer
People who say that they will refactor their code later to make it "good" don't understand refactoring, nor the art and craft of programming. -- Josh SmithIt was not that long ago, database purists said keys should "only" be real values and not just numbers or other forms of non-revelant data :)
Rocky <>< Latest Code Blog Post: SilverlightCity blog running! Latest Tech Blog Post: 15 Free utilites!
-
I've been doing db related business apps for...well forever and I've come to learn that it's a wise person who uses Guid's as primary keys. Besides all the uniqueness reasons and ease of combining databases etc it's a huge performance benefit because you can generate them at the client and do single trips to the db server for just about any operation.
"110%" - it's the new 70%
Not to mention testing scenarios. I have had so many where I need to generate multitudes of new numbers, and with GUID's I really felt the lack of ID 'collisions' tangibly.
-
Not only for indexing.I mean if I have a column SSN which is not the primary key and I use GUID(Though no one here dare to follow it) as primary key,would that be a wrong decision if I set unique constraint for SSN.
I would not have a problem. We use an Identity Number in South Africa. It's name describes it's very purpose, and I have often encountered these or used them as a unique constraint.
-
Aside from the duplication issue, it would probably be a poor choice of primary key for a couple of other reasons. First, A person could decline to give you their SSN because of privacy or fraud concerns. If this were the case, you would have to generate a key value that does not duplicate any of the possible SSNs. The process for doing so would almost certainly be more complex than just generating a unique key up front. Second, by using SSN as a primary key, you introduce privacy requirements into your schema and processing. You would have to make sure that some output does not accidently expose the SSN to unauthorized recipients either explicitly or accidently, e.g. as part of some network communication. I would think that using SSN as a primary key would be more trouble than it is worth.
Another issue may be storing data on non-US citizens. You would probably have to use a passport number as a substitute.