Re: Surrogate VS natural keys

Naz Gassiep wrote:
> OK so which is the "correct" way to do it?
>
> E.g., Say I have a table with users, and a table with clubs, and a table
> that links them. Each user can be in more than one club and each club
> has more than one member. Standard M:M relationship. Which link table is
> the "right" way to do it?
>
> This:
>
> CREATE TABLE (
> userid INTEGER NOT NULL REFERENCES users,
> clubid INTEGER NOT NULL REFERENCES clubs,
> PRIMARY KEY (userid, clubid)
> );
>
> Or this:
>
> CREATE TABLE (
> id SERIAL PRIMARY KEY,
> userid INTEGER NOT NULL REFERENCES users,
> clubid INTEGER NOT NULL REFERENCES clubs
> );
>

The former uses a primary key across both columns to enforce a unique
constraint. In the latter, you have a seperate ID column, which does not
enforce that constraint. And you have to ask yourself if you'll ever be
referencing that ID column for anything at all. I doubt i ever would.
Generally, you'd be using this to relate rows from a more generalised
table using either the club ID or the user ID. I can't see how having a
seperate serial ID column would be useful for any kind of select.

brian