Thread: 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@eskimo.com>
To: davidb@vectormath.com <davidb@vectormath.com>
Cc: Postgres Group <pgsql-general@postgreSQL.org>
Date: Friday, February 04, 2000 5:01 PM
Subject: Re: [GENERAL] using ID as a key


>On Fri, 4 Feb 2000 davidb@vectormath.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@eskimo.com
>
>
>************
>

On Fri, 4 Feb 2000 davidb@vectormath.com wrote:

> 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
>

David,

Both reasons you mentioned make sense, and I am almost convinced to change
my design.. I say almost because I will need to look at it again to make
sure that modifying the key structure won't hamper selects
(there may be times we need to perform lookups on only one half of the
compound key, e.g. if key = col1+col2, we might need to lookup on col1 only -
for our particular values this could prove troublesome to do with the like
operator)

Anyway, thanks for your answer.


Simon.



--
 "Linux - open doors, not windows."

   Simon Drabble                      It's like karma for your brain.
   simon@eskimo.com

Simon Drabble wrote:

I agree with everything Simon says (hmmm, there's a game in that) and
would add:

It's very likely that at some time you will change your database design
such that you will want to add or remove a field or two that uniquely
identify the record (hmmm, person's first and last name not enough
anymore, need to add social security number...).  Now, all those joins
(and foreign keys!) need to be changed to reflect the additional field.
Yes, I have been bitten by that.

The other way (unique ID number), nothing in your queries changes.
>
> On Fri, 4 Feb 2000 davidb@vectormath.com wrote:
>
> > 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
> >
>
> David,
>
> Both reasons you mentioned make sense, and I am almost convinced to change
> my design.. I say almost because I will need to look at it again to make
> sure that modifying the key structure won't hamper selects
> (there may be times we need to perform lookups on only one half of the
> compound key, e.g. if key = col1+col2, we might need to lookup on col1 only -
> for our particular values this could prove troublesome to do with the like
> operator)
>
> Anyway, thanks for your answer.
>
> Simon.
>
> --
>  "Linux - open doors, not windows."
>
>    Simon Drabble                      It's like karma for your brain.
>    simon@eskimo.com
>
> ************

--
Microsoft forced OEMs to ignore consumer demand for a browserless
version of Windows.
  -- Judge Thomas Penfield Jackson

Your most unhappy customers are your greatest source of learning.
  -- Bill Gates