Re: MySQL and "set" data type table joining

Yea, but keys are duplicated and I'm trying to only have a single cross-reference table because genres aren't the only thing going to be linked to games. At this rate, with all these cross-reference tables, I'll have at LEAST 10 (see http://tnlc.com/eep/compare/gameform.html for more info on what I'm attempting to database) and it will be VERY confusing to query. :/

So, I was hoping by using sets I could reduce the amount of cross-reference tables and have a more efficient relational database.

MS Access relationship diagrams:

http://tnlc.com/eep/compare/relations.jpg (1 cross-reference table)
http://tnlc.com/eep/compare/relations1.gif (attempting to cross-reference games and genres)
http://tnlc.com/eep/compare/relations2.gif (more cross-reference tables)
http://tnlc.com/eep/compare/relations3.gif (latest with lots of cross-reference tables--ugh, what a confusing mess!)

Suggestions? Is it possible to use a single cross-reference table? If not, am I on the right track with the multi-cross-reference table design? Help!

Marshall Spight wrote:

> "Eep²" <eep_at_tnlcNOSPAM.com> wrote in message news:3D9A5121.5788A4B4_at_tnlcNOSPAM.com...
> > 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
>
> Uh, this sounds like a classic 'many-to-many' situation.
>
> create table games
> (
> game_id int primary key,
> ...
> )
>
> create table genres
> (
> genre_id int primary key,
> ...
> )
>
> create table game_genre
> (
> game_id int foreign key references games,
> genre_id int foreign key references genres
> )
>
> That kind of thing. One row in the games table for each game;
> one row in the genre table for each genre; one row in the game_genre
> table for each genre of each game.
Received on Sat Oct 05 2002 - 13:33:03 CEST