Table of Contents

About

This article is about the one-to-many relationship in a relational model.

Example

For example, in a geography schema having tables Region, State, and City:

  • many states are in a given region, but no states are in two regions.
  • Similarly, there are many cities, a city is in only one state (cities that have the same name but are in more than one state must be handled slightly differently).

The key point is that each city exists in exactly one state, but a state may have many cities, hence the term many-to-one

Usage: Hierarchy / Grouping relationship

One-to-many relationships are used to represent:

Modeling

Dimensional Modeling

In cube modeling (dimensional schema), this relationships:

Binary Relationship

This is a binary relationship with 1:n mapping in which:

  • the existence of the n entities on one side of the relationship
  • depends on the existence of one entity on the other side of the relationship.

Implementation

Relational database

Foreign Key Constraint

In relational databases, these many-to-one relationships are enforced and materialized with foreign key/primary key constraint.

This is why a One-to-many is also known as a primary Key-Foreign Key Relationship.

Parent ID in child's primary key?

In a child relationship, we may want to give the child table a primary key composed of the parent ID column.

For instance:

Parent Id Column Child Id Column
1 1
1 2
2 1
2 2
… …

Advantage:

  • The sequence of the child is smaller

Disadvantage:

  • The foreign keys constraints on this table are now bigger because they need now 2 columns.
  • The update statements need to include 2 columns in place of 1.
  • If the relationship is modified between the child and the parent, you need to modify the relationship in all foreign children, leading to possible update errors.

Conclusion: uses a single ID value to identify a single object

In a mapping - Orphan Management

An orphan instance is created while loading data into an entity if you insert an instance that does not have an existing parent instance.

For example, you load an article into a fact table but this article code is not present in the article dimension. This record is an orphan record.

When loading orphan instance, you can specify the integrity policy used:

  • Reject the instance
  • Or set an other instance value to regroup the orphan instances