| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Normalization, Natural Keys, Surrogate Keys
In article <3CE952BE.A90AEA10_at_exesolutions.com>,
dmorgan_at_exesolutions.com says...
> I keep hearing that Social Security Numbers are not unique but have
> yet to come upon a situation where that was true.
Now that you have made such a statement in public, Murphy's Law will come along and prove my point for me. :)
Maybe it's just my luck, but I learned early on that as soon as someone says something like: "Yeah, it's not truly unique, but it's good enough for us." in a database implementation is the moment just before a duplicate is entered... :)
> If you do not, if some system, somehow, assigns different surrogate
> keys, you are going to have a huge mess with legal implications
> somewhere down the line.
Well, the concept was the usefulness of surrogates to replace complex, natural keys and the example chosen was SSNs used as PKs instead of another common bad choice of last name/DOB. The example was given to be used generally, and not for getting into the politics and pros-and-cons of SSNs in particular.
However, I'll allow myself the diversion to bring up three counterpoints:
First, do you know which of the two (ex: the new applicant and the ten-year employee) is the legal holder of that erroneous SSN? Why should the database be the draconic arbiter, rather than a human policy officer? The best thing would be to be able to have both entered and flagged for administrative attention giving you flexibility and legal protection.
Secondly, legal implication also exist if you use the SSN as a basis to deny hiring or fire someone. From http://www.ssa.gov/: "Any employer that uses the information SSA provides regarding name/SSN verification to justify taking adverse action against an employee may violate state or federal law and be subject to legal consequences." Is a duplicate SSN immediate cause for suspicion that should affect hiring or employement?
Thirdly, and given the SSA doesn't even trust their own numbers, do you want to prevent yourself from hiring that much-needed employee because your database design is too tight and he/she's going to have to do whatever he/she needs to with the Social Security Administration rather than use your own surrogate key in your design? Along with my first point, I'd say that policy makers should dictate the data model, not the other way around.
Personally, I don't use SSNs as PKs. I've heard too many horror stories.
> Personally; I'd rather have the system catch that one up front and
> with a blaring of trumpets and beating of drums.
IMO, that's a policy-level decision that has no bearing on the fact that surrogate keys that replace complex, natural keys can be a good idea at times, depending on the model.
(Any opinions expressed are strictly mine only and not my employer's)
 |
 |