Re: Question about One to Many relationships - Mailing list pgsql-sql

From PFC
Subject Re: Question about One to Many relationships
Date
Msg-id op.s6xhjbjocigqcu@apollo13
Whole thread Raw
In response to Re: Question about One to Many relationships  ("D'Arcy J.M. Cain" <darcy@druid.net>)
Responses Re: Question about One to Many relationships
List 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 KEYalbum_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.


pgsql-sql by date:

Previous
From: Milorad Poluga
Date:
Subject: Re: Question about One to Many relationships
Next
From: "Todd Kennedy"
Date:
Subject: Re: Question about One to Many relationships