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 ?

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

(And probably a CHECK to ensure starting end 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...

...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.

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.

Top