Design pattern: recursive associations
A recursive association connects a single class type (serving in one role) to itself (serving in another role).
Example: In most companies, each employee (except the CEO) is supervised by one manager. Of course, not all employees are managers. This example is used in almost every database textbook, since the association between employees and managers is relatively easy to understand. Perhaps the best way to visualize it is to start with two class types:
In some project-oriented companies, an employee might work for more than one manager at a time. We also might want to keep a history of the employees’ supervision assignments over time. We can model either case by revising the class diagram to a many-to-many pattern:
To produce a list of employees and their managers, we have to join the employees table to itself, using two different aliases for the table name. An outer join is needed to include any employee who is not managed by anyone.
SELECT E.lastName AS "Employee", M.lastName AS "Manager" FROM Employees E LEFT OUTER JOIN Employees M ON E.managerID = M.employeeID ORDER BY E.lastName
In effect, the SQL statement works as if there were two copies of the employees table, as in the first (incorrect) UML diagram. You can visualize rows being joined this way:
The many-to-many structure is handled similarly in SQL. (Note the explicit ordering of the joins specified by parentheses.):
SELECT E.lastName AS "Employee", M.lastName AS "Manager" FROM Employees E LEFT OUTER JOIN (Supervisions S INNER JOIN Employees M ON S.managerID = M.employeeID) ON E.employeeID = S.employeeID ORDER BY E.lastName