N:M Relationship between users and guests

I’m building an app that can assign guests to an existing user. Those guests can see some information about the users they are associated with.

It’s N:M:

• Users can have multiple guests associated
• Guests can be associated to multiple users

Under the hood they are stored in the same table, the only difference between them is the role they have.

(My real table have more fields like password etc… but those are not relevant to this question)

I need to create a table like this:

The thing is that userId and guestId are referenced to the same column (id) of Users table.

Is this viable?

Answer

A user can only be either a guest or a host. This is why you have a role column in your user table.

Your guests table contains two users in a row, and as I mentioned in the request comments, I’d call them host_user_id and guest_user_id to make their roles obvious and indicate they are both user IDs.

The table design is okay for that. The only downside is that you could mistakenly make a host a guest and a guest a host, because the guests table is not aware of which is which, both are mere user IDs. If you want the database to guarantee consistency in this aspect, this gets a tad more complicated. Here is a design that deals with this problem:

create table users
(
  id         not null int,
  full_name  not null varchar(100),
  role       not null varchar(10),
  created_at not null timestamp,
  updated_at          timestamp,
  constraint pk_users primary key (id),
  constraint chk_users_role check (role in 'host', 'guest'),
  constraint unq_users_role unique (id, role)
);

create table guests
(
  host_user_id  not null int,
  host_role     not null varchar(10),
  guest_user_id not null int,
  guest_role    not null varchar(10),
  constraint pk_guests primary key (host_user_id, guest_user_id),
  constraint fk_guests_host foreign key (host_user_id, host_role) references users (id, role),
  constraint fk_guests_guest foreign key (guest_user_id, guest_role) references users (id, role),
  constraint chk_guests_host_role check (host_role = 'host'),
  constraint chk_guests_guest_role check (guest_role = 'guest')
);

This doesn’t look as nice as before, as host_role being constantly 'host' and guest_role being constantly 'guest' looks redundant to us human readers, but it guarantess that a host_user_id really refers to a host user and a guest_user_id to a guest user.