Relationship Examples

The Logic Sample database illustrates classic examples of the kinds of parent/child relationships present in virtually all databases.

The following is a list of the key patterns:

One-to-many/Different

This parent/child relationship is the most common, where there are different objects at both ends of the relationship. The classic example is Customer/Purchaseorder, where Purchaseorder contains a foreign key of the Customer.

One-to-many/Same ("self relationship")

This parent/child 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 Foreign Key head_department_name.

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.

Note: Transitive Closure is a retrieval concept that means get all related objects and sub-objects, recursively.

Many-to-many/Different

This parent/child relationship includes many objects on both sides of a relationship. Relational databases do not support these directly, since a foreign key (like any attribute) can have only one value. You can introduce junction tables to represent these.

For more information about junction tables, see Primary Key Generation.

For example, in the Logic Sample database, Orders can have many Products and a Product can be ordered on many Orders. The example introduces the junction Lineitems table, which has foreign keys to both, as well as additional attributes (such as quantity_ordered).

Many-to-many/Same

This parent/child relationship is less common and rather complicated. 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 two foreign keys to the parent.

For an example of this relationship, see the Bill of Materials Structure.

As things get more complex, there can be multiple relationships between the same tables.

More Information

For more information about the Logic Sample database, see Logic Sample Database.