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/
