Skip to content Skip to main navigation Skip to footer

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 .
Relationships in an 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?
Recursive Relationship

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
Relationship Example

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?
Hierarchy Versus Recursive Relationship

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”
Drawing Convention

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.
Automobile Manufacturing Business Scenario

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

Was This Article Helpful?

1
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *