Recursive Relationships

A recursive relationship (also called self-referencing relationship) occurs when an entity is related to itself.

• In other words, an entity has a relationship with another instance of the same entity type.
• Useful when modeling hierarchies or networks within the same entity set.

Key Points of Recursive Relationships

• Entity participates more than once in the relationship.
• Each participation plays a different role in the relationship.
• Requires the use of role names to distinguish how the same entity is involved.

Types of Recursive Relationships

1. One-to-One (1:1) → One instance relates to only one other instance of the same entity.
   • Example: In a company, each employee has one mentor (and that mentor is also an employee).
2. One-to-Many (1:N) → One instance relates to many others of the same entity.
   • Example: A manager manages many employees, but each employee has only one manager.
3. Many-to-Many (M:N) → Many instances relate to many others of the same entity.
   • Example: In a social media app, a user can follow many users, and can also be followed by many users.

Example of Recursive Relationships

• Entity: Employee
• Attributes: Emp_ID, Name, Position
• Relationship: Manages

1. One employee can manage many employees.
2. Each employee is managed by exactly one manager.

Textual ER Representation:

[EMPLOYEE] ──(Manages)── [EMPLOYEE]

Attributes:
Emp_ID (PK)
Name
Position

• The same entity (Employee) appears twice in the relationship.
• We use roles → “Manager” and “Subordinate”.

How Recursive Relationships are Implemented in Databases

Recursive relationships are usually implemented by:

1. Foreign Key referencing the same table.

   • Example: In Employee, a column Manager_ID references Employee(Emp_ID).

   EMPLOYEE
   -------------------
   Emp_ID (PK)
   Name
   Position
   Manager_ID (FK → Emp_ID)

   This way, each employee record points to their manager within the same table.

2. Junction Table (for M:N recursive relationships).

   • Example: For User follows User, create a Follows table:

   FOLLOWS
   -------------------
   Follower_ID (FK → User_ID)
   Followed_ID (FK → User_ID)

   This allows many-to-many self-referencing