Examples of Relationships

This article illustrates key parent-child relationships. The Sample database used in the Sample API includes classic examples of the kinds of parent-child relationships present in virtually all databases.
lac40
This article illustrates key parent-child relationships. The Sample database used in the Sample API includes classic examples of the kinds of parent-child relationships present in virtually all databases.
For more information about the Sample database that is used in the 
Sample
 API, see Sample Database.
In this article:
 
 
One-to-many/Different Relationship
The one-to-many/different parent-child relationships is the most common parent/child relationship, where there are different objects at both ends of the relationship. The classic example is Customer/Orders, where Orders contains a foreign key of the Customer.
The following image shows an example of this relationship from the Sample database that is used in the 
Sample
 API:
  CA Technologies  
One-to-many/Same (Self) Relationship
The one-to-many/same parent-child relationship, or self relationship, includes a parent and child of the same type, but not the same instance. The classic example is Departments which have (Sub) 
Departments
, where Department has a 
head_department_name
 foreign key.
For example, the CEO Department has Sub Departments of Sales, and Engineering. Self-relationships recurse. Sales has its own Sub Departments such as International Sales and Domestic Sales.
Transitive Closure is a retrieval concept that means get all related objects and sub-objects, recursively.
The following image shows an example of this relationship from the Sample database:
CA Technologies  
Many-to-many/Different Relationship
The many-to-many/different parent-child relationship includes many objects on both sides of a relationship. Relational databases do not support these relationships directly, since foreign key (like any attribute) can have only one value. You can introduce junction tables, or links, to represent these.
For example, in the Sample database, 
Orders
 can have many 
Products
 and a 
Product
 can be ordered on many 
Orders
. This sample database introduces the 
Lineitems
 junction table, which has foreign keys to both, as well as additional attributes (such as 
quantity_ordered
).
The following image shows an example of this relationship from the Sample database:
CA Technologies  
For more information about junction tables, including how to add junction records, see Automatic Key Generation.
Many-to-many/Same Relationship
This many-to-many/same parent-child relationship is a less common and rather complicated parent/child relationship. The classic example is a Bill of Materials explosion, where Products can be Kits: comprised of multiple Component Parts (which recurses). (Components can have Sub Components.) Conversely, a Component Part can be a part of multiple Kit Parts. You introduce a 
product_billofmaterials
 junction table, which has foreign keys to both endpoints. As things get more complex, there can be multiple relationships between the same tables.
The following image shows an example of this relationship from the Sample database:
CA Technologies