Surrogate VS natural keys

[Naz Gassiep <naz@xxxxxxxx>]

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
);

I've always favored natural keys (the first option) as it just seems to
me a more natural and semantic representation of the data, however I
often get app designers complaining about it being more complex or
something.

Comments?