Date: Fri, 24 Mar 2006 18:29:25 +0100
To: "D'Arcy J.M. Cain" <darcy@druid.net>,
	"Todd Kennedy" <todd.kennedy@gmail.com>
Subject: Re: Question about One to Many relationships
Cc: pgsql-sql@postgresql.org
References: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
	<20060324120142.d3fdf477.darcy@druid.net>
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-ID: <op.s6xhjbjocigqcu@apollo13>
In-Reply-To: <20060324120142.d3fdf477.darcy@druid.net>
User-Agent: Opera M2/8.51 (Linux, build 1462)


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

	For a compilation, you should link a band to a track, not an album. This  
opens another can of worms...

	I would use the following tables :

CREATE TABLE albums (
	id SERIAL PRIMARY KEY
	... other data
);

CREATE TABLE tracks (
	id SERIAL PRIMARY KEY
	album_id INTEGER NOT NULL REFERENCES albums( id )
	... other data
):

CREATE TABLE artists (
	id SERIAL PRIMARY KEY
	... other data
);

-- A role is : composer, performer, singer, DJ, compilation maker, lead  
violonist, etc.
CREATE TABLE roles (
	id SERIAL PRIMARY KEY
	... other data
);

CREATE TABLE track_artists (
	track_id INTEGER NOT NULL REFERENCES tracks( id )
	role_id INTEGER NOT NULL REFERENCES roles( id )
	artist_id INTEGER NOT NULL REFERENCES artists( id )
);

And you may also with to specify main artists for an album :

CREATE TABLE track_artists (
	album_id INTEGER NOT NULL REFERENCES albums( id )
	role_id INTEGER NOT NULL REFERENCES roles( id )
	artist_id INTEGER NOT NULL REFERENCES artists( id )
);

You will then need a few LEFT JOINs and to get the artists for a track. It  
is interesting to know if the artist comes from the album or from the  
track. For instance the composer of the whole album might invite a guest  
singer on some tracks.