Re: [SQL] Question about One to Many relationships
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about One to Many relationships
On Fri, 24 Mar 2006 11:52:31 -0500 Todd Kennedy [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Question about One to Many relationships
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
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Question about One to Many relationships
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 [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Question about One to Many relationships
On Fri, 24 Mar 2006 13:34:34 -0500 Joe [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about One to Many relationships
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
