Resolve many to many relationship

Question

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.

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.

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.

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.

Answer 1

1. Normalize
2. Best Practices on Referential Integrity
3. Check this - http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/327/Need-Some-More-Sample-Databases.aspx

Answer 2

You don't need any temp tables for the query. There is an intermediary table for the mapping.

numbers_tbl
-----------
nid   int
number    varchar

employees_tbl
-----------
eid    int
name  varchar

employee_to_phone_tbl
-----------
eid    int
nid    int

How can I call Bob?

select *
from employees_tbl e
inner join employee_to_phone_tbl m
  on e.eid = m.eid
inner join numbers_tbl n
  on m.nid = n.nid
where e.name = 'Bob'

Who might pickup if I call this number?

select *
from numbers_tbl n
inner join employee_to_phone_tbl m
  on m.nid = n.nid
inner join employees_tbl e
  on e.eid = m.eid
where n.number = '555-5555'

Answer 3

Employees:

eID, eName
1, e1
2, e2

PhoneNumbers:

pID, pNumber
1, v1
2, v2

EmployeePhones:

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

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...)

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

Answer 4

After just a little more thought, here is what I came up with. It probably goes along with the approach AviewAnew is thinking of.

employees
id (index)
name

numbers
id (index)
number

relations
employees.id (index)
numbers.id (index)

---

employees
1 : e1
2 : e2

numbers
1 : v1
2 : v2

relations
1 : 1
1 : 2
2 : 1

Is this the best/only approach?

Answer 5

Others have explained the schema, but I'm going to explain the concept. What they're building for you, the table named EmployeePhones and employee_to_phone_tbl, is called an Associative Entity, which is a type of Weak Entity.

A Weak Entity does not have its own natural key and must instead be defined in terms of its foreign keys. An Associative Entity exists for the sole purpose of mapping a many-to-many relationship in a database that does not support the concept. Its primary key is the grouped foreign keys to the tables it maps.

For further information on relational theory, see this link