From: PFC <lists@peufeu.com>
To: 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
Date: Fri, 24 Mar 2006 18:29:25 +0100
Message-ID: <op.s6xhjbjocigqcu@apollo13> (raw)
In-Reply-To: <20060324120142.d3fdf477.darcy@druid.net>
References: <226d83de0603240852q54af3512odfce218d69718c8b@mail.gmail.com>
<20060324120142.d3fdf477.darcy@druid.net>
>> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@peufeu.com, darcy@druid.net, todd.kennedy@gmail.com
Subject: Re: Question about One to Many relationships
In-Reply-To: <op.s6xhjbjocigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox