A unique kind of relationship is the reflexive relationship—a relationship that shares the same source and destination entity. Reflexive relationships are important when modeling data in which an instance of an entity points to another instance of the same entity.
For example, to show who a given person reports to, you could
create a separate manager entity. It would be easier, however, to
just create a reflexive relationship. The managerID attribute
is the relationship’s source key whereas employeeID is
the relationship’s destination key. Where a person’s managerID is
the employeeID of another
employee object, the first employee reports to the second. If an
employee doesn’t have a manager, the value for the managerID attribute
is null in that employee’s
record.
Figure
5-3 shows this relationship as it exists in the
Employee table. The row with NAME = Brent references
the row with NAME = K. Boss since
the manager relationship is defined with MANAGER_ID as
the source key and EMPLOYEE_ID as
the destination key.
Figure 5-3 Reflexive relationship table
Reflexive relationships can represent arbitrarily deep recursions.
So, in the model above, a person can report to another person who
reports to yet another person, and so on. This could go on until
a person’s managerID attribute
is null, which denotes
that person reports to no one.