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