On Wed, Jul 30, 2003 at 01:11:35PM -0700, Eric Clark wrote: > On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: > > CREATE TABLE cd ( > > id integer unique, > > artist varchar(25), > > title varchar(25) > > ); > > > > CREATE TABLE cd_genres ( > > cd_id integer, > > genre varchar(25) > > ); > > I think you've got this backwards. There is no advantage in the above > table's over simply having a genre varchar(25) in the cd table. > > You really want: > > CREATE TABLE genre ( > genre_id serial, > genre varchar(25) > ); > > CREATE TABLE cd ( > cd_id integer unique, > artist varchar(25), > title varchar(25), > genre_id varchar(25) references genre (genre_id) > );
This doesn't allow multiple genre's per CD, though, does it? A CD can only have 1 genre_id. I would like the ability to have multiple genres, in which case a third table is necessary: CREATE TABLE cd_genres ( cd_id integer, genre_id integer ); cd_id references cd.id and genre_id references genre.genre_id. This still requires the complex LEFT JOIN query from my first post, too, I think, *plus* an extra join between cd_genres and genre. -Dave ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html