Relational Data Modeling - One-to-many / May-to-one Relationship

1 - About

one-to-many cardinality in a relational modeling environment.

A One-to-many is also known as Primary Key-Foreign Key Relationships

In relational databases, these many-to-one relationships are often enforced by foreign key/primary key relationships.

In a dimensional schema, this relationships typically are between fact and dimension tables and between levels in a hierarchy.

This relationship is often used to describe classifications or groupings and the many-to-one relationships that define the hierarchy become levels in a dimension

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.

3 - Example

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

  • there are many states that 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

4 - Implementation

4.1 - Relational database

In a relational database, a one-to-many relationship is materialized with a foreign key constraint.

4.2 - 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

Data Science
Data Analysis
Data Science
Linear Algebra Mathematics

Powered by ComboStrap