Re: MySQL / Perl theory & design conundrum

You create a third table called belongs_in and it has two columns - userID and groupID, both of which foreign-key back to their appropriate columns. You also make the primary key a multi-column key, having both columns in it. That way you guarantee you can't have a user belonging to the same group twice.

To figure out what groups a person belongs to, you do

select group_id
  from belongs_in
 where user_id = ?
[download]

And, vice-versa.

------
We are the carpenters and bricklayers of the Information Age.

The idea is a little like C++ templates, except not quite so brain-meltingly complicated. -- TheDamian, Exegesis 6

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Comment on Re: MySQL / Perl theory & design conundrum Download Code

Replies are listed 'Best First'.
Re^2: MySQL / Perl theory & design conundrum by Aristotle (Chancellor) on Aug 05, 2003 at 17:18 UTC
Or you could directly get the group info, which is probably what you were looking for (rather than just the id), by joining the tables: `SELECT group_foo, group_bar FROM groups, belongs_in WHERE belongs_in.user_id = ? AND belongs_in.group_id = groups.group_id` [download] Makeshifts last the longest.	[reply] [d/l]