Date: Fri, 24 Mar 2006 13:34:34 -0500
From: Joe <svn@freedomcircle.net>
Subject: Re: Question about One to Many relationships
In-reply-to: <226d83de0603240937o7b5608f9je5179238ee3c9c5a@mail.gmail.com>
To: Todd Kennedy <todd.kennedy@gmail.com>
Cc: "D'Arcy J.M. Cain" <darcy@druid.net>,  pgsql-sql@postgresql.org
Message-id: <44243BBA.8030802@freedomcircle.net>
Organization: Freedom Circle, LLC
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
	<20060324120142.d3fdf477.darcy@druid.net>
	<226d83de0603240937o7b5608f9je5179238ee3c9c5a@mail.gmail.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)

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

Joe