Date: Fri, 24 Mar 2006 12:01:42 -0500
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
Message-Id: <20060324120142.d3fdf477.darcy@druid.net>
In-Reply-To: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
References: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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.