Recursive Relationships

April 2024 · 2 minute read
Recursive Relationships

Previous Topic: N-ary RelationshipsNext Topic: Subtype Relationships

Recursive Relationships

An entity can participate in a recursive relationship (also called fishhook) where the same entity is both the parent and the child. This relationship is an important one when modeling data originally stored in legacy DBMSs such as IMS or IDMS that use recursive relationships to implement bill of materials structures.

For example, a COMPANY can be the parent of other COMPANYs. As with all non-identifying relationships, the key of the parent entity appears in the data area of the child entity. See the following figure:

The recursive relationship for COMPANY includes the diamond symbol to indicate that the foreign key can be NULL, such as when a COMPANY has no parent. Recursive relationships must be both optional (diamond) and non-identifying.

The “company-id” attribute is migrated through the recursive relationship, and appears in the example with the rolename “parent-id.” There are two reasons for this. First, as a general design rule, an attribute cannot appear twice in the same entity under the same name. Thus, to complete a recursive relationship, you must provide a rolename for the migrated attribute.

Second, the attribute “company-id” in the key, which identifies each instance of COMPANY, is not the same thing as the “company-id” migrated through the relationship, which identifies the parent COMPANY. You cannot use the same definition for both attributes, so the migrated attribute must be rolenamed. An example of possible definitions follows:

company-id

The unique identifier of a COMPANY.

parent-id

The “company-id” of the parent COMPANY. Not all COMPANYs have a parent COMPANY.

If you create a sample instance table, such as the one that follows, you can test the rules in the relationship to ensure that they are valid.

COMPANY

company-id

parent-id

company-name

C1

NULL

Big Monster Company

C2

C1

Smaller Monster Company

C3

C1

Other Smaller Company

C4

C2

Big Subsidiary

C5

C2

Small Subsidiary

C6

NULL

Independent Company

The sample instance table shows that Big Monster Company is the parent of Smaller Monster Company and Other Smaller Company. Smaller Monster Company, in turn, is the parent of Big Subsidiary and Small Subsidiary. Independent Company is not the parent of any other company and has no parent. Big Monster Company also has no parent. If you diagram this information hierarchically, you can validate the information in the table, as shown in the figure below:

ncG1vNJzZmirY2Ourq3ZqKWar6NjsLC5jp6psKGeYsK0e7Kup6mnoql8eoGOfHhkfYKstq93o5qrmmN9pLGmuMSrYqtxW2p6hpq0aHmop5uotaa4xZh9oqSVqHyJoKyFZn6Kp567Zn6PiK2eqqaesrh7kW5rcGlnY7W1ucs%3D