Re: System (Hidden) Keys

From: David Roth <droth_at_dr.ultranet.com>
Date: 1995/06/02
Message-ID: <3qo9uk$u4k_at_caesar.ultra.net>#1/1


randyd_at_cais2.cais.com (Randy Dewoolfson) wrote:

>We are about to have a massive debate in the company I work with.
>The discussion will be about primary keys that are system generated,
>(like by a sequence) vs. a set of 'regular' fields that can be used
>as unique primary keys.
 

>What I'm looking for is basically a list of PROs and CONs about
>defining Primary keys in these two ways.

.
.
.

Real keys reduce the number of tables in many queries.

Artificial keys reduce the size of the primary key index. More keys per block, less blocks, less levels in tree --> faster retrieval.

If there is a real key that is meaningful and short use it. If not go to an artificial key.

As a rule of thumb (which I violate regularly) I us real keys with 1 or 2 columns but switch to artificial keys if I would need more than 3 columns to get uniqueness. (Yes I know I skipped 3)

Some popular authors recommend using artificial keys all the time. This is a cop out.
You need to understand your application, your users and your database and then make the decision on a table by table basis.

David J Roth
Carty Mailloux Consulting Received on Fri Jun 02 1995 - 00:00:00 CEST

Original text of this message