sql - N :M Relationship between users and guests

Question

我正在构建一个可以将访客分配给现有用户的应用程序。这些 guest 可以看到有关与他们关联的用户的一些信息。

这是 N:M :

用户可以有多个访客关联

访客可以关联多个用户

实际上，它们存储在同一个表中，它们之间的唯一区别是它们所具有的作用。

(我的真实表有更多字段，如密码等......但这些与这个问题无关)



我需要创建一个这样的表:



问题是 userId 和 guestId 被引用到 id 表的同一列( Users )。

这是可行的吗？

Answer 1

用户只能是访客或主机。这就是为什么您的用户表中有一个角色列。

您的 guest 表连续包含两个用户，正如我在请求评论中提到的，我将他们称为 host_user_id 和 guest_user_id 以明确他们的角色并表明他们都是用户 ID。

table 设计是可以的。唯一的缺点是您可能会错误地将主机设为访客，将访客设为主机，因为 guest 表不知道哪个是哪个，两者都只是用户 ID。如果您希望数据库在这方面保证一致性，这会变得有点复杂。这是一个处理这个问题的设计:

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')
);

这看起来不像以前那么好，因为 host_role 一直是 'host' 并且 guest_role 一直是 'guest' 对我们人类读者来说看起来是多余的，但它保证 host_user_id 确实指的是主机用户，而 guest_user_id 指的是访客用户。