Date: Fri, 24 Mar 2006 13:42:44 -0500
From: "D'Arcy J.M. Cain" <darcy@druid.net>
To: Joe <svn@freedomcircle.net>
Cc: todd.kennedy@gmail.com, pgsql-sql@postgresql.org
Subject: Re: Question about One to Many relationships
Message-Id: <20060324134244.4e8e6ede.darcy@druid.net>
In-Reply-To: <44243BBA.8030802@freedomcircle.net>
References: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
	<20060324120142.d3fdf477.darcy@druid.net>
	<226d83de0603240937o7b5608f9je5179238ee3c9c5a@mail.gmail.com>
	<44243BBA.8030802@freedomcircle.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Fri, 24 Mar 2006 13:34:34 -0500
Joe <svn@freedomcircle.net> wrote:
> Todd Kennedy wrote:
> > 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.
> 
> But if a band can have songs in many albums and an album can have songs 
> from multiple bands, it's a many-to-many relationship, NOT one-to-many. 
>   Short of the full track design suggested by PFC, you'd normally 
> implement a many-to-many table as follows:
> 
> CREATE TABLE bands_on_album (
> band_id integer REFERENCES band (id),
> album_id integer REFERENCES albums (id),
> PRIMARY KEY (band_id, album_id)
> )
> 
> This of course precludes the same band being listed twice in a given 
> album.  If you do need that info, then you're really asking for "tracks".

I think that you are making assumptions about his requirements.  Also,
who's to say that each track can have one and only one band?  What if
you have an album of sound effects?  That's no bands.

I think that the most instructive thing would be to hear what his
friends claim the problem is.  Their issue may be based on a more
complete knowledge of his requirements.  That may be more complicated,
simpler or both than we know now.

-- 
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.