SQL 递归关系的数据库设计

Database design for a recursive relationship

提问by Songo

Consider this case where I'm trying to model a database for a company:

考虑这种情况，我试图为一家公司建立一个数据库模型：

• Entities: Employees, Managers, Departments.
• An Employeeworks in only 1 Departmentwhile a Departmentmay have many Employeesworking in it.
• A Managermay manage only 1 Departmentand similarly a Departmentmay have only 1 Manager.
• A Managersupervises many Employees, but an Employeeis only supervised by one Manager.

• 实体：Employees, Managers, Departments.
• AnEmployee只在 1Department个中工作，而 aDepartment可能有很多个Employees工作在其中。
• AManager可能只管理 1Department并且类似地 aDepartment可能只有 1 Manager。
• AManager监督多人Employees，而 anEmployee只监督一个人Manager。

Now I have 2 ways to model this:

现在我有两种方法来建模：

First solution:

第一个解决方案：

I'll consider that the Managerentity inherits from the Employeeentity considering that I'll keep data that is unique to the Managers (e.g. Bonus & Status).

考虑到我将保留经理独有的数据（例如奖金和状态），我会考虑该Manager实体继承自该Employee实体。

• Since the relation between Departmentand Employeeis 1:Nthen I'll put the Department Idas a foreign key in the Employeetable for the Worksrelation.

• Since the relation between Departmentand Manageris 1:1then I'll put the Department Idas a foreign key in the Managertable for the Managesrelation.

• 由于之间的关系Department，并Employee为1:N后来我就把这 Department Id作为一个外键Employee表的Works关系。

• 由于之间的关系Department，并Manager为1:1后来我就把这 Department Id作为一个外键Manager表的Manages关系。

Problem: How can I represent the recursive relation between the Managerand Employee?

问题：如何表示Manager和之间的递归关系Employee？

---

---

Second solution:

第二种解决方案：

I'll consider that the Managerentity is not needed as other Employeesmay also have a Bonusand Status. (Actually I added these 2 attributes just to see how to model it in both cases)

我会认为Manager不需要该实体，因为其他实体也Employees可能有Bonusand Status。（实际上我添加了这 2 个属性只是为了看看如何在这两种情况下对其进行建模）

• Since the relation between Departmentand Employeeis 1:Nthen I'll put the Department Idas a foreign key in the Employeetable for the Worksrelation.
• Since the relation between Employeeand Manageris 1:Nthen I'll put the Employee Idas a foreign key in the Employeetable for the Supervisesrelation and call it Manager Id.

• 由于之间的关系Department，并Employee为1:N后来我就把这 Department Id作为一个外键Employee表的Works关系。
• 由于之间的关系Employee，并Manager为1:N后来我就把这 Employee Id作为一个外键Employee表的Supervises关系，并调用它Manager Id。

Problem: How can I represent the relation between the Managerand Department?

问题：我怎么能代表之间的关系Manager和Department？

---

---

Questions:

问题：

1. Is there any obvious mistakes in both design as they are?
2. How to solve each problem in both cases?
3. Is there a better solution than these two?

1. 这两种设计是否有明显的错误？
2. 在这两种情况下如何解决每个问题？
3. 有没有比这两个更好的解决方案？

回答by Branko Dimitrijevic

I'd probably go with something like:

我可能会选择类似的东西：

This model has the following characteristics:

该模型具有以下特点：

• Manager "inherits" employee.
  • To represent an employee, insert a single row in EMPLOYEE.
  • To represent a manager, insert one row in EMPLOYEE andone row in MANAGER.
• A department can have multiple employees.
• Every department has exactly 1 manager and every manager manages 0 or 1 departments.
• A supervisor can be ordinary employee or a manager.
• Departments are not required to "match":
  • A supervisor can work in different department from the supervised employee.
  • A manager can manage different department from where (s)he works.
  • If a supervisor is manager, then the department (s)he manages, the department (s)he works in and the department(s) of his/her supervised employees can all be different.

• 经理“继承”员工。
  • 要表示员工，请在 EMPLOYEE 中插入一行。
  • 为了表示一个经理，插入EMPLOYEE一行，并在MANAGER一行。
• 一个部门可以有多个员工。
• 每个部门正好有 1 个经理，每个经理管理 0 或 1 个部门。
• 主管可以是普通员工或经理。
• 部门不需要“匹配”：
  • 主管可以与受监管的员工在不同的部门工作。
  • 经理可以在他工作的地方管理不同的部门。
  • 如果主管是经理，那么他管理的部门、工作的部门和他/她的下属员工的部门都可以不同。

