两个实体之间的多重关系，这是好的做法吗？
我有以下关于办公室及其员工的典型场景：
- 每个员工属于一个办公室
- 每个办公室只有一名经理（一名员工）
ER 模型
正如您所看到的，这会导致关系被记录两次，一次是办公室表中的外键指向经理，另一次是员工表中的外键指向员工工作的办公室。
我已经研究了建模的替代方法，但仍然有点迷失。请有人建议一种合适的建模方法，或者我的方法是否适合该场景。
非常感谢
I have the following typical scenario regarding an office and its staff:
- Each staff member belongs to one office
- Each office has only one manager (a staff member)
ER Model
As you can see, this results in the relationship being recorded twice, once for the foreign key in the office table to point to the manager, and also in the staff table pointing to the office that staff member works for.
I have looked into alternative ways of modelling this but am still a bit lost. Please could someone advice a suitable way of modelling this, or if my method is acceptable for the scenario.
Many thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这并不是说“关系被记录了两次”，而是这些表之间实际上有两个关系 - 这完全没问题。我唯一关心的是，经理是否可以与他所担任的经理属于同一个办公室？ （与此相关的是：每个员工都有一个办公室，并且每个办公室都有一位经理，而经理又是员工，这真的是真的吗？）如果是这样，那么你就有了循环依赖：在办公室存在之前，您无法设置经理的办公室，但在经理存在之前，您无法设置办公室的经理。只要一个或另一个字段可为空，您就可以通过应用程序逻辑解决此问题（
INSERT一个，然后INSERT另一个，然后UPDATE第一个），但是有点难看。但如果这些关系确实存在，那么你就无能为力了。It's not that "the relationship [is] recorded twice", but that you actually have two relationships between these tables — which is perfectly fine. My only concern is, can a manager belong to the same office that (s)he's the manager of? (And relatedly: is it really true that every staff member has an office and every office has a manager who is a staff member?) If so, you have a circular dependency: you can't set the manager's office until the office exists, but you can't set the office's manager until the manager exists. As long as one or the other field is nullable, you can work around this by application logic (
INSERTone, thenINSERTthe other, thenUPDATEthe first one), but it's a bit ugly. But if those are the relationships that exist, then there's not much you can do about it.从 SQL 角度来看，这样的循环关系是有效的，但它会导致一些事情变得复杂。
例如，当您备份和恢复数据时，您必须将外键约束之一的创建推迟到恢复数据之后。因为如果您在填充表之前创建约束，则在恢复办公室的经理之前，您无法恢复其办公室，并且在恢复办公室的经理之前，您也无法恢复办公室。
解决此问题的另一种方法是使用布尔列
Staff.IsManager，而不是使用Office.Manager外键列，该布尔列对于经理为 true，但对于所有其他员工为 false在给定的办公室。A circular relationship like this is valid, from an SQL perspective, but it causes some things to become complex.
For instance, when you back up and restore the data, you have to defer creation of one of the foreign key constraints until after you restore the data. Because if you create the constraints before you fill the tables, you can't restore the manager of the office before you restore his office, and you can't restore the office before you restore its manager.
Another way of solving this instead of using a
Office.Managerforeign key column is to use a boolean columnStaff.IsManagerwhich is true for the manager but false for all other staff in the given office.我觉得没关系，因为尽管关系中的表是相同的，但关系实际上是非常不同的。您可以推断经理在她管理的办公室工作，这是正确的，但这只是一个域规则，并且不会使数据库设计的这一部分反规范化。
如果您想摆脱双重关系，您可以随时创建一个 Managers 表，其中包含 Staff 和 Office 的外键。
It is ok by me because regardless of the fact the the tables in the relationships are the same, the relationships are actually very different. It is correct that you can deduct that a manager works in the the office she manages, however this is rather a domain rule and doesn't de-normalize the this part of the database design.
if you want to get rid of the double relationship you can always create a Managers table which will contain foreign key to Staff and Office.
当您想要添加新办公室时，这似乎很尴尬。一个办公室必须有一个经理，所以你首先要培养一个经理。但经理是员工，必须有办公室，所以你首先要打造办公室。
要打破这个循环，您需要允许其中一个暂时为 NULL 或其他不真实的值，然后修改您第一个创建的值以引用第二个值。不是不可能，只是尴尬。
如果我设计这个，我可能会有一个单独的“管理”表，将办公室与经理联系起来。
This seems awkward when you want to add a new office. An office must have a manager, so you need to make the manager first. But a manager is a staff member, which must have an office, so you need to make the office first.
To break this cycle, you need to allow one to be temporarily NULL or some other untrue value, and then modify whichever you create first to refer to the second. Not impossible, just awkward.
If I were designing this, I would probably have a separate "manages" table, relating offices to mangers.
没关系，只是为了表明关系。但请注意，这可能会导致无限循环。
检索数据时可能会发生这种循环（我曾经遇到过同样的情况），因此最好对其进行规范化并避免这种 2 关系问题。
This is ok, just to show the relationship. But be aware that this may lead to an infinite loop.
This loop can happen When retrieving data (I faced the same situation once), so it's better to normalize this and avoid this 2-relationships issue.