From: D'Arcy J.M. Cain <darcy@druid.net>
To: Todd Kennedy <todd.kennedy@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question about One to Many relationships
Date: Fri, 24 Mar 2006 12:01:42 -0500
Message-ID: <20060324120142.d3fdf477.darcy@druid.net> (raw)
In-Reply-To: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
References: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
On Fri, 24 Mar 2006 11:52:31 -0500
"Todd Kennedy" <todd.kennedy@gmail.com> wrote:
> 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"
Wrong in what sense? I can see issues depending on what your
requirements are. Well, one issue. There is nothing in the above
definition that guarantees that every album has at least one band on
it. Is that an issue in this system? Otherwise, I can't see anything
wrong from a relational database POV.
What are people saying is wrong about it?
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
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: darcy@druid.net, todd.kennedy@gmail.com
Subject: Re: Question about One to Many relationships
In-Reply-To: <20060324120142.d3fdf477.darcy@druid.net>
* 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