NOTE: If your DBMS does not support deferred constraints, you'll want to make the DEPARTMENT.MANAGER_ID NULL-able, to break the cycle that would otherwise prevent you from inserting the new data.

注意：如果您的 DBMS 不支持延迟约束，您需要使 DEPARTMENT.MANAGER_ID 为 NULL，以打破循环，否则会阻止您插入新数据。

---

---

If the departments are required to match, then you'd either employ a DBMS-specific technique (such as triggers or "special" constraints), or "propagate" the DEPARTMENT_ID into the PK of employees. This propagation is what ultimately enables the matching:

如果要求部门匹配，那么您要么采用特定于 DBMS 的技术（例如触发器或“特殊”约束），要么将 DEPARTMENT_ID “传播”到员工的 PK 中。这种传播最终实现了匹配：

Since EMPLOYEE_ID must be globally unique, it cannot stay in the composite key together with the DEPARTMENT_ID. So, we make it alternate key and instead use the surrogate EMPLOYEE_NO in the PK.

由于 EMPLOYEE_ID 必须是全局唯一的，因此它不能与 DEPARTMENT_ID 一起留在组合键中。因此，我们将其设为备用键，而是在 PK 中使用代理 EMPLOYEE_NO。

This model prevents you from having a manager that manages one department and works in another, or a supervisor that supervises employees from a different department.

此模型可防止您拥有管理一个部门并在另一个部门工作的经理，或监督来自不同部门的员工的主管。

---

---

In case you are not familiar with the symbol...

如果你不熟悉这个符号......

...it denotes a "category". In this context, you can simply interpret it as a "1 to 0 or 1" relationship between EMPLOYEE and MANAGER.

...它表示一个“类别”。在这种情况下，您可以简单地将其解释为 EMPLOYEE 和 MANAGER 之间的“1 对 0 或 1”关系。

回答by Philippe Grondier

Without getting into details, I do assure you that the Employee/Manager/Department solution is, on the long term, a source of displeasure (at first) then a real PITA (later on) for the persons in charge of maintaining the database and/or developping its interface. So I do advise you to stick to your 2nd proposal.

在不深入细节的情况下，我向您保证，从长远来看，员工/经理/部门解决方案是不满意的来源（起初）然后是负责维护数据库的人员的真正 PITA（后来）和/ 或开发其界面。所以我建议你坚持你的第二个建议。

Regarding the manager/department relation, you have mainly two ways to represent this relation. Both solutions authorize you to keep your recursive "Manager manages Employee" relation in addition to a "manager manages Department" relation that you can implement as follows:

关于经理/部门关系，您主要有两种方式来表示这种关系。除了可以按如下方式实现的“经理管理部门”关系之外，这两种解决方案都授权您保留递归“经理管理员工”关系：

1 - first/simple way: add a manager/employee id in your department table. This field is of course a foreign key to the employee table

1 - 第一个/简单的方法：在部门表中添加经理/员工 ID。这个字段当然是employee表的外键

2 - second/more complex solution: add a "manager" table with the following fields:

2 - 第二个/更复杂的解决方案：添加具有以下字段的“经理”表：

Manager id (PK, surrogate)
Department id (FK)
Employee id (FK)
beginningDate
endingDate

where you will store the management history: who, for which department, from when, till when

您将在哪里存储管理历史记录：谁，在哪个部门，从什么时候到什么时候

In this case do not forget to add some logic (trigger, or client-side control) to translate your business rules such as you can have only one manager for a specific period and a specific department, no department can stay more than ... without a manager, etc.

在这种情况下，不要忘记添加一些逻辑（触发器或客户端控制）来转换您的业务规则，例如您在特定时期和特定部门只能有一个经理，没有一个部门可以停留超过......没有经理等。

EDIT:

编辑：

3 - a richer solution would be a generalisation of my second proposal, and will allow you to keep track of everyone's career in the company. You can do it with a 'works in' table, such as this one (as we call it here a 'position' table, I will keep the same terminology here:

3 - 更丰富的解决方案将是我的第二个建议的概括，并允许您跟踪公司中每个人的职业生涯。你可以用一个“作品在”表来完成，比如这个（因为我们在这里称它为“位置”表，我将在此处保留相同的术语：

Position id (PK, surrogate)
Department id (FK)
Employee id (FK)
Position Level (FK)
beginningDate
endingDate

Where 'position level' leads to another table holding the different positions that can exist in a department, one of them being of course the 'manager' position.

“职位级别”导致另一个表格，其中包含一个部门中可能存在的不同职位，其中一个当然是“经理”职位。

