1

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.

| improve this question | |
  • Var.1: Separate relation table junction (leader_id, member_id) where each separate field referrences to users table independently. Var2. groups table with leader_id field referenced to users. Relational table junction (group_id, member_id) where group_id is referenced to groups and member_id is referenced to users. – Akina Oct 17 '19 at 18:08
  • Hi @Akina, I don't quite understand your comment. Are you saying to make a third table? Also, the group id is synonymous with the leader id. I forgot to mention that, but I'll edit my question now. – hjk321 Oct 17 '19 at 18:57
  • the group id is synonymous with the leader id. If so use variant 1. Are you saying to make a third table? Of course - "group" is separate entity. But why third? user + group. – Akina Oct 18 '19 at 5:21
1

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.
| improve this answer | |
  • I think you don't quite understand the question. The way you have the groups table makes it so only a single member_id may be in each entry. I need a set of member-id in each entry of the groups table. – hjk321 Oct 18 '19 at 16:22
  • @hjk321 only a single member_id may be in each entry ??? Of course! One member - one record. Ten members - ten records with the same leader_id. And so on. – Akina Oct 18 '19 at 18:07
0

a group leader, and with a variable amount of members

That sounds like a 1:many mapping. Such is implemented by having a column leader_id (with FK) in the members table.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.