如何设计一个需要引用的表的数据库？ [英] How to design a database with a table that needs to reference itself?

问题描述

我正在构建一个数据库，遇到一个问题，我似乎不能包容我的想法。数据库比所图示的复杂得多，但问题可以在下面的表格结构中进行说明。

问题是每个员工都有一个经理，每个经理都是一名员工。看来这些表必须参考彼此。但是，当我设置它时，这似乎不能正常工作。

我正在使用cakephp。这种关系类型的名称是什么？这是错误的设计吗？这种情况的适当设计是什么？我希望它可以自动运行。

解决方案

如果我正确理解问题，您不需要经理桌子。由于经理是员工，所以没有理由不要将管理员存储在员工表中。只需在Employees表中添加一个名为 manager_id 的字段，对于任何给定行的引用，该员工的管理员（这是同一个表中的另一行）。

这被称为递归的一对一关系，这是很常见的。在您的情况下，这也将阻止您在两个实体（名称，密码，职务等）之间定义重复的字段。

I'm building a database and have run into a problem that I can't seem to wrap my mind around. The database is much more complex than what is pictured, but the problem can be distilled into the table structure below.

The issue is that every employee has a manager and every manager is an employee. It would seem that these tables have to reference eachother. However, this doesn't seem to work correctly when I set this up.

I'm using cakephp. What is the name of this relationship type? Is this the wrong design? What is the proper design for this situation? I would like it to work as automagically as possible.

解决方案

If I understand the question correctly, you don't need the Managers table at all. Since a manager is an employee, there is no reason the managers shouldn't be stored in the Employees table. Simply add a field to the Employees table called manager_id, which for any given row references that employee's manager (which is another row from the same table).

This is called a recursive one-to-one relationship, and it is quite common. In your case, it will also prevent you from having to define duplicate fields across the two entities (names, passwords, job titles, etc).

这篇关于如何设计一个需要引用的表的数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！