CODEWITHSUNDEEP
mysql
Caeculus
Caeculus

Reputation: 89

Relating 2 tables (users and groups) via IDs

I have 2 tables in my MySQL database for users and groups. I need to relate users with groups and groups with the users. The only way that came my mind is having a group_ids col for users and user_ids col for groups. I have to do like this because I will show users' groups that they registered in their profile and I will show registered users in groups' users pages.

In this option I need to to store group ids for users like "2,5,14" and same in groups for registered user ids like "22,24,15 ...".

It sounds okey to me but parsing IDs on back-end from commas is not sounds "professional". And also I have concerns for the performance when there is huge amounts of users in a group.

I know this seems like a opinion based question but I have a question and I think it is not opinion based.

Is there a usage like this in "data science"? I mean, is this a common usage or am I missing something here because I really can't think something else.

Upvotes: 0

Views: 39

Answers (1)

Strider
Strider

Reputation: 134

You could create a new table called user_group wich stores the user_id and group_id as foreign key and primary key

The you can get all groups by user with

SELECT item1, item2... 
FROM user 
INNER JOIN user_group on user.user_id = user_group.user_id
INNER JOIN group on user_group.group_id = group.group_id 
WHERE user.user_id = id;

Upvotes: 1

Related Questions