CODEWITHSUNDEEP
database-designentity-relationshiper-diagram
ResVict
ResVict

Reputation: 332

Entities with a strict 1-2 relationship

I am trying to understand how do we model and define the cardinality of two entities when the relationship is of exact nature. Lets say 1 to 2.

For an example, if I have entities Journey and Location. Now each journey has 2 locations, a starting location and an ending location.

So how do we model this relationship and put the cardinality on each entity (Journey and Location).

Simple google search results in simple 1-1, 1-m, and m-n relationship explanations. But how do we limit them to be exactly 1-2 ?

Upvotes: 2

Views: 3044

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Your example is not "1 to 2", but "N to 2", and you'd model it simply by having two foreign keys:

enter image description here

(And probably a CHECK to ensure that starting and ending locations differ.)

If you really wanted "1 to 2", in a sense that any given location is always connected to just one journey, you'd have to do something like this...

enter image description here

...and use a DBMS that supports deferred constraints, so you can break chicken-and-egg problem when inserting new data.

On a DBMS that doesn't support deferred constraints, you could make starting and ending locations NULL-able, but then it would no longer be "1 to 2", but "1 to 0..2" relationship.

Upvotes: 6

nvogel
nvogel

Reputation: 25526

Different modelling notations represent this in slightly different ways but normally the number (variously called the degree, range or multiplicity) is written near the end of the relationship line where it applies. It is most common in UML notation. In IDEF1X terminology it is sometimes called "N Cardinality".

In SQL databases it is for most practical purposes virtually impossible to implement the constraints for such relationships where the minimum required multiplicity is greater than zero. That probably explains why these relationships are uncommon in database models but more common in class diagrams. Object oriented systems can in principle enforce the constraint whereas SQL-based databases typically can't.

Upvotes: 0

Related Questions