Re: PRIMARY KEYS

I think it has a lot to do with performance versus natural design.

While it may be natural to key records off of a primary key of first name,
last name, address, city, state, cip, it is much faster to key off of a
simple integer.

So, one school of thought would be to use a unique index on the stuff that
should be unique, but to have a serial column act as your foreign key.

The other would be to use a primary key as both a unique index AND a
foreign key.

Performance wise, the single int will usually win, especially if you
aren't returning data that is actually in the unique index.

I think both schools are valid, one provides a more natural feel to the
way the fks are referenced, but is slower, while the other uses an
artificial fk is less intuitive but faster.

On Thu, 22 May 2003, Erik Price wrote:

> This is none of my beeswax but I was just reading an excerpt from a book
> introducing relational database concepts and one of the points made was
> that it is a good practice to use a primary key that is devoid of any
> significance -- it should only be significant as a primary key. The
> reason for this is that when there is meaning to a column, then there is
> the possibility that the column may be altered in some way, so it is
> best to use a "pure" primary key column dedicated to that purpose.
>
>
>
> Erik
>
>
>
> elein wrote:
> > This is unlike any database theory I've heard of.
> > Choosing a natural key over an artificial key is
> > the ideal. I've heard that a lot.
> >
> > Sometimes there are several candidate keys to
> > choose from. And sometimes the primary keys
> > are more than one column.
> >
> > Sometimes I bail out to an artificial key when the
> > primary key is too long, but it depends very much on how
> > the table will be accessed and who knows what and
> > when.
> >
> > --elein
> >
> > On Tuesday 20 May 2003 05:41, Karsten Hilbert wrote:
> >
> >>And - if you agree with database theory - a bad one at that.
> >>Supposedly primary keys should be void of any meaning bar
> >>their primary key-ness. I got into the habit of starting
> >>any but the most simple table like this:
> >>
> >>create table (
> >> id serial primary key,
> >> ...
> >>
> >>Never had any trouble with that. Good or bad practice ? Gotta
> >>decide for yourself.
> >>
> >>Karsten
> >>--
> >>GPG key ID E4071346 @ wwwkeys.pgp.net
> >>E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 4: Don't 'kill -9' the postmaster
> >>
> >>
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>