Mysql – (MySQL) SET column with values from a foreign key constrant

MySQL

I am making a junction table that tracks relationships between users. Among other things it is formatted with a group leader, and with a variable amount of members. All users have a user id and so this relationship between leader and members must constrain to the defined uuids in the users table.

Currently, the only way I know how to accomplish this is to make a bunch of columns named member1, member2 and so on, and then make foreign key constraints on each column, but this is inefficient as the number of members in a group varies from entry to entry. So I thought to use a SET type for the members column. However, the SET column type forces you to define constants for it, and this simply isn't possible with a wide amount of user ids.

So in essence, my question is, how can I make a set whose values are constrained to the values in another table? I'm trying my best to follow the standard practice of using junction tables, but if you have a better organizational solution which allows me to sidestep this issue entirely while still accomplishing the same or similar goal that would also be most appreciated.

Edit: Group id is synonymous with leader id because groups are identified by their leaders.

Best Answer

Schematically.

CREATE TABLE users ( id PRIMARY KEY, ... );
CREATE TABLE groups ( leader_id REFERENCES users (id),
                      member_id REFERENCES users (id) );

The keys (including primary) and constraints in groups are dependent by:

  • does the user may be a member of one group only, or of a lot of groups;
  • does the group leader may be a member of another group (rings possible);
  • does a leader must (or may) be a member of his own group.
Related Question