DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=BPiI80a3GyhJjr6WKGT356xqjM3JJZCuY7xJU+n5MAFUyvsnERQQ6IXj0HC4NW0GmAHzhH/NhajcaCl3x1dpGFhTLKwl6EMpL4Ir2jInT22pC8/cLJm02T25+gKAv/hLhIjp7BBkS4C54q4mitzMLHI7wwH9xLqDz7c2eGJa4qg=
Message-ID: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
Date: Fri, 24 Mar 2006 11:52:31 -0500
From: "Todd Kennedy" <todd.kennedy@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Question about One to Many relationships
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

This should be a simple idea, but I've been going back and forth on it
with various people in my tech group.

So I've got two tables, one for albums and one for bands, for
simplicity's sake, they look like this:

CREATE TABLE bands (
id serial PRIMARY KEY,
name varchar(64) NOT NULL CHECK( name <> ''),
UNIQUE(name)
);

CREATE TABLE albums (
id serial PRIMARY KEY,
name varchar(128) NOT NULL CHECK( name <> '')
);

And I want to link the band to the album, but, if the album is a
compilation it'll be linked to multiple band.ids, so i can't just add
a column like:

band_id integer REFERENCES band (id)

to the albums table, othewise i'd have to duplicate the albums in the
table (one record for each band associated with an album).

I thought a lookup table would be appropriate here, so like:

CREATE TABLE bands_on_album (
id serial PRIMARY KEY,
band_id integer REFERENCES band (id),
album_id integer REFERENCES albums (id)
)

but i'm being told this is "wrong"

I feel like this is the accurate way to do this, does any one have
experience on this matter?

Thanks!
Todd