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.
junction (leader_id, member_id)
where each separate field referrences tousers
table independently. Var2.groups
table withleader_id
field referenced tousers
. Relational tablejunction (group_id, member_id)
wheregroup_id
is referenced togroups
andmember_id
is referenced tousers
. – Akina Oct 17 at 18:08user
+group
. – Akina Oct 18 at 5:21