Re: Surrogate VS natural keys

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.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.