Re: MySQL and "set" data type table joining
Date: Sat, 05 Oct 2002 15:28:11 GMT
Message-ID: <fyDn9.50613$PP.56618_at_rwcrnsc53>
"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.
> 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. :/
>
> 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. Can it have multiple names? Engines? Publishers? These I'm less sure of. 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.)
> Suggestions? Is it possible to use a single cross-reference table?
Not really, no.
Marshall Received on Sat Oct 05 2002 - 17:28:11 CEST