Re: Normalization, Natural Keys, Surrogate Keys

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 17 May 2002 08:54:13 -0500
Message-ID: <u8z6ilw51.fsf@grossprofit.com>

On Fri, 17 May 2002, comedyharris_at_hotmail.com wrote:

>> A natural unique index should suffice.  Then a generated key
>> makes for a much easier to understand and code to model, (in
>> my opinion).  Restrict on the natural fields to restrict on
>> but join on the generated keys.  To me, this just seems a
>> whole lot easier to understand and code to, which makes for
>> more successful projects.

>
> Would there be anything wrong with doing this the other way
> around? Making the primary key the natural key, but then
> creating a surrogate key, which is an auto-number column with a
> unique constraint on it? This way, you keep the model
> 'correct', but provide development ease with your candidate key
> column.

Hm... I think everybody joins tables by looking at the keys. This would seem to confuse the issue to me. Also, you lose that the database would maintain the key relationships once you've created the surrogate keys. Once those keys get there, surrogate or natural, well, they better stay.

I know I would never use this manuafactured key because I wouldn't be able to depend on it. Is it a child to some other table? No. It is just some number. I don't think it would ever get used.

You sound like you really want to go with natural keys. Go with it. It certainly is a sound design.

-- 
Galen Boyer

Received on Fri May 17 2002 - 08:54:13 CDT