Logical Data Modelling: Simple Relationships

An Entity in isolation offers little value. Every Entity must have at least one Relationship to another Entity in the final version of our Logical Data Model.

In this blog, we will introduce Relationships as another fundamental block in forming our Logical Data Models.

Alongside the tools described in the previous blogs for defining Entities, we will be able to start constructing basic Entity Relationship Models.

Let’s follow the example of the relationship between an Organisation and a Person Entity.

To do this, we will introduce one of the most common patterns in Data Modelling – the Master-Detail Relationship, commonly referred to as Parent-Child Relationships.

To create Relationships, we use a line to link the two Entities. These lines have different syntax depending on the type of Relationship, which we will cover in the following sections. Again, we will be using Barker Notation.

Optionality

Some Relationships are mandatory for an Entity and others optional.

In this case, we can read that the Relationship from Organisation to Person is optional, meaning we may record that an Organisation has a Person related to it, but not mandatory to associate at least one Person with each Organisation instance.

However, each Person must be related to an Organisation i.e. we cannot record a Person instance in the absence of a related Organisation Entity.

Cardinality

The Cardinality of a Relationship refers to the number of Entities that are at that end of the Relationship.

Notice the 0 stems from a Relationship being optional.

Applying these, there may be one or more People related to one and only one Organisation.

We must define the Relationships because they determine the structure and patterns of our data. The different elements used to define these relationships are outlined as follows.

Names

The Relationship name should describe concisely what it represents, in the form of a verb.

Here is the way you read the above:

It is recommended to include the relationship description in one direction only, to declutter the model. The inverse of the relationship can almost always be inferred from the included one. So here, you can remove the ‘produce’ from the model fragment.

Definitions

Further detail should be recorded about the Business and Data Rules that govern how the Relationship gets created, modified or removed.

Examples:

“When a match is underway and a referee determines that a player successfully scores without infringing the laws of the game, a goal is recorded and linked to that specific match and player.”

“Once a player has signed a contract with a team and has been officially registered for the season, they become associated with that team and may be included in match-day squads.”

One feature of the Relationships thus far is that they can be transferred. Barker introduced notation to denote that a relationship cannot be transferred.

Here, the diamond indicates that the treatment of a specific injury type cannot be transferred to another staff member.

If a relationship cannot be transferred from one Master Entity instance to another, this is an important detail that should be recorded.

In most situations, it is fine for the Relationship to be transferrable.

However, this implicit transferability must be treated with caution. Once transferred, the Data Model has no way of telling who previously treated the injury. Should this need to be tracked, another approach will be needed.

Even though an implemented system may have an auditing capability to capture this, we must not factor that into our Logical Data Models. If the team needs to know the history of the staff who treat certain injuries, then this should be incorporated into the Logical Data Model structures.

The next blog in the series will explore more complex Relationships, including how These will include how to model multiple and many-to-many Relationships.