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

Data System Architecture


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.


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


Relational database

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

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

Recommended Pages
Data System Architecture
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...
Data System Architecture
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...
Data System Architecture
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...
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...
Obiee Foreign Key Complex Join
OBIEE - Foreign Key or Physical Join

A foreign key repository join is used only to define a Primary Key-Foreign Key Relationships in the Physical Layer. It consist of a column or a set of columns and refers to the primary key column in...
Obiee New Complex Join
OBIEE - How to define a One to Many Relationship

How to model a One to Many Relationship in OBIEE ? Starsnowflake schemasdimensional schemaone-to-many relationshipsdimension tablesfact tables You must in the logical business model from OBIEE designed...
Bi Server Architecture With Client
OBIEE - How to model the different relationships ?

The relationships modeling in OBIEE. Star schemas and snowflake schemas work well for modeling a particular part of a business where there are one-to-many relationships between the dimension tables...

Share this page:
Follow us:
Task Runner