recently a question came across the techknow list that i've had asked of me several times before, surprisingly by a supposed DBA or two! personaly, i find the solution simple, but everyones brain works a little different and i can't remember names, birthdates, addresses or phone numbers to save my life, so... the following is the question followed by my answer, hope it helps some of you:

have a situation where a Users table has a Category field, and users need to be placed in 1 to 50 different categories. i wanted to foreign key the Category field to a Category table, but since each user will have multiple categories, i don't think i can use the mySQL foreign key features (which i find somewhat confusing anyway. innoDB??)

the way you would want to do that is with a reference table. it's real simple. for example, if you had two tables with the following data fields:

users (ID, Name)
----------------
1, Jack
2, Jill
3, Bill

group (ID, Name)
----------------
1, Boys
2, Girls
3, Cool

If you want your reference table to reflect that while Jack and Bill are boys and Jill is a girl, that only Jill and Bill are cool, it would look like this:

group_user (user.ID, group.ID)
------------------------------
1, 1
2, 2
2, 3
3, 1
3, 3

have both these field built with FK constraints to the respective PK in the user and group tables.

Does that make sense?

additionaly, when you're pulling the data (via web form i presume) make your drop down menu for the group send back not the group name but the group id and that makes the query a little simpler:

select all from user, user_group
where user.id is user_group.user
	and user_group.group is [seleted group id]

...or something like that. i haven't written sql in about a year and a half.

l8rz, tX
r00t@texorcist.org
collusion.group
http://collusion.org
4n7i.31337ist