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

> (Data|State) Management and Processing > Data Modeling (RDBMS or Code)

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 - In the database

One-to-many is usually enforced by the combination of :

  • a primary key (in our example in the column DEPTNO from the table DEPT)
  • and a foreign key (in our example in the column DEPTNO from the table EMP)

A relational database management system (RDBMS) enforces it :

  • normally either by deleting the foreign key rows as well to maintain integrity,
  • or by returning an error and not performing the delete.

Which method is used would be determined by the referential integrity constraint, as defined in the data dictionary.

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
data/modeling/one-to-many.txt · Last modified: 2017/09/13 21:21 by gerardnico