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

> (Data|State) Management and Processing > Data Modeling (Relational Database, Code, Graph, Text)

1 - About

One-to-many is also known as :

  • to-many
  • Many-to-one
  • Primary Key-Foreign Key Relationships
  • Parent-Child

A many-to-one relationship is where one entity (typically a column or set of columns) contains values that refer to another entity (a column or set of columns) that has unique values.

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

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 for 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.”


3 - Implementation

3.1 - Relational database

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

3.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