samurdhilbk samurdhilbk - 2 months ago 8
MySQL Question

mysql - Maintaining Subscription List of a Group in a Website

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?

Answer

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
Comments