SQL Server数据库设计用户、组、角色、成员

时间:2022-10-24 12:48:46

I have the following SQL Server db with one table so far.

我有下面的SQL Server db,到目前为止只有一个表。

----------------
|Users         | 
----------------
| UserId    PK |
|              |
| Other fields |
----------------

I need to add few more tables to it which is not a problem but this is the goal:

我需要增加一些表格,这不是问题,但这是我们的目标:

----------------    ----------------    ----------------   ---------------- 
|Users         |    |Roles         |    | Teams       |    | Groups       | 
----------------    ----------------    ----------------   ---------------- 
| UserId    PK |    | RoleId    PK |    | TeamId    PK |   | GroupId   PK | 
|              |    |              |    |              |   |              | 
| Other fields |    | Other fields |    | Other fields |   | Other fields | 
----------------    ----------------    ----------------   ---------------- 

What I need to achieve is the following:

我需要做的是:

  • I have X amount of users

    我有X个用户

    • User1
    • User1
    • User2
    • User2
    • UserX
    • UserX
  • I have 3 roles only for all users to use in all teams and groups

    我有3个角色,仅供所有用户在所有团队和组中使用

    • Admin
    • 管理
    • Member
    • 成员
    • Visitor
    • 游客
  • One user can create X amount of teams

    一个用户可以创建X个团队

    • Team1
    • Team1
    • Team2
    • Team2
    • TeamX
    • TeamX
  • One user can create X amount of groups

    一个用户可以创建X数量的组。

    • Group1
    • Group1
    • Group2
    • Group2
    • GroupX
    • GroupX
  • Groups and Teams can have users assigned to them with different roles (Admin, Member, Visitor)

    组和团队可以将用户分配给不同的角色(Admin、Member、Visitor)

  • One user can belong to one or many team or groups

    一个用户可以属于一个或多个团队或组。

  • One user can belong to one or many roles

    一个用户可以属于一个或多个角色。

I have some hard time understanding the relation between those tables.

我很难理解这些表之间的关系。

Here is what I managed to achieve based on the answer from @Robertas Valeika.

以下是我根据@Robertas Valeika的回答所取得的成果。

http://i58.tinypic.com/3588h0i.png

http://i58.tinypic.com/3588h0i.png

1 个解决方案

#1


2  

You need 3 more tables. UsersRoles UsersRolesGroups UsersRolesTeams.

你还需要3张桌子。UsersRoles UsersRolesGroups UsersRolesTeams。

Relationships:

关系:

UsersRoles - UsersRolesGroups, Groups - UsersRolesGroups

UsersRoles - UsersRolesGroups - UsersRolesGroups

UsersRoles - UsersRolesTeams, Teams - UsersRolesTeams

UsersRoles - UsersRolesTeams, team - UsersRolesTeams

Users - UsersRoles, Roles - UsersRoles.

用户-用户角色,角色-用户角色。

And add FK to users in groups and teams tables to identify creator of group and team.

并向组和组表中的用户添加FK,以标识组和组的创建者。

#1


2  

You need 3 more tables. UsersRoles UsersRolesGroups UsersRolesTeams.

你还需要3张桌子。UsersRoles UsersRolesGroups UsersRolesTeams。

Relationships:

关系:

UsersRoles - UsersRolesGroups, Groups - UsersRolesGroups

UsersRoles - UsersRolesGroups - UsersRolesGroups

UsersRoles - UsersRolesTeams, Teams - UsersRolesTeams

UsersRoles - UsersRolesTeams, team - UsersRolesTeams

Users - UsersRoles, Roles - UsersRoles.

用户-用户角色,角色-用户角色。

And add FK to users in groups and teams tables to identify creator of group and team.

并向组和组表中的用户添加FK,以标识组和组的创建者。