From: | PFC <lists(at)peufeu(dot)com> |
---|---|
To: | "D'Arcy J(dot)M(dot) Cain" <darcy(at)druid(dot)net>, "Todd Kennedy" <todd(dot)kennedy(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Question about One to Many relationships |
Date: | 2006-03-24 17:29:25 |
Message-ID: | op.s6xhjbjocigqcu@apollo13 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Todd Kennedy | 2006-03-24 17:37:06 | Re: Question about One to Many relationships |
Previous Message | Milorad Poluga | 2006-03-24 17:22:45 | Re: Question about One to Many relationships |