0

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

or 

SELECT GROUPID FROM SUBSCRIPTIONS WHERE USERID=4

This will get me the desired lists. Is this the most efficient/standard way to do this or is there a better way?

samurdhilbk
  • 419
  • 1
  • 5
  • 16

2 Answers2

1

You wrote all right.

Normally there are 3 types of relations between records in relative databases:

  1. One - one (e.g. user and profile linked via user.profile_id = profile.id)
  2. One - many (user and messages linked via message.user_id = user.id)
  3. Many - many

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
Stalinko
  • 3,319
  • 28
  • 31
1

If I understand your question correctly, that is the standard way.

You've created a "pivot table" that sits between the user table and the groups table and it stores the relationships between the two. This is the way that many-to-many relationships are stored in relational databases. As you correctly stated, you can retrieve all members of a group or all groups for a member that way.

Ambulare
  • 897
  • 3
  • 10
  • 32