具有3个角色的数据库设计用户

After reading Database design: 3 types of users, separate or one table? I am confuse on how I should design the database.

阅读数据库设计后:3种类型的用户,单独或一个表?我对如何设计数据库感到困惑。

---------------------- 1st Design ----------------------

---------------------- 1st Design ----------------------

USERS

• username (PK)
• password
• account type (Admin, Manager or Worker)

帐户类型(管理员,经理或工人)

PRO: When log in I can easily retrieve if this user is Admin, Manager, Worker

PRO:登录时,如果此用户是Admin,Manager,Worker,则可以轻松检索

CON: I cannot form relationship (ONE Manager to MANY Workers) using this design.

CON:我不能使用这种设计形成关系(ONE Manager到MANY Workers)。

---------------------- 2nd Design ----------------------

----------------------第二设计----------------------

USERS

• username (PK)
• password

ADMINS

• username (PK and FK to USERS.username)

用户名(PK和FK到USERS.username)

• password

MANAGERS

• username (PK and FK to USERS.username)

用户名(PK和FK到USERS.username)

• password

WORKERS

• username (PK and FK to USERS.username)

用户名(PK和FK到USERS.username)

• owner (FK to MANAGERS.username)

所有者(FK到MANAGERS.username)

• password

PRO: Relationship can be formed

PRO:可以形成关系

CON:

• When log in I have to find if user is Admin/Manager/Worker by looking in each of the table.

登录时,我必须通过查看每个表来查找用户是否为Admin / Manager / Worker。

• It is also hard to enforce if one username appear multiple time across ADMINS, MANAGERS and WORKERS (BIG CONCERN!!!)

如果一个用户名在ADMINS,MANAGERS和WORKERS之间出现多次,那么也很难强制执行(BIG CONCERN !!!)

I have been spending several hours searching google for good design but couldn't find any solution. I am sure this kind of requirement is quite common? Please provide some insight! Thank you!

我花了几个小时搜索谷歌的好设计,但找不到任何解决方案。我相信这种要求很常见吗?请提供一些见解!谢谢!

3 个解决方案

#1

You can form a type of one-many relationship with the first design - just add a ManagerID that refers back to the UserID.

您可以与第一个设计形成一种多关系类型 - 只需添加一个引用回UserID的ManagerID即可。

You end up with something like this:

你最终得到这样的东西:

UserID    Username    AcctType    ManagerID
1         Bob         Manager     NULL
2         John        Worker      1

Another option is to add a third table that does the linking between a worker and a manager.

另一种选择是添加第三个表来完成工作者和管理者之间的链接。

User table:

UserID    Username    AcctType
1         Bob         Manager
2         John        Worker
3         Mark        Worker

UserLink table:

ManagerID    UserID
1            2
1            3

#2

The golden design will be :

黄金设计将是:

users (id, username, pass, ...)
roles (id, role, ...)
role_users(id, role_id, user_id, ...)

#3

Another way:

 users:
   id             serial primary key
   name           text
   password       text
   role           roletype    
   manager        int
   managerrole    roletype

and some constraints.

和一些约束。

foreign key (manager,managerrole) references users (id,role)

check ( case when role = 'worker' then managerrole is not distinct from 'manager' and manager is not null else managerrole is null and manager is null end )

now the first constraint forces managers to be present in the user table, and the second constraint enforces that only user with role workers can have a manager and leverages the first contraint to enforce that all such managers have the role 'manager'.

现在,第一个约束强制管理器出现在用户表中,第二个约束强制执行只有具有角色工作者的用户可以拥有管理员并利用第一个约束来强制所有这样的管理者都具有角色“经理”。

I think you also need a unique index on (id, role) and an ordinary index on (manager,managerrole)

我认为你还需要(id,role)上的唯一索引和(manager,managerrole)上的普通索引