Re: Normalization, Natural Keys, Surrogate Keys

To follow up a little more. In any healthcare situation - if you decide to use SSN's, how are you going to handle newborns? Need to get them in your system - but can't because they don't have an SSN yet - probably won't have one for at least six months.

Your users will assign a dummy SSN - 000-00-0001, but because you can't have duplicates, you can only track a single newborn, or you are going to have a mess of 'dummy' SSN's that somebody has to track and clean up.

"Paul Tiseo" <123tiseo.paul_at_123mayo.edu (drop the numbers)> wrote in message news:MPG.17541dc82616d06b989711_at_news.easynews.com...
> 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)
> --------------------------------------------------------------------
> Paul Tiseo, Intermediate Systems Programmer
> Reply To: 123tiseo.paul_at_123mayo.edu (drop the numbers)
Received on Sun May 26 2002 - 11:29:33 CDT