7. ENTITY RELATIONSHIP MODELLING
A relationship is recursive if the same entity type appears more than once. A typical business example is a rule such as “an employee supervises other employees”. The supervises relationship is recursive; each instance of supervises will specify two employees, one of which is considered a supervisor and the other the supervised. In the following diagram the relationship symbol joins to the Employee entity type twice by two separate lines. Note the relationship is one-to-many: an employee may supervise many employees, and an employee may be supervised by at most one other employee.
With recursive relationships it is appropriate to name the roles each entity type plays. Suppose we have an instance of the relationship:
John supervises Terry
Then with respect to this instance, John is the supervisor employee and Terry is the supervised employee. We can show these two roles that entity types play in a relationship by placing labels on the relationship line:
This one-to-many supervises relationship can be visualized as a hierarchy. In the following we show five instances of the relationship: John supervises Lee, John supervises Peter, Peter supervises Don, Peter supervises Mary, and John supervises Noel.
In the above example note the participation constraint at both ends of supervises is optional. This must be the case because some employee will not be supervised, and, for some employees there are no employees they supervise.
Generally recursive relationships are difficult to master. Some other situations where recursive relationships can be used:
- A person marries another person
- A person is the parent of a person
- A team plays against another team
- An organizational unit reports to another organizational unit
- A part is composed of other parts.