Re: MySQL and "set" data type table joining

Marshall Spight wrote:

> "Eep²" <eep_at_tnlcNOSPAM.com> wrote in message news:3D9ECDFF.C02B4CE0_at_tnlcNOSPAM.com...
> > 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.
>
> If I understand you correctly, you want to have a single cross-reference table
> to handle all the relationships in your schema. This is a really bad idea, especially
> if the table will ever be updated. Consider: you have game G, in genres genre1
> and genre2. G uses engine1 and engine2. Later you decide to classify G as being
> in genre3 as well. Instead of being able to do this by inserting (G, genre3) into
> the game_genre table, you'll instead have to insert:
> (G, genre3, engine1)
> (G, genre3, engine2)
> into what you're calling the "compare" table. Consider what happens when you
> want to delete something. All of these single-change operations now involve
> many rows.

That's why I mentioned using sets in my original post. By having a separate genres table, and referencing multiple entries to it via a "genreID" set in the "compare" (single cross-reference) table, I can reduce duplicating keys and the need for a separate "games_genres" cross-reference table.

> > 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. :/
>
> Not that confusing. It's just confusing because you're not used to it yet.
> Consider if you were writing a program, and it had ten functions, or ten
> classes. Is that a lot?

I don't know; I don't code! Well, I'm trying to learn PHP to deal with MySQL though, but am still new to both...

> > 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!)
>
> None of these look quite right. It's not at all clear to me that you've decided which
> relationships are 1:1, 1:many, and many:many. Can a game have multiple developers?
> Certainly.

Not usually, actually, but I have a separate developers table because they apply to games AND engines.

> Can it have multiple names? Engines? Publishers? These I'm less sure of.

Yes to both.

> If each game can have only one of these, then they should be columns in the game
> table, not separate tables. (That is, in the case of publishers, you would have a column publisherID in the game table, instead of a
> game_publishers table.)

But what about duplicated publisher fields for multiple games? For example, Eidos publishes a LOT of game series (Tomb Raider, Thief, Deus Ex, etc). I thought the whole point of keeping things in separate tables was to reduce duplicated data? By having publishers in a separate table I can just make a call to the publisherID (which will be easier to update should the publisher name change or something--though I'd probably want to keep the old name in for reference).

> > Suggestions? Is it possible to use a single cross-reference table?
>
> Not really, no.

You sure? ;P Thanks for the reply, by the way. Received on Sat Oct 05 2002 - 18:09:06 CEST