Re: [GENERAL] using ID as a key - Mailing list pgsql-general

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