In a database management system (DBMS), recursive relationship is when the same entity participates in the relationship more than once. This structure allows hierarchical or network-like data representations, such as organizational charts or bill of materials. It facilitates efficient querying and navigation of complex data structures within the database.
The ER diagram below shows recursive relationships between team and supervision with the two possible roles to draw the relationship.
What is a recursive relationship?
If an entity type appears more than once in a relationship, it is said to be recursive. The parent entity or table and the child entity or table in this kind of relationship are identical. Recursive relationships involve the same entity type participating in a relationship type multiple times while taking on a different role each time. An example of a typical business rule states, "An employee supervises other employees."
Here's your problem of the day
Solving this problem will increase your chance to get selected in this company
Skill covered: DBMS/SQL
What does the COMMIT statement do?
Saves changesGroups dataJoins tables
Choose another skill to practice
Introduction to Recursive Relationship in DBMS
In DBMS, a recursive connection is a particular kind of relationship between two entities of the same kind. We already know that a relationship is said to be recursive if an entity type appears more than once in it. A recursive connection in database management systems (DBMS) is a non-identifying link between two entities or tables that symbolises the possibility of one firm owning another.
A recursive relationship is one that exists between two entities that belong to the same kind. This indicates that there is a relationship between many examples of the same entity type. In this case, the same entity type participates in a relationship type more than once, playing a different role in each instance. In other words, there has always been a connection between events that occur in two distinct things. An employee can oversee several employees as one form of a recursive relationship.
Illustration 1
For more clarity, let's look for another example -
A normal relationship looks like this :
In this relationship, entity 1 participate once, and entity 2 also participates once.
But what if we create an entity that relates with itself?
Then that type of relationship is called a recursive relationship. It can be represented as:
Let us consider there is an employee table :
It contains employee ID (empid) and Manager ID (mgrid)
empid
mgrid
11
2
22
3
33
4
44
x
In the above table employee 11 is managed by 2, 22 by 3, 33 by 4, and 44 by x.
We have to understand that in the relationship between employee and manager, there is only one entity, the employee, which is creating a relationship by itself.
So we can show the relation of this particular table like this :
Illustration 2
Let assume an employee (as supervisor) entity, and we will call it on another side as an employee(supervisee). So the relationship between them is supervised.
Let us suppose employee E1 is the supervisor of E2 and E3.
Under supervisor, many employees can work just like E1 is the supervisor of E2 and E3, but E2 cannot have two supervisors and same as E3. We can say that an employee cannot have two bosses in simple language. He can only have only one boss.
Let assume that E4 is the manager of E1 and E6 is the CEO.
So, E1 has a manager above him, then E1 is now a supervisee.
So hierarchy can look something like this:
Cardinality of Supervisor
Cardinality: N (Many) ∵ supervisors can have many supervisees
Cardinality of supervisee
Cardinality: 1 ∵ supervisee can have only one supervisor.
Not every employee needs to be a supervisor, so not all the entity employees need to participate in supervisor. Also, not every employee needs a supervisor because the CEO (E6) cannot have a supervisor.
Let us see how this recursive relationship is shown in the ER diagram.
Note- While writing cardinality in ER diagram, the position is exchanged. As shown above, see positions of 1 and N.
Examples of Recursive Relationship in DBMS
The following are some examples of recursive relationships in DBMS:-
Employee-Manager Relationship
In an organization, employees can have managers who are also employees within the same organization, this means they will be a part of the employee table as well. This can be modeled as a recursive relationship because the foreign key for managerID will point to the employeeID or the primary key of the employee table.
Folder-Subfolder Relationship
In a file management system, folders can contain subfolders, and subfolders can further contain subfolders, this hierarchical relationship is represented using a recursive relationship.
Comment Replies
A commenting system that allows users to recursively reply to comments for eg. Reddit, is another example of a recursive relationship, where a comment can have a parent comment, which is denoted using a foreign key to the same comments table.
Frequently Asked Questions
How are recursive relationships represented in ER Diagram?
We use a self-join, or a join between a table and itself, to depict a recursive relationship. We establish a connection between entities of the same type. We label the two lines between entity and relationship with the two possible roles to draw the situation.
What is the recursive relationship?
The recursive relationship is the relationship among instances (rows) of the same entity type or, we can say, the relationship between two entities of the same type.
What is an ER diagram?
An Entity Relationship Diagram (ERD) represents the relationships between entities in a database. It is usually referred to as an ER Diagram.
What is an example of a recursive relationship set?
On an organizational chart, an employee may have relationships with other employees who are also in managerial positions. This is an example of a recursive relationship set. Similar to this, users of social networks can establish friendships with other users.
What is recursive relationship 1 to 1?
A recursive relationship of one-to-one means that two entities of the same entity type are related to one other. These relationship structures describe hierarchical lines at any level, including the lowest one. One employee, for instance, has just one employee ID.
Conclusion
In this blog, we have learned about Recursive relationships through some illustrations. Recursive relationships are the relationship between two entities of the same entity type.
Also, we learned about how the recursive relationship is shown in the ER diagram.
Visit here to learn more about different topics related to Database management systems.
Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.