From: Todd Kennedy <todd.kennedy@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Question about One to Many relationships
Date: Fri, 24 Mar 2006 11:52:31 -0500
Message-ID: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: todd.kennedy@gmail.com
Subject: Re: Question about One to Many relationships
In-Reply-To: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox