Storing Membership Data

[eluser]crumpet[/eluser]
Hey,
I'm working on a site right now where users can join groups (like facebook). My question is where should I store group membership.
As i see it the options are these:
1. in the groups table in a field called membership as a serialized array of userID's
2. in the users table in a field called groups as a serialized array of groupID's
3. or both.
I'm leaning towards storing in both because it seems really inefficient if everytime i want to see what groups a user has joined I query the entire groups database, unserialize every membership array and array_search() there userID. (Option two is the same problem the other way around). But storing in both places seems like messy coding.. I'm pretty inexperienced at php and i'm just kind of teaching myself. Is it alright to store the same thing in two places? Is there an easy and clean way to do this that I"m not seeing?

Thanks

[eluser]Brandon Dickson[/eluser]
Why not a group_x_user table,

one column with group id's and another with user id's, then just perform joins

that way your not serializing anything, when you query it would look like this.

Code:

#get users in group

SELECT user_id, user_name FROM group_x_user LEFT JOIN users_table WHERE group_id = x

#get groups for user

SELECT group_id, group_name FROM group_x_user LEFT JOIN group_table WHERE user_id = x

#this also alows for more advanced querying later on...

SELECT user_id, user_name FROM group_x_user LEFT JOIN users_table WHERE group_id = x AND user_country = 'usa'

#etc.

-Brandon

[eluser]crumpet[/eluser]
genious, i knew i was going about this the wrong way
thank you very much for your help

so when a user adds a group i just add an entry to that table with the users id and the group id

awesome

[eluser]marcoss[/eluser]
That's right, you keep the users and groups data in separate tables and relate them by their PK using the schema Brandon proposed, I'd call it users2groups though.

And by the way, it doesn't matter if you are new to php, this is a DB Design issue, if you are also new into that area, you should take a moment to read this tutorial http://www.tomjewett.com/dbdesign/dbdesi...intro.html.

Regards.

[eluser]crumpet[/eluser]
thanks for the link, this is a great resource.