best way to store member of group in mysql table

Question

I am developing an android application where people will sign up and join groups. I have a separate MySQL table which stores details of the users and another table which stores details of groups. But I have problem relating users to groups.

The users table will have his user_id, name, password, email_id, address, etc. The group table will have the group_id, group_name and other details regarding the group.

I must be able to get

1. all the users who are in a group
   
2. all the groups which a user has joined

quickly and easily.

Note: Assume you have over a 100k users and over 1000 groups.

Answer 1

The idiomatic way of doing this would be to have an additional n:m mapping table, which just holds pairs of IDs - a user and a group he or she belongs to:

CREATE TABLE group_membership (
    user_id INT NOT NULL,
    group_id INT NOT NULL,
    PRIMARY KEY (user_id, group_id),
    CONSTRAINT FOREIGN KEY (user_id) REFERENCES users (id),
    CONSTRAINT FOREIGN KEY (group_id) REFERENCES groups (id)
)

And now, e.g., if you wish to query all the users from my_group, you could do so with a couple of joins:

SELECT u.*
FROM   users u
JOIN   group_membership gm ON u.id = gm.user_id
JOIN   groups g ON gm.group_id = g.id
WHERE  g.name = 'my_group'