Re: [GENERAL] using ID as a key

On Fri, 4 Feb 2000 davidb(at)vectormath(dot)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(at)eskimo(dot)com