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,以标识组和组的创建者。