Reflexive Association

Introduction
This is part 10 of my series, Database Essentials. Database Essentials is division 1 of a set of tutorials I have on Database. In this part of the series, we look at what is called the Reflexive Association. This is another kind of relationship we have to learn. Our aim is to discover the nature of this association and not to know how to derive it or when to use it or how to use it.

Meaning of Reflexive Relationship
A reflexive association is a relationship from a class (table) back to itself.

Illustration
You will have to open the following link in a browser tab in order to see the table and diagram for this tutorial:

http://www.cool-mathematics.biz/diagrams/reflexive-relationship.htm

The most common business situation for the reflexive relationship is with an employee table. Table 10.1 shows a sample Employee table. In this table there is an EmployeeID column and a Manager column. A relationship exists between employees and managers. A manager is also an employee and this makes the relationship reflexive.

There are three managers in the table. These managers are the CEO, the Sales Manager and the Factory Manager. Information for each employee is in a row. In the manager cell of each row, you have the ID of the employee's manager. Since there are three managers among the employees, you can only find three possible values (IDs) in the Manager column.

Business Rule
The business rule for the above table is that each employee can only have one manager. This means a one-to-many relationship between manager and employee. It is possible to have a company where each employee can have more than one manager; however, I will not address that.

Class Diagram
Fig 10.1 shows the class diagram for the above table.

Conclusion
A reflexive association is a relationship from a class (table) back to itself.

Comment
In part five of the series we talked about one-to-one, one-to-many and many-many relationships. We said that a many-to-many relationship should be converted into one-to-many relationships. In part 6 of these tutorials, we saw the N-ary relationship. This relationship is characterized by one-to-many relationships. The N-ary relationship is a number of one-to-many relationships exiting in a particular way. In part 7 we saw the Aggregation Relationship. The Aggregation relationship is characterized by a one-to-many relationship. In part 8 we saw the Composition relationship; this relationship is characterized by one-to-one relationships between the bicycle table and any of the part tables. However, if all the parts were in one table (say a component table), then the composition relationship would be characterized by one-to-many relationship between the bicycle table and the component table. In part 10 we saw the Generalization relationship, which is characterized by one-to-one relationship. In this part of the series, we have seen a reflexive relationship characterized one-to-many relationship.

We can see that each of the relationships from part 6 to 10 (N-ary, aggregation, composition, generalization and reflexive) is either a one-to-one or one-to-many relationship existing in a particular way. Now this is my conclusion about relationships: The one-to-one and one-to-many relationships are core relationships on which other relationships are formed. I do not know if this statement has been quoted elsewhere. I have read 5 books on relational database and I never saw this conclusion (statements) in the books.

Let us stop here and continue in the next part of the series.

Chrys