Unary Relationships

All the relationships discussed so far have been binary relationships having two participants. Unary relationships have only one participantthe relation is associated with itself. The classic example of a unary relationship is Employee to Manager. One's manager is, in most cases, also an employee with a manager of his or her own.

Unary relationships can be of any cardinality. One-to-many unary relationships are used to implement hierarchies, such as the organizational hierarchy implicit in the Employee-Manager relationship. Many-to-many unary relationships, like their binary counterparts, must be modeled with a junction table. Unary relationships can also be optional on the one side, as shown in Figure 3-14. The CEOs of most organizations do not have a manager. (The stockholders don't count unless they're an independent part of the data model.)

Unary relationships are modeled in the same way as binary relationshipsthe candidate key of the primary relation is added to the foreign relation. The only difference is that the primary and foreign relations are the same. Thus, if the candidate key of the Employee relation is EmployeeID, declared on the EmployeeID domain, then you'd add an attribute to the relation called perhaps ManagerID, also declared on the EmployeeID domain, as shown in Figure 3-14

Which raises an interesting issue: For reasons that are probably obvious, no two fields in a table can have the same name. (You shouldn't have two attributes in a single relation with the same name either, but "name" is a slightly slippery concept at the abstract level.) You must, therefore, rename either the primary or foreign key in a unary relationship. Most often, you'll re-name the foreign key. In our example, you would probably call the foreign key "ManagerID".

In fact, you can re-name the foreign key attribute in any relation, and it often makes sense, semantically, to do so. The EmployeeID of the Employee relation, for example, would become CustomerServiceRepID in the Customer relation. The fact that the development environment can make some smart guesses about combining tables based on fields of the same name should not be construed as a recommendation to blindly copy names from one relation to another.