postgresql - 如何正确关联课程、角色和教师（postgres）之间的关系？

Question

我正在尝试为lessons( session) 和teachers( user) 设置数据库模型，我现在有以下 sql，它假设lessons并teachers处于多对多关系：

DROP TABLE IF EXISTS "user";
CREATE TABLE "user" (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL,
    email CITEXT UNIQUE CONSTRAINT email_length CHECK (char_length(email) <= 254),
    password VARCHAR(255) NOT NULL
);

DROP TABLE IF EXISTS "session";
CREATE TABLE "session" (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255),
    event_id INT NOT NULL
);

DROP TABLE IF EXISTS "session_user";
CREATE TABLE "session_user" (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id INT NOT NULL REFERENCES "user"(id),
    session_id INT NOT NULL REFERENCES "session"(id),
    UNIQUE (user_id, session_id)
);

现在我需要实现一个新的角色功能，每个用户在会话中可能有不同的角色。例如，在会话 A 中，用户 X 是主持人，用户 Y 是助理，而在会话 B 中，用户 X 是助理，用户 Y 是主持人。我如何模拟这种关系？

编辑

这个结构是做我想做的事情的正确方法吗？

标签： postgresqldatabase-design