Date: Wed, 30 Jul 2003 15:27:36 -0500
From: Dave Dribin <dave-ml@dribin.org>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: One to many query question
Message-ID: <20030730202736.GE1383@neo.realtors.org>
Mail-Followup-To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
References: <20030730193517.GB1383@neo.realtors.org>
	<1059595896.12276.16.camel@eric>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1059595896.12276.16.camel@eric>
User-Agent: Mutt/1.4.1i

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