Self-referential relationship table design: one or two tables?

Question

CREATE TABLE Employee 
(
     id        INT,
     boss      INT REFERENCES Employee(id),
     PRIMARY KEY (id)
);

One employee can have many bosses and one boss can have many employees.

Does this table function the same as this two-table design?

  CREATE TABLE Employee 
    (
         id        INT,         
         PRIMARY KEY (id)
    );

Create table ManagerRelation (
  id_from int NOT NULL,
  id_to int NOT NULL, 
  PRIMARY KEY (id_from, id_to),
  FOREIGN KEY (id_from) REFERENCES Employee(id),
  FOREIGN KEY (id_to) REFERENCES Employee(id)
);

The second table ManagerRelation stores ids of workers who have boss-employee relationship.

My question is, are these two design right? If right, are they exactly the same functionally?

In the first case each employee can have only one boss. In the second example, you can have an unlimited umber of relationships (from and to) for each employee. Each employee can have mutiple bosses. — Robin Davies, Mar 05 '16 at 02:48

score 0 · Accepted Answer · answered Mar 05 '16 at 01:21

The two designs are quite different. The first requires that each employee have (at most) one boss, although each boss could have many employees.

The second allows for employees to have more than one boss.

From your description of the problem, the second form is the more appropriate data model. From my understanding of boss-employee relationships, I would expect the first to be correct.

Self-referential relationship table design: one or two tables?

1 Answers1