I'm creating a website where the users can join certain groups. Now I need to maintain the set of users in each group and/or the set of groups that each user has joined. Since MySql doesn't support arrays, I cannot maintain say, an array of users in a group(as a field in the "groups" table) or an array of groups in a user(as a field in the "users" table). So how can I achieve this?
My current solution is to maintain a table of group-subscriptions which has fields for the userID and groupID. So when I need either of these two lists I can do,
SELECT USERID FROM SUBSCRIPTIONS WHERE GROUPID=3
SELECT GROUPID FROM SUBSCRIPTIONS WHERE USERID=4
You wrote all right.
Normally there are 3 types of relations between records in relative databases:
user.profile_id = profile.id)
message.user_id = user.id)
Your case is the last and it always works via a 3rd table.
For your case it can be
users_subscriptions (user_id, subscription_id)
Example query to select all users with their subscriptions:
SELECT u.name, GROUP_CONCAT(s.name) as `subscriptions` FROM users u JOIN users_subscriptions us ON us.user_id = u.id JOIN subscriptions s ON us.subscription_id = s.id GROUP BY u.id