CODEWITHSUNDEEP
mysqlperformancedatabase-design
Panda
Panda

Reputation: 6896

MySQL - Storing groups the user is in

I have a website that assigns user to groups and each user can be in unlimited number of groups.

Example:

User A:

User B

Currently, my table structure is as follows:

Table: login

I'm considering storing PHP serialise array by having a new row in the table login. However, editing a user's groups would be difficult as I would have to unserialise it then serialise it again.

Or would it be better to store the groups in a new table entirely?

What is the fastest and most efficient way to store the groups that each user belongs to?

This method needs to be easy when updating the groups and also fast when querying.

Hope that there's a better method than serialise.

Upvotes: 0

Views: 375

Answers (1)

Darwin von Corax
Darwin von Corax

Reputation: 5256

What you're describing is called a many-to-many relationship (each group has multiple members, and each person is a member of multiple groups.) The usual approach would be to have a second table for groups, indexed by a unique group ID and storing all information about each group except membership, and a third table for membership.

The membership table (known as an "intersection entity") has as its columns the primary keys of the other two tables, that is, login_id and group_id. Its primary key would be the concatenation of both its columns (PRIMARY KEY (login_id, group_id)) and each of its columns would also be a foreign key referencing one of the other tables.

This situation is well-known and well-studied, and in fact is about the second thing you learn when you begin to study normalization of database tables.

An example might be

CREATE TABLE login
(
  id INT AUTO_INCREMENT,
  username VARCHAR(255),
  password VARCHAR(255),
  -- other columns go here
  PRIMARY KEY (id)
);

CREATE TABLE groups
(
  id INT AUTO_INCREMENT,
  name VARCHAR(255),
  description TEXT,
  -- other columns go here
  PRIMARY KEY (id)
);

CREATE TABLE membership
(
  login_id INT,
  group_id INT,
  -- possibly more columns, or perhaps not
  PRIMARY KEY (login_id, group_id),
  CONSTRAINT member_user FOREIGN KEY (login_id) REFERENCES login (id),
  CONSTRAINT member_group FOREIGN KEY (group_id) REFERENCES groups (id)
);

The foreign keys on membership ensure that a user cannot be a member of a group unless both the user and the group already exist. The composite primary key for membership ensures that a given person can belong to a given group only once; neglecting this point can lead to some really bizarre problems when you try to query the tables later.

Hope that helps.

Upvotes: 3

Related Questions