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
