Recursive Relationships in ER diagrams

A relationship in which the same entity set participates more than once in different roles is called a recursive relationship or unary relationship. In a relational database, a recursive relationship is implemented using a self-referencing foreign key.

• A self-join represents the same entity set in two roles, such as Supervisor and Subordinate, and connects related records within the same table.
• Used to represent hierarchies or networks, where an entity can be connected to other entities of the same type.

Example: In an organizational chart, an employee can have a relationship with other employees who are also in a managerial position. Similarly, in a social network, a user can have a relationship with other users who are their friends.

Cardinality in Recursive Relationship

We use cardinality constraints to specify the number of instances of an entity set that can participate in a relationship. For example, in an organizational chart, one supervisor can supervise many subordinates, and each subordinate can report to at most one supervisor. This is represented as a one-to-many (1:N) relationship between the employee entity and itself. Let us suppose that we have an employee table, where:

• A manager supervises a subordinate.
• Every employee can have a supervisor except the CEO
• There can be at most one boss for each employee.
• One employee may be the boss of more than one employee.

Here, REPORTS_TO is a recursive relationship on the Employee entity set. The Employee entity set participates in two roles: Supervisor and Subordinate. These are called role names. Since only one distinct entity set (Employee) participates in the relationship, the degree of REPORTS_TO is 1, making it a unary relationship.

Note: Although the Employee entity set appears twice in the ER diagram, it is still only one distinct entity set. The two occurrences represent different roles Supervisor and Subordinate. Therefore, the relationship has degree 1, not degree 2.

• The minimum cardinality of the Supervisor role is 0 because the lowest-level employee (e.g., a subordinate) may not manage anyone.
• The maximum cardinality of the Supervisor role is N, as an employee can manage many subordinates.

For the Subordinate role:

• The minimum cardinality is 0, as the CEO, for example, is not a subordinate to anyone.
• The maximum cardinality is 1, as a subordinate can have only one manager.

Note: In this case, neither of the participants has total participation since the minimum cardinality for both roles is 0. Therefore, the relationship is represented with a single line (not a double line) in the ER diagram

Implementing a Recursive Relationship

To implement a recursive relationship, a foreign key of the employee’s manager number would be held in each employee record. A sample table would look something like this:- 

Emp_entity( Emp_no,Emp_Fname, Emp_Lname, Emp_DOB, Emp_NI_Number, Manager_no);

Manager no - (this is the employee no of the employee's manager)

Example:

CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employee(id)
);

Here, the employee table has a foreign key column called manager_id that references the id column of the same employee table. This allows you to create a recursive relationship where an employee can have a manager who is also an employee.

Sample Employee Table Structure:

In this table:

• Manager_no refers to the Emp_no of the employee’s manager.
• The CEO (employee 1 in this example) does not have a manager, hence their Manager_no is NULL.