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

Data System Architecture

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





Discover More
Star Schema
Dimensional Data Modeling - Degenerate Dimension of Fact dimension (i.e. event/header entity)

In a dimensional model, you may find a dimension table: with a cardinality (distinct value is higher than 10,000). cardinality which has a many-to-one relationship with the fact table close to a one-to-one...
Star Schema
Dimensional Data Modeling - Fact Table

A fact table is a table that stores fact where a fact can be seen as the representation of an event. It's also known as: Transactional history Unalterable fact. (The content of this kind of table...
Star Schema
Dimensional Data Modeling - Hierarchy

in dimensional data modeling A hierarchy is a set of levels having many-to-one relationships between each other, and the set of levels collectively makes up a dimension in the form of a tree (A tree...
Star Schema
Dimensional Data Modeling - Relationship

In a dimensional data model, you have mostly a one-to-many relationship between the dimension and the fact table between each level in a hierarchy man-to-many relationship are modeled with a bridge...
Firewalld - Zone

A zone is a grouping of rule configurations known as zone option. A firewall zone is defined for: a connection, an interface or source address binding (range of source addresses) This is a one-to-many...
Jpa Mapping Method
JPA - Entity Annotations

A key feature of EJB 3.0 and JPA is the ability to create entities that contain object-relational mappings by using (metadata) annotations rather than deployment descriptors (orm.xml) as in earlier versions....
One To Many Hello Data Model
JPA - How to define a @One-to-Many relationship ?

To define a one-to-many relationship, the following annotation is used @OneToMany. The Interface with parameters and default values: where: targetEntity is the entity class that is the target...
Erd Entity Instance
Logical Data Modeling - Entity Set (Class, Table)

An entity set is a set of entity. An entity set is implemented: in a relational database by a table and an entity by a row in code by a Class and an instance by an object entity of a language...
Data System Architecture
Logical Data Modeling - Group (Grouping)

group (or grouping) refers to the fact of putting things (elements) together in a container. This is a one-to-many relationship. In data modeling, you group: attributes in: an entity a relationship...
Data System Architecture
Logical Data Modeling - Hierarchy Relationship

A hierarchy is a composed relationship. It means that it represents a succession of one-to-many relationship that build a tree between: label values or entity A hierarchy is represented in a relational...



Share this page:
Follow us:
Task Runner