基于项目的角色的数据库设计 [英] Database design for project-based roles

问题描述

假设我有一个包含以下实体的项目:

Let's assume I have a project with the following entities:

• 用户
• 项目

因此，用户通过多对多关系与项目相关.因此，让我们假设连接表名为 user_projects，其中包含 user_id 和 project_id.

因此，问题在于用户根据其角色具有不同的访问权限(权限)(因此我需要以某种方式将 Role 实体实现到我的数据库架构中) 在项目中，我对如何在我的数据库中实现这个有点困惑.请记住，用户在注册时不受单一角色的约束，角色是在创建项目时决定的.

So, the problem is that a user has different access rights (permissions) based on their role (so I need to somehow implement the Role entity into my database schema) in the project, and I'm a little confused as to how I would implement this in my database. Please bear in mind that a user is not bound to a single role during registration, the role is decided when a project gets created.

我的思路如下.我猜用户和项目都与角色相关 作为多对多关系，对吗?因此，两者都需要自己的连接表，例如 user_roles 和 project_roles.但这对我来说似乎有点奇怪，我想可以以某种方式更优雅地处理.

My train of thought was the following. I guess both Users and Projects would be related to the Role as a Many-to-Many relation, is that correct? So both would need their own join table, something like user_roles and project_roles. But this seems a little odd to me, and could be handled more elegantly somehow, I imagine.

非常感谢任何建议，如果答案显而易见，我很抱歉，我是数据库设计的新手.

Any advice would be much appreciated, apologies if the answer is blatantly obvious, Im new to database design.

非常感谢！

推荐答案

扩展您拥有的 user_projects 表:

CREATE TABLE roles (
   role_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   role_description text NOT NULL
);

CREATE TABLE user_projects (
   user_id bigint REFERENCES users NOT NULL,
   project_id bigint REFERENCES projects NOT NULL,
   PRIMARY KEY (project_id, user_id),
   role_id bigint REFERENCES roles  NOT NULL
);

那么user_projects中的每一个条目不仅关联了一个用户和一个项目，而且还分配了一个项目中的角色.

Then every entry in user_projects does not only relate a user and a project, but also assigns a role in the project.

这篇关于基于项目的角色的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！