I am in the process of desiging a php/mysql web site. This site will have users and users accounts. A user can belong to any amount of groups on the system. A group on the system is basically just a bunch of users. My problem is how can i dynamicaly keep track of the groups that a certain user belongs to.
For example user "Bob" migth belong to group's A, B and C. My user table columns might look like this
username, password, group1, group2, group3
What happens if the next user belonged to 10 groups and another 15. How could i store this in a database and allow users to belong to as many groups as they like.
I thought of maybe just having a text field where i would comma or whitespace seperate each group a user belonged to. For instance A,B,C,R,Z,L . That scheme seems kinda sloppy and i believe that the parsing would get somewhat out of hand. I also thought that i could leave it up to the group table to keep track of which users belonged to the group. For example the group A table would have all the users listed in it. But i see a problem with that when you have a large amount of users and groups. And in order to find out which group a user belonged to you would have to search each group table inorder to find out what each user belonged to. And for a system with lets say 10,000 users this could be a bottleneck. Expescially using mySQL with no row level locking.
Any suggestions or past experience solutions?
Your help would be greatley appreciated.
Ira
