Does anybody possess a process or approach for identifying how you can resove a many-to-many relationship inside a relational database? Here's my scenario. I've got a number of contacts and several telephone numbers. Each contact could be connected with multiple telephone numbers and every telephone number could be connected with multiple contacts.
An easy illustration of this case could be a workplace with two employess (e1 &lifier e2), one primary voice line (v1), one private voice line (v2). e1 may be the Boss so that they have thier own private voice line, v1, however they may also be arrived at by calling the primary line, v2, and requesting the Boss. e2 is simply an worker and may simply be arrived at by calling v2.
So, e1 ought to be associated with v1 &lifier v2. e2 ought to be associated with v2. Conversly, v1 ought to be associated with e1 and v2 ought to be associated with e1 &lifier e2.
The aim here's to general electric in a position to run queries like "what amounts can e1 be arrived at at" and "what employees could be arrived at at v2", etc.. I understand the solution calls for medium difficulty table or tables however i cannot appear to nail lower the precise architecture.
You do not need any temp tables for that query. There's a middleman table for that mapping.
numbers_tbl ----------- nid int number varchar employees_tbl ----------- eid int name varchar employee_to_phone_tbl ----------- eid int nid int
How do 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 basically refer to this as 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'
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 definitely inner join. if you want to discover what number(s) e1 could be arrived at 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'
In my opinion this will work (testing it at this time...)
EDIT: Required us a couple of minutes to type up, sorry for duplication...
Others have described the schema, but I am likely to explain the idea. What they are building for you personally, the table named EmployeePhones and worker_to_phone_tbl, is known as an Associative Entity, which is a kind of Weak Entity.
An Inadequate Entity doesn't have its very own natural key and should rather be defined when it comes to its foreign secrets. An Associative Entity is available for that sole reason for mapping a many-to-many relationship inside a database that doesn't offer the concept. Its primary secret is the arranged foreign secrets towards the tables it maps.
For more info on relational theory, see this link
- Normalize
- Guidelines on Referential Integrity
- Take a look - http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/327/Need-Some-More-Sample-Databases.aspx
After somewhat more thought, here's what I emerged with. It most likely goes together with the approach AviewAnew thinks about the problem of.
employees
id (index)
title
amounts
id (index)
number
relations
employees.id (index)
amounts.id (index)
employees
1 : e1
2 : e2
amounts
1 : v1
2 : v2
relations
1 : 1
1 : 2
2 : 1
Is the very bestOronly approach?