Re: MySQL and "set" data type table joining

OK, I'm TRYING to figure out this relational database crap but it's wrackin' mah bwain. I want to store multiple genres for a game and am having trouble coming up with the most efficient solution.

Do I, like I've been attempting, have to use a genreID "set" type in the "compare" table in order to assign multiple genres to a game--or I must list gameID twice, which would end up duplicating other data?:

table: compare
fields (data): id, gameID, genreID ("set" type) sample data: 1, 1, (1,2)

table: genres
fields: genreID, genre
sample data: 1, action
2, adventure

Or do I list games with multiple genres in the genres table (which still duplicates gameIDs but takes up less space than in the compare table)?:

fields: genreID, gameID, action, adventure, role-playing, simulation, strategy sample data: 1, 1, action
2, 1, adventure

Help!

Eep² wrote:

> Anyone?
>
> Eep² wrote:
>
> > Is this the best way to store "lookup" values in a separate table with a "set" datatype in the main field so multiple genres can be selected without having to add a compare.id column to the genres table?
> >
> > Eep² wrote:
> >
> > > I'm trying to join a "set" field from one table to another table with the set's contents in it.
> > >
> > > table setup:
> > >
> > > CREATE TABLE compare (
> > > id tinyint(4) NOT NULL auto_increment,
> > > genreID set('1','2','3','4','5','6') default NULL,
> > > PRIMARY KEY (id)
> > > ) TYPE=MyISAM;
> > >
> > > INSERT INTO compare VALUES (1,'1,2');
> > > INSERT INTO compare VALUES (2,'1,2');
> > > INSERT INTO compare VALUES (3,'1,2');
> > > INSERT INTO compare VALUES (4,'1,2');
> > > INSERT INTO compare VALUES (5,'1,2');
> > >
> > > CREATE TABLE genres (
> > > genreID tinyint(1) NOT NULL auto_increment,
> > > genre varchar(20) default NULL,
> > > PRIMARY KEY (genreID)
> > > ) TYPE=MyISAM;
> > >
> > > INSERT INTO genres VALUES (1, 'action');
> > > INSERT INTO genres VALUES (2, 'adventure');
> > > INSERT INTO genres VALUES (3, 'role-playing');
> > > INSERT INTO genres VALUES (4, 'simulation');
> > > INSERT INTO genres VALUES (5, 'strategy');
> > > INSERT INTO genres VALUES (6, 'other');
> > >
> > > query:
> > >
> > > SELECT
> > > genres.genre
> > > FROM
> > > compare,genres
> > > WHERE
> > > compare.genreID=genres.genreID
> > >
> > > Now, what I want to happen is when compare.genreID=1,2 the genres.genre should come out "action,adventure" (or, more preferably, "action-adventure"). Right now it comes out as "role-playing" (or "3" using genres.genreID) for some odd reason, as if the set is just being added together instead of remaining a set.
> > >
> > > Help! Thanks.
Received on Wed Oct 02 2002 - 03:51:13 CEST