Re: Normalization, Natural Keys, Surrogate Keys
Date: 30 May 2002 09:38:51 -0700
Message-ID: <2cf20de8.0205300838.745a1c4a_at_posting.google.com>
Bernard Peek <bap_at_shrdlu.com> wrote in message news:<9VvwdnybQ788Ewfp_at_shrdlu.com>...
> In message <2cf20de8.0205260901.19582f0a_at_posting.google.com
> >, Costin Cozianu <c_cozianu_at_hotmail.com> writes
>
>
> >For example, in the above, because you allow for that 999999999 for
> >situations that are very rare (let's say <1% or <5% of the cases), you
> >fail to protect the vast majority of cases, because you won't be able
> >to declarer the UNIQUE constraint on the SSN. There are better way to
> >deal with this, one example that I suggested is create an
> >SSN_Exceptions table where you put the data collected for the persons
> >who don't have a SSN or their SSN has problems.
>
> There's an alternative solution, add an extra integer field to the main
> table with a default value of 0. Make the SSN and the new field a
> composite key. When you have an unknown or invalid (or duplicate) SSN
> insert a sequential number into the integer field. Any record with a
> non-zero value should be considered an ongoing problem.
>
> If you want you can create a view onto that table that will perform the
> same functions as the SSN_Exceptions table.
This is a bad solution.
But the really bad thing about this design is that it allows duplicate SSN to begin with, and it might allow creation and propagation of other data related to the bogus SSN through the RowID. For example you can't declare in a related Accounts table that the Person_RowID foreign key references strictly
The goal is not to create an alternate key, but to enforce data quality. That's why natural keys are critical, because they are the only one that enforce the data quality.
Of course, it all depends on the actual requirements of the actual practical problem, but for example in a banking system the situation is simple: either your SSN is alright or we don't do business with you. I hope the DMV and other critical state agencies have the same policy: fix your problems with Social Security Administration first, get a valid identification and then we'll do bsuiness with you.
Why bother to input you in the system with a bogus SSN only to fix it later ? I can put your data in an exception table to save you time later, but that's as far as one should go. Received on Thu May 30 2002 - 18:38:51 CEST