Re: MySQL and "set" data type table joining

Eep² wrote:

> OK, I'm TRYING to figure out this relational database crap but it's wrackin' mah bwain.

Well, since the subject starts 'MySQL', perhaps you should consider a MySQL forum instead of the generic comp.databases and comp.databases.theory?

I haven't spent any significant time looking at your problem, but I think the usual way to model it is a table game_genre, with columns game_id and genre_id, both foreign keys into tables defining games and genres. No messing with SET types unless you know enough to know how to use them efficiently. Don't forget, premature optimization is the root of all evil. Do it the easy to understand way, then demonstrate that you have a performance problem using that method, and then consider the alternatives. In particular, note that enforcing 'referential integrity' on the elements of a SET to another table typically is not possible -- so you have to write code somewhere (trigger, program) to ensure that the elements of the set of genres for a particular game are all valid genres. If you have a system capable of defining domains or distinct types, that may not be a problem (though you might still want to ban pre-school comedy horror games - ie some combinations of genres are not appropriate).

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix 1.00.PC2 -- http://dbi.perl.org/