Re: PRIMARY KEYS - Mailing list pgsql-general

This message of Scotts and Mike Mascari's message clarifies my statement about
natural keys better than I could.  IME I design databases to reflect
the data in them using relational theory with a dab of object modeling.
I am as much influenced by Rambaugh (Object-Oriented Modeling & Design,
Rumbaugh, Blaha, et al) as Date and intuition.

Design by theory, then tune for practicality when doing the implementation.
SQL is not the only query language nor is PostgreSQL's implementation
of it the only one.  But I must say that PostgreSQL's UPDATE CASCADE
capabilities are pretty nice when you have natural keys.

elein

On Thursday 22 May 2003 09:56, scott.marlowe wrote:
> 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
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

--
=============================================================
elein@varlena.com     Database Consulting     www.varlena.com
PostgreSQL General Bits    http:/www.varlena.com/GeneralBits/
   "Free your mind the rest will follow" -- en vogue