Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Normalization, Natural Keys, Surrogate Keys

Re: Normalization, Natural Keys, Surrogate Keys

From: Bernard Peek <bap_at_shrdlu.com>
Date: Tue, 28 May 2002 17:45:15 +0100
Message-ID: <9VvwdnybQ788Ewfp@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.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti
Received on Tue May 28 2002 - 11:45:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US