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" <[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 4: Have you searched our list archives? http://archives.postgresql.org
