Re: MySQL and "set" data type table joining

"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.

HTH Marshall Received on Sat Oct 05 2002 - 07:52:24 CEST