Re: MySQL and "set" data type table joining

Jonathan Leffler wrote:

> 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?

Unfortunately there isn't a MySQL newsgroup that I know about--and I've already been posting to MySQL web forums. Besides, other people have posted MySQL posts here and they've been promptly replied while mine has not. :/

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

Well, I've been using PHP to interpret the genreID set into appropriately checked checkboxes and table field entries but I'd like to see if there's a way to do it from/within MySQL directly instead of having to dork around with server-side code (vs. in-database).

Anyway, thanks for the response at least--I'll keep looking. Received on Wed Oct 02 2002 - 07:42:25 CEST