Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 29 Nov 2002 21:23:13 -0600
Message-ID: <ulm3b1sbt.fsf@hotpop.com>

On 27 Nov 2002, Mark.Powell_at_eds.com wrote:
> Galen Boyer <galenboyer_at_hotpop.com> wrote in message
> news:<uwumzph80.fsf_at_hotpop.com>...
>

>> On Tue, 26 Nov 2002, niall.litchfield_at_dial.pipex.com wrote:
>> > <ctcgag_at_hotmail.com> wrote in message The problem is that the
>> > assumptions often turn out to be incorrect.  It appears that US
>> > SSN's can have duplicated values. Equally I couldn't appear in such
>> > a database (not having an SSN). Pick an SSN as a natural key and
>> > you will eventually get duplicates or null values.
>> 
>> I've gone back and forth on this and I think this has finally made me
>> make up my mind.
>> 
>> If, I assume that I chose SSN as my primary key, what do I do when
>> the SSN has been duplicated?  Where is my schema design now.  If, on
>> the other hand, I defined a dummy PK, and an SSN column with a unigue
>> PK on it, then I could turn the unique index on the SSN into a
>> non-unique one.
>> 
>> At that point, I would have the same whirl-wind, Oh shit, what do we
>> do now issue no matter the PK vs non-PK argument, but my application
>> would still work.  I could figure the work-around, test it and
>> implement it.  With the duplicate SSN as my PK, I have actually have
>> to tell somebody that I can't service them as a customer, and then
>> try to fix things.
>> 
>> So, the internal dummy key seems to offer something the "supposedly
>> natural key" can't.  Protection from disastrous client interactions.

>
> Galen, I do not find your argument to be very convincing. If you have
> an artificial PK and along with a unique requirement on the SSN and
> the unique SSN suddenly becomes non-unique you are likely to have
> numerous places in the code where one and only one row is expected by
> the logic represented by your code plus the data related by the
> artificial key is in reality related to the business value, that is,
> the SSN. So now are you sure the related artificial key data is
> connected to the correct SSN information. While technically the
> artificial key should keep working in practical reality you are likely
> to have problems. In a case like this you have a design issue that is
> really independent of the method used to assign the PK.
>
> The US Social Security Administration has never said that SSN numbers
> are unique as it is the SSN and certain character positions of the
> name that make the number truly unique; however, it is policy to not
> re-issued the number to anyone until the original holder has been dead
> for a certain length of time so for practical matters the number
> should be unique. What you are most likely to run into is that in the
> case of foreign nationals who marry an American who then passes away
> is that the Social Security Administration uses the deceased
> person&#8217;s SSN plus a suffix. That means the nine-digit numeric
> is actually a ten digit alphanumeric where the tenth digit is almost
> always blank or null depending on how you want to model it. Most
> systems only expect nine-digit SSN and lose the suffix if the holder
> bothers to give it.
>
> In most all other cases you either have a bad number or fraud. Though
> to complicate matters I did read that a minor error was made and a few
> numbers (somewhere in range 15,000 &#8211; 30,000) were re-issued too
> soon.
>
> But this is a data definition issue and the point is if your business
> rules change your application may need to change no matter what method
> of assignment was used for creating the primary key as the artificial
> key has to be associated to the correct business data. In examples
> like this that association is subject to errors.

Mark,

I guess the SSN isn't all that great of an argument tool for the "natural" crowd? Do many people actually choose to use the SSN as a key? I would never do this because of the simple fact that there would be too many rules played out before the record got into the database. Is the "key" in a particular format?
Does it have only numbers?
Is there a 10th digit?
Is it really the correct SSN number?
...

I would rather make it so there is more than one way to identify the person on the end of the app, allowing for mistakes at each entry point, hoping they can at least remember one way in. If they do get one of those correct, then they get the information from the record with my manuafactured key.

Having the key to the table be something that the user is in charge of defining doesn't sit well with me.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.

Received on Fri Nov 29 2002 - 21:23:13 CST