2

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 ?

ResVict
  • 312
  • 2
  • 12

2 Answers2

5

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.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • The notation is not important here. Similarly, implementation is not in focus at the moment. The main concept/design is. A journey has two locations associated with it, a starting location and an ending location. How do we express this relationship. In ER clearly there are two entities (Journey and Location) but how to show that there are two locations with a specific name are associated with Journey? In OO , two location objects can be used as members of Journey objects, but how to represent it in a design? – ResVict Mar 01 '15 at 09:23
  • 1
    Journey with two required attributes: JOURNEY {Start,End}. The key would obviously be {Start,End}. Isn't it as easy as that? – nvogel Mar 03 '15 at 20:41