Re: [GENERAL] using ID as a key

Hi Simon,

I have a practical reason and a theoretical reason.

Practical reason - As an application develops over time, the database
structure grows in unpredictable ways. My experience has been that this
process begins during design. So suppose you have a table that is a
mapping table between two other tables (the most tempting place to use a
complex key). Later, it turns out that this mapping is something that needs
to
be referenced by several other tables. Now, every query that you write that
joins this mapping table to its subordinate tables needs to reference both
parts of the primary key. This problem has two facets: First, it is
annoying to
have to add the additional portion of the join to every query. Second, I
guarantee that at some point one of your programmers will forget to add one
of the parts of the join to one of his queries (this is VERY BAD, because
this
particular programming error is difficult to detect before the application
goes
into production, and it's a nuisance to track down once it is detected).

Theoretical reason - Just as every variable in an application ought to serve
one and only one purpose, every field in a database ought to serve one and
only one purpose. And keeping track of records is a purpose in itself.
Now,
any time you have a complex key, the columns of the complex key have a
meaning outside of keeping track of records. A change in that other meaning
can affect that column's ability to serve its function as part of a record
identifier.

So, for goodness sake, just add the extra column.

David Boerwinkle

-----Original Message-----
From: Simon Drabble <simon(at)eskimo(dot)com>
To: davidb(at)vectormath(dot)com <davidb(at)vectormath(dot)com>
Cc: Postgres Group <pgsql-general(at)postgreSQL(dot)org>
Date: Friday, February 04, 2000 5:01 PM
Subject: Re: [GENERAL] using ID as a key

>On Fri, 4 Feb 2000 davidb(at)vectormath(dot)com wrote:
>
>>
>> Finally, since you proclaim that you are new to database design, I cannot
>> pass up this opportunity strike out against the forces of evil. Unless
you
>> ABSOLUTELY cannot avoid it, never use more than one field as your primary
>> key. Oh, it seems like a good idea at design time, but it's not. If you
>> use
>> complex primary keys, you and your programmers will regret it for the
rest
>> of
>> the application's useful life.
>>
>>
>> David Boerwinkle
>>
>
>
>David,
>
>What makes this such a bad idea? I am just now implementing a db that uses
two
>fields as primary key, and I'd like to know what I'm letting myself and
>colleagues in for! I can only guess that it will slow down queries, but
that's
>not something a programmer should necessarily be concerned with..
>
>(Thankfully it is not too late to change the design if need be :) )
>
>Simon.
>
>
>--
> "Linux - open doors, not windows."
>
> Simon Drabble It's like karma for your brain.
> simon(at)eskimo(dot)com
>
>
>************
>