Logical Data Modelling: Complex Relationships

In the last blog we introduced Relationships, outlining the basic syntax and introducing a simple many-to-one Relationship.

In this blog we will explore some more complex Relationships.

We will first look at whether multiple Relationships exist between Entities, or whether they are in fact many-to-many Relationship, and how to resolve these. Then, we’ll explore a common ‘Master Detail with Detail Grouping’ set of Relationships.

It is possible to have more than one parallel relationship between the same two Entities, as illustrated:

Figure 1: Multiple Relationships between Person and Club

This indicates that more than one parallel relationship can exist between a Club and a Person. One shows that each person must be employed by a club, the other that one or more people must be a contact for a club.

Whilst this is perfectly valid in our Data Models, be wary that these repeated parallel Relationships can suggest a many-to-many relationship is instead required.

If unsure that more of these parallel relationships will be discovered in the future, it could be a good idea to change the Relationships into a single many-to-many relationship. However, if this risk does not exist, modelling such Relationships as many-to-many can lose some of the explicit clarity of the multiple relationships detailed above.

Relationship transferability (explored in previous blog) and multiple parallel Relationships may indicate there should instead be a many-to-many Relationship existing between the entities.

Illustrating a possible remodelling of the preceding example:

Figure 2: Remodelling as a many-to-many Relationship

This M:M Relationship type occurs frequently at the start of the modelling process, and are fine in high level models, but these must be resolved in our Logical Data Models.

Notice the unclear definition of ‘involved with’ of Person to Club, this tells us straight away this is an area for refinement.

Occurrences of these relationships indicate there is potentially more than one Relationship between the same two occurrences in two Entities.

We need to record which Relationship occurrences are which, and we use Entities to record this.

So, we need to introduce an Entity to record each of the Relationship instances, often called an Intersection Entity.

A standard pattern that can be used for resolving a M:M Relationship like this is:

Figure 3: Resolving a many-to-many Relationship

Given that the intersection Entity records the particular instance of a Relationship between the other two entities, neither of its own Relationships can be optional, irrespective of the original M:M Relationship’s optional definitions.

Also notice that here that the Relationships are One to Many from the original Entities own perspective. However, this not always the case. You should always probe further and ask if it needs to be resolved again.

Another common Relationship pattern is where there is a Master and a Detail Entity but with an additional Entity existing between the two.

We will follow with a new example of a Football Club, Team and Player Entity.

A Football Club employs players, additionally, a football club can operate multiple teams. For instance, Charlton have a first team, reserve team, multiple youth teams and a women’s team.

Figure 4: Master Detail with Detail Grouping

This intervening Entity (here it’s the Team Entity) often represents a grouping of the Detail Entity within the Master Entity.

The model fragment illustrates this situation. We see that a Player must be associated with not only a Football Club, but also a Team. This Team must in turn be associated with a Football Club.

Though not necessarily wrong, it is a pattern that should ask questions. Is this genuinely accurate and if so, what does it truly represent?

Why are there two ways that a Player and Football Club are related?

The first check to carry out is for a potential Relationship redundancy.

If a player must play within a Team, that itself must be defined within one and only one Club, it is possible that the direct relationship between the player and the club is redundant and can be removed.

By this we mean that as soon as the player is associated with a team, then implicitly they are associated to the club.

If the direct Relationship is redundant between the Club and the Player, then the following is a better representation:

Figure 5: Redundant Relationship removed

If the Relationships do not exhibit redundancy, then instead, other common patterns for the Relationships from the Player Entity to its Master Entities include:

a) Both Optional
b) One Optional

a) Both Relationships are Optional

If we want to record the players for both our own football club and those of other clubs, it may make sense to model both Relationships but make them optional.

For players that are external, we don’t necessarily know the association of each player to their Club’s internal structures, for example, we may not know when a player is deemed too good for the U18’s and promoted to the U21’s.
Meaning, the Relationship from the external player to its employing club would need to be mandatory, but the Relationship between the external player and the team needs to be optional.

However, if we want to track the team within which all internal players play, we only need to record the association between the player and the team. So, the direct Relationship between the internal player and club is not required, hence needs to be optional as well.

Figure 6: Optional Master Detail and Optional Detail Grouping

b) One Relationship is Optional – Temporal effect

An interpretation of figure 4 is that we must associate the players with a team.

But, what do we do if we don’t know which team they are associated with at the time we capture their details. For instance, we want to record the significance of Real Madrid signing 16-year-old Brazilian Endrick – but will he go straight into their first team or play for the reserve team?

We will only be able to record this Relationship when it becomes known to us and therefore needs to be optional:

Figure 7: Mandatory Master Detail with Optional Detail grouping

The point here with these examples is that you must be able to spot these nuances and spend the time validating the existence and optionality of each Relationship.

By sticking to a methodical layout for your Data Models, significant benefits can be gained.

It is recommended that you place the Many end of all Relationships to the right or bottom of the model.

This layout reveals several important features:

It should mean that immediately we can see which Entities are the:

By adopting this layout, and through experience, you will notice that to gain an understanding of a model, you will no longer have to trace your finger around it, nor mouth the Entity and Relationship names.

Instead, you will quickly get the gist of the model and begin to spot the structural patterns within it.

The next set of blogs in the series will explore the importance and techniques of uniquely identifying Entities by using their Attributes and or their Relationships, as well as introducing the need and techniques of normalisation.