This proposal is closer to what is used in HR database and software, and you might not need such a complex solution. But keep in mind that splitting the human beings in multiple tables is ALWAYS a mistake.

这个提议更接近于人力资源数据库和软件中使用的，你可能不需要这么复杂的解决方案。但请记住，将人分成多个表总是错误的。

EDIT: following your comment ...

编辑：按照您的评论...

To make things clear, I'd advise you to adjust your field names. I'd propose you to have the following fields:

为了说清楚，我建议您调整字段名称。我建议你有以下领域：

Tbl_Employee.id_EmployeeManager

and

和

Tbl_Department.id_DepartmentManager

Doing this, we (or any developper) will immediately understand that id_EmployeeManager participates in the recursive relation between the persons, while id_DepartmentManager participates in the relation between people and department.

这样做，我们（或任何开发人员）将立即明白 id_EmployeeManager 参与人员之间的递归关系，而 id_DepartmentManager 参与人员和部门之间的关系。

Back to your questions, and according to me, you should not create the following link:

回到你的问题，根据我的说法，你不应该创建以下链接：

Tbl_Department.id_DepartmentManager -> Tbl_Employee.id_EmployeeManager

By doing so, you are meaning that somebody cannot be a department manager unlesshe is already managing employees. What about departments with a single employee? What about people named managers of a department newly created, where still no employee is allocated? It does not work. The right link should be:

这样做的意思是，除非某人已经在管理员工，否则他不能成为部门经理。一个员工的部门呢？新创建的部门名为经理的人怎么办，但仍然没有分配员工？这是行不通的。正确的链接应该是：

Tbl_Department.id_DepartmentManager -> Tbl_Employee.id_Employee

You could of course add some business rules saying for example that "an employee managing a department can only be a manager" (id_Employee exists somewhere as id_EmployeeManager) or "an employee managing a department cannot have a manager (where id_EmployeeManager for this employee is null...). But these are only business rules. Your data model is clean to accept all rules as long as the basic rule is respected, which is that a department is managed by an employee!

您当然可以添加一些业务规则，例如“管理部门的员工只能是经理”（id_Employee 作为 id_EmployeeManager 存在于某处）或“管理部门的员工不能有经理（其中该员工的 id_EmployeeManager 为空） ...). 但这些只是业务规则. 只要遵守基本规则, 即一个部门由一个员工管理, 你的数据模型是干净的, 可以接受所有规则!

回答by Ellest

How about sticking with the 2nd design and having a pseudo-relationship?

坚持第二个设计并建立伪关系怎么样？

I'm assuming you're going to have a department_idcolumn in the Employee entity to link the relationship between Employee and Department entities. If we can assume that there won't be a manager hierarchy (managers of managers) we can enforce a pseudo-relationship between the two tables where Department_IDfor managers (Manager_IDis Null) represents the Department they manage.

我假设您将department_id在 Employee 实体中有一个列来链接 Employee 和 Department 实体之间的关系。如果我们可以假设没有经理层次结构（经理的经理），我们可以在两个表之间强制执行伪关系，其中Department_ID经理（Manager_ID为空）代表他们管理的部门。

As long as you document this clearly, I think it would be a space-efficient approach as you already would have a FK column (department_id) in the Employee entity referencing the Department entity.

只要您清楚地记录这一点，我认为这将是一种节省空间的方法，因为您已经department_id在 Employee 实体中拥有一个 FK 列 ( ) 来引用 Department 实体。

回答by Diego

My opinion:

我的看法：

Table Person where you will add the info for both employees and managers, managers are human being too, you know? :), and you have a managerId field to link to the Id of the manager.

Table Person 您将在其中添加员工和经理的信息，经理也是人，您知道吗？:)，并且您有一个 managerId 字段可以链接到经理的 Id。

Table department with the department information

带有部门信息的表部门

and, if the employee can belong to more that one department, create a table employee_department to relate them. If an employee can belong to only one department and you don't need more info in the relation, add a departmentID field on the Employee table.

并且，如果员工可以属于多个部门，则创建一个表employee_department 将它们关联起来。如果一名员工只能属于一个部门，并且您不需要在关系中提供更多信息，请在 Employee 表中添加一个部门 ID 字段。

回答by pascalvgemert

I think this is the best solution:

我认为这是最好的解决方案：

A manager is an employee that manages a department. The recursive relation you can get by the next flow:

经理是管理部门的员工。您可以通过下一个流程获得的递归关系：

Employee has a department A department has a employee as manager

员工有部门 部门有员工担任经理

Maybe its handy to give the employee table a EmployeeType column to define the role.

也许给员工表一个 EmployeeType 列来定义角色很方便。