Обсуждение: Question about One to Many relationships
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
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.
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
>> 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.
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. >
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 > >
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.
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
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