DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=hcJovFHViHVLfvAsnVnzxZY860sFDksdw8K03P3qPVDEj87zoNdiP2DGJvrBzuihGjAWSsVLj94rDc5eTuhmvg8+v3sRJNKdhuiGVF0HJQZtxhCDxfUjV40Y40c9vc2YN0ZzLDFSTMqn+eujwAbeB4OF4+ixTFRCk2cejrNe0y8=
Message-ID: <226d83de0603240937o7b5608f9je5179238ee3c9c5a@mail.gmail.com>
Date: Fri, 24 Mar 2006 09:37:06 -0800
From: "Todd Kennedy" <todd.kennedy@gmail.com>
To: "D'Arcy J.M. Cain" <darcy@druid.net>
Subject: Re: Question about One to Many relationships
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20060324120142.d3fdf477.darcy@druid.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
	<20060324120142.d3fdf477.darcy@druid.net>

They haven't responded me as of yet. There should be a band associated
with each album -- this is handled in code, but other than that this
is the only relational db way I can think of to do it.

Thanks!
Todd

On 3/24/06, D'Arcy J.M. Cain <darcy@druid.net> wrote:
> 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.
>