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

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

1 个解决方案

#1

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