解决多对多的关系 [英] Resolve many to many relationship

问题描述

有没有人有一个过程或方法用于确定如何在关系数据库中移除多对多关系？这是我的情况。我有一组联系人和一组电话号码。每个联系人可以与多个电话号码相关联，并且每个电话号码可以与多个联系人相关联。

Does anyone have a process or approach to use for determining how to resove a many-to-many relationship in a relational database? Here is my scenario. I have a group of contacts and a group of phone numbers. Each contact can be associated with multiple phone numbers and each phone number can be associated with multiple contacts.

这种情况的一个简单的示例是具有两个雇员& e2），一条主语音线路（v1），一条专用语音线路（v2）。 e1是CEO，所以他们有自己的私人语音线，v1，但他们也可以通过调用主线，v2，并要求CEO。 e2只是一个员工，只能通过调用v2才能到达。

A simple example of this situation would be an office with two employess (e1 & e2), one main voice line (v1), one private voice line (v2). e1 is the CEO so they have thier own private voice line, v1, but they can also be reached by calling the main line, v2, and asking for the CEO. e2 is just an employee and can only be reached by calling v2.

所以，e1应该与v1& v2。 e2应与v2相关。 Conversly，v1应与e1相关，v2应与e1& e2。

So, e1 should be related to v1 & v2. e2 should be related to v2. Conversly, v1 should be related to e1 and v2 should be related to e1 & e2.

这里的目标是能够运行查询，例如什么数字可以达到和什么员工可以在v2达到等。 。我知道答案会涉及一个或多个中间表，但我似乎不能确定确切的架构。

The goal here is to ge able to run queries like "what numbers can e1 be reached at" and "what employees can be reached at v2", etc.. I know the answer will involve an intermediate table or tables but I just can't seem to nail down the exact architecture.

推荐答案

员工：

eID, eName
1, e1
2, e2

PhoneNumbers：

PhoneNumbers:

pID, pNumber
1, v1
2, v2

EmployeePhones：

EmployeePhones:

eID, pID
1, 1
1, 2
2, 2

那么你内联接。如果您需要找出在（t-sql）可以到达的数字e1：

then you inner join. if you need to find out what number(s) e1 can be reached at (t-sql):

SELECT E.eName, P.pNumber 
FROM   dbo.Employees E 
INNER JOIN dbo.EmployeePhones EP ON E.eID = EP.eID 
INNER JOIN dbo.PhoneNumbers P ON EP.pID = P.eID 
WHERE E.eName = 'e1'

我相信这应该工作...）

I believe this should work (testing it right now...)

编辑：给我几分钟的时间输入，对不起重复...

Took me a few minutes to type up, sorry for duplication...

这篇关于解决多对多的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！