How to correctly create a table that has multiple columns self reference another row?

Question

EmpNo |Designation     | ReportsTo | Room
10    | Director       | NULL      | 1
20    | Senior Manager | 10        | 2
30    | Typist         | 20        | (THIS GUY REFERS TO empno 10 room)
40    | Programmer     | 30        | 4

How do I correctly make EmpNo 30's room refer to his boss', but not make EmpNo 40 refer to his boss'?

Steve Oakes · Accepted Answer · 2016-09-07T10:44:10.447

In the relational model, each row represents a dataset representing a single item and so you do not 'refer' to another row's value.

The concept of an employee sharing a room of another employee is similar in fact to the relationship you already have of an employee having a manager. This could be represented similarly in the table like so:

EmpNo |Designation     | ReportsTo | SharesRoomWith | Room
10    | Director       | NULL      | NULL           | 1
20    | Senior Manager | 10        | NULL           | 2
30    | Typist         | 20        | 10             | NULL
40    | Programmer     | 30        | NULL           | 4

Where a NULL in SharesRoomWith means that an employee has their own room. You could then query the list of employees and their rooms:

-- Employees that have their own room
select
    e.EmpNo,
    e.Designation,
    e.Room
from Employee e
where e.SharesRoomWith is NULL

UNION 
-- Employees that share a room
select
    e.EmpNo,
    e.Designation,
    e.Room
from Employee e

join Employee e2
on e.SharesRoomWith = e2.EmpNo

to get:

EmpNo | Designation    | Room
10    | Director       | 1
20    | Senior Manager | 2
30    | Typist         | 1
40    | Programmer     | 4

This achieves the result that if the Director changes rooms, an update is only made in one location (the room number) and the typist's room will also reflect that change.

Note this design is not fully normalised so may not be an ideal solution, but demonstrates at least how you could represent that relationship.

This however assumes that a single room will never be shared by more then two people. I would probably model this as a many-to-many relationship between employee and room (because room seems to be an entity of its own) — , Sep 07 '16 at 06:48
Why do you say that? Multiple people can exist in one room by all referring the employee in the room. I agree that room could be broken out in the example. — Steve Oakes, Sep 07 '16 at 07:09
If the Typist shares the room with the Senior Manager and the Programmer, how do you store that? Or to put it the other way: how do you decide which row stores the room number and which rows store the "shares room with" information. I think splitting that up into a many-to-many relationship makes things a lot easier. — , Sep 07 '16 at 07:12
I assumed that a person will only exist in one room. Also the intent here seemed to be to capture that an employee is linked to another employees room (the typist sits wherever the director is) rather than just any room that may or may not have multiple people referring it. Many to many emp to room would need to track somehow that the typist has moved rooms, if the director changes room. This was my understanding of the question. — Steve Oakes, Sep 07 '16 at 07:22

score 1 · Answer 2 · answered Sep 07 '16 at 12:11

I'm not sure I fully understand the question. Do you mean the Typist is always in the same room as his or her Director, and if Director moves, the Typist moves too? In that case make the Typists room NULL and use SQL to get his room when you need it:

-- everybody in their own room
SELECT EmpNo, Room 
FROM YourTable 
WHERE Room IS NOT null 
UNION 
-- everybody in their boss's room
SELECT Empl.EmpNo, Boss.Room 
FROM YourTable AS Empl INNER JOIN YourTable AS Boss   
ON Empl.ReportsTo = Boss.EmpNo 
WHERE Empl.Room IS null 
ORDER BY 1

How to correctly create a table that has multiple columns self reference another row?

2 Answers2