Re: Surrogate VS natural keys

Martijn van Oosterhout wrote:
> On Wed, Jun 20, 2007 at 08:39:23AM -0700, Rich Shepard wrote:
>> Also, the reason for a third, M-M, table is to relate multiple players and
>> multiple clubs. If you think of the logic involved, your third table has
>> only one row for each player-club combination. Therefore, each row is unique
>> by definition and a surrogate key adds no value.
>
> While true in this simple case, it can quickly become more complicated
> if your relationship starts gaining attributes. For example, if you add
> start and stop dates, so the (player,club) combination is not unique
> anymore. If you track invoices, games or scores it may be easier to
> reference the relatioship via a surrogate key rather than copying the
> other IDs around everywhere.
>
> For simple tables like this I generally don't bother, but sometimes I
> find myself adding a surrogate key later.

The value of a surrogate key is easy retrieval and really has nothing to
do with normalization or proper modeling.

I often add a surrogate key, even when one is not required just so I
don't have to worry about have a 4 element where clause.

Joshua D. Drake

>
> Have a nice day,

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/