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
