Surrogate VS natural keys

From: Naz Gassiep <naz(at)mira(dot)net>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Surrogate VS natural keys
Date: 2007-06-20 14:11:14
Message-ID: 46793582.8070109@mira.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2007-06-20 14:16:04 Re: Re: [GENERAL] Looking for Graphical people for PostgreSQL tradeshow signage
Previous Message Robin Ericsson 2007-06-20 14:03:01 Re: Problem compiling on CentOS