I have a website that assigns user to groups and each user can be in unlimited number of groups.
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,
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.