为用户管理系统设计数据库

I am designing a user management system.

我正在设计一个用户管理系统。

The user can be a manager or a simple user. If the manager logs in he should be able to see all employees or users working under him.

用户可以是经理或简单用户。如果经理登录,他应该能够看到在他下面工作的所有员工或用户。

Example:

M1 -> U1, U2, M11

and M11-> U3, U4, M12

and M12 -> U5, U6, U7

when M1 logs in he should see data of U1, U2, M11, U3, U4, M12, U5, U6, U7

当M1登录时,他应该看到U1,U2,M11,U3,U4,M12,U5,U6,U7的数据

and if M12 logs in then U5, U6, U7 only.

如果M12只登录U5,U6,U7。

1 个解决方案

#1

If I understood your problem correctly then below is a sample

如果我理解你的问题,那么下面就是一个例子

DECLARE @tblEmployee AS TABLE ( EmployeeId INT IDENTITY(1,1),
                                    FName       VARCHAR(100),
                                    LName       VARCHAR(100),
                                    ManagerId   INT  NULL --  Reference of EmployeeId 
                                    )

You can achieve the task by above table structure and apply CTE Common table Expression for retrival.

您可以通过上面的表结构来完成任务,并应用CTE Common table Expression进行回溯。

The other way around Hierarchy Data type is https://docs.microsoft.com/en-us/sql/relational-databases/tables/lesson-1-1-examining-the-current-structure-of-the-employee-table?view=sql-server-2017

层次结构数据类型的另一种方法是https://docs.microsoft.com/en-us/sql/relational-databases/tables/lesson-1-1-examining-the-current-structure-of-the-employee-表?视图= SQL服务器-2017

#1