Recursive Relationship
Objectives
This article covers the following objectives:
- Define and give an example of a recursive relationship
- Represent a recursive relationship in an ERD given a scenario
- Construct a model using both recursion and hierarchies to express the same conceptual meaning
Purpose
- Often, roles are organized by hierarchy — at work (manager, crew chief, front-counter clerk, food preparers), or in school (headmaster or principal, assistant headmaster or assistant principal, teachers, staff).
- Hierarchical data is very common.
- Understanding it will help you model:
- Business organizational charts
- Building structures
- Family trees
- and many other hierarchies found in the real world
Relationships in an Organizational Chart
- An Organization’s reporting hierarchy can be represented by this organizational chart .

- An organizational chart can be represented by this data model.
- We create an entity for each level, with a relationship to the next level.
- What are the UIDs for each entity?

Another Relationship Example
- Notice the barred relationships.
- Here you have a case of the cascading UIDs:
- the UID of FLOOR is the combination of FLOOR number and the BUILDING id
- the UID of SUITE is the combination of SUITE number and the FLOOR number and the BUILDING id
- the UID of ROOM is the combination of ROOM id and SUITE number and FLOOR number and the BUILDING id

Hierarchy Versus Recursive Relationship
- Both of these models represent all employees.
- The one on the left is a hierarchical structure.
- The one on the right uses a recursive structure.
- A relationship cannot be both hierarchical and recursive at the same time.
- Which one do you think is better?

Recursive Relationship:
- Recursive relationships tend to be simpler because you are using only one entity.
- Your diagram will be less “busy.”
- However, they are less specific – you cannot have mandatory attributes or relationships unless they are mandatory in all instances of the entity.
Drawing Convention
- The ERD convention to show a recursive structure is drawn as a loop, also known as a “pig’s ear”

Automobile Manufacturing Business Scenario
- For an automobile manufacturing organization, consider all elementary parts, sub-assemblies, assemblies, and products as instances of an entity called COMPONENT
- The model can be created as a simple recursive relationship.

Summary
In this tutorial, you should have learned how to:
- Define and give an example of a recursive relationship
- Represent a recursive relationship in an ERD given a scenario
- Construct a model using both recursion and hierarchies to express the same conceptual meaning
0 Comments