Thread: Question about One to Many relationships

Question about One to Many relationships

From
"Todd Kennedy"
Date:
Hi,

This should be a simple idea, but I've been going back and forth on it
with various people in my tech group.

So I've got two tables, one for albums and one for bands, for
simplicity's sake, they look like this:

CREATE TABLE bands (
id serial PRIMARY KEY,
name varchar(64) NOT NULL CHECK( name <> ''),
UNIQUE(name)
);

CREATE TABLE albums (
id serial PRIMARY KEY,
name varchar(128) NOT NULL CHECK( name <> '')
);

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:

band_id integer REFERENCES band (id)

to the albums table, othewise i'd have to duplicate the albums in the
table (one record for each band associated with an album).

I thought a lookup table would be appropriate here, so like:

CREATE TABLE bands_on_album (
id serial PRIMARY KEY,
band_id integer REFERENCES band (id),
album_id integer REFERENCES albums (id)
)

but i'm being told this is "wrong"

I feel like this is the accurate way to do this, does any one have
experience on this matter?

Thanks!
Todd


Re: Question about One to Many relationships

From
"D'Arcy J.M. Cain"
Date:
On Fri, 24 Mar 2006 11:52:31 -0500
"Todd Kennedy" <todd.kennedy@gmail.com> wrote:
> So I've got two tables, one for albums and one for bands, for
> simplicity's sake, they look like this:
> 
> CREATE TABLE bands (
> id serial PRIMARY KEY,
> name varchar(64) NOT NULL CHECK( name <> ''),
> UNIQUE(name)
> );
> 
> CREATE TABLE albums (
> id serial PRIMARY KEY,
> name varchar(128) NOT NULL CHECK( name <> '')
> );
> 
> 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:
> 
> band_id integer REFERENCES band (id)
> 
> to the albums table, othewise i'd have to duplicate the albums in the
> table (one record for each band associated with an album).
> 
> I thought a lookup table would be appropriate here, so like:
> 
> CREATE TABLE bands_on_album (
> id serial PRIMARY KEY,
> band_id integer REFERENCES band (id),
> album_id integer REFERENCES albums (id)
> )
> 
> but i'm being told this is "wrong"

Wrong in what sense?  I can see issues depending on what your
requirements are.  Well, one issue.  There is nothing in the above
definition that guarantees that every album has at least one band on
it.  Is that an issue in this system?  Otherwise, I can't see anything
wrong from a relational database POV.

What are people saying is wrong about it?

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: Question about One to Many relationships

From
Milorad Poluga
Date:
Child  table references a not-existing table:
('band' insted of 'bands')

Error: ERROR: relation "band" does not exist

> CREATE TABLE bands (

> CREATE TABLE bands_on_album (
- - -
> band_id integer REFERENCES band (id),
- - -

Regards, 
Milorad Poluga

milorad.poluga@cores.co.yu



Re: Question about One to Many relationships

From
PFC
Date:

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


Re: Question about One to Many relationships

From
"Todd Kennedy"
Date:
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.

Thanks!
Todd

On 3/24/06, D'Arcy J.M. Cain <darcy@druid.net> wrote:
> On Fri, 24 Mar 2006 11:52:31 -0500
> "Todd Kennedy" <todd.kennedy@gmail.com> wrote:
> > So I've got two tables, one for albums and one for bands, for
> > simplicity's sake, they look like this:
> >
> > CREATE TABLE bands (
> > id serial PRIMARY KEY,
> > name varchar(64) NOT NULL CHECK( name <> ''),
> > UNIQUE(name)
> > );
> >
> > CREATE TABLE albums (
> > id serial PRIMARY KEY,
> > name varchar(128) NOT NULL CHECK( name <> '')
> > );
> >
> > 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:
> >
> > band_id integer REFERENCES band (id)
> >
> > to the albums table, othewise i'd have to duplicate the albums in the
> > table (one record for each band associated with an album).
> >
> > I thought a lookup table would be appropriate here, so like:
> >
> > CREATE TABLE bands_on_album (
> > id serial PRIMARY KEY,
> > band_id integer REFERENCES band (id),
> > album_id integer REFERENCES albums (id)
> > )
> >
> > but i'm being told this is "wrong"
>
> Wrong in what sense?  I can see issues depending on what your
> requirements are.  Well, one issue.  There is nothing in the above
> definition that guarantees that every album has at least one band on
> it.  Is that an issue in this system?  Otherwise, I can't see anything
> wrong from a relational database POV.
>
> What are people saying is wrong about it?
>
> --
> D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
> http://www.druid.net/darcy/                |  and a sheep voting on
> +1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.
>


Re: Question about One to Many relationships

From
"Todd Kennedy"
Date:
We're not concerned with the track info.  This is a listing of album
information, hence the one to many relationship between the album and
the artist.

and for the record, i should correct myself.  he said it was "bad" not "wrong".

but i hadn't given him all the details.

But.  Yes.  Thank you all for your help.

On 3/24/06, Joe <svn@freedomcircle.net> wrote:
> 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
>
>


Re: Question about One to Many relationships

From
"D'Arcy J.M. Cain"
Date:
On Fri, 24 Mar 2006 13:34:34 -0500
Joe <svn@freedomcircle.net> wrote:
> 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".

I think that you are making assumptions about his requirements.  Also,
who's to say that each track can have one and only one band?  What if
you have an album of sound effects?  That's no bands.

I think that the most instructive thing would be to hear what his
friends claim the problem is.  Their issue may be based on a more
complete knowledge of his requirements.  That may be more complicated,
simpler or both than we know now.

-- 
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Re: Question about One to Many relationships

From
Joe
Date:
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



Re: Question about One to Many relationships

From
"Jim C. Nasby"
Date:
On Fri, Mar 24, 2006 at 06:29:25PM +0100, PFC wrote:
> 
> 
> >>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 :

BTW, if you're going to be writing code to manage stuff like this, you
should absolutely check out the source for http://musicbrainz.org/,
which uses PostgreSQL as it's backend.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461