Logical Data Modelling – Advanced Relationship Modelling

This blog will introduce a selection of more advanced relationship modelling scenarios, needed to model more complex real life business scenarios.

We will start with the ‘Fan Trap’ which is often demonised, but can form a powerful modelling tool when used correctly. We then explore a range of recursive Relationship scenarios.

Fan traps are a particular variation of a many-to-many Relationship pattern we are already familiar with from previous blogs in the series, but they are often cited as a problem.

However, when used correctly it can serve as a very powerful tool.

It occurs when two Detail Entities are related to the same Master Entity.

Take a look at the following:

What this presents is each staff member can have one or more roles, and each of these roles can provide authority for one or more Entitlements.

For example, a staff member has a role of ‘Physio’, which gives the entitlements of using the staff canteen and giving massages.

Notice, the Role Entity is linked to two Detail Entities, so forms the Master Entity in a Fan Trap. Staff Role and Role Entitlement act as typical Intersection Entities and aren’t always involved in a Fan Trap.

If we want to find out which entitlements a specific person has, it can provide the answer, but it is a set of Entitlements. Equally, it can answer who have a specific entitlement, with the result being a set of staff.

So this pattern provides a mapping of a set to a set.

However, what it cannot do is provide a mapping of an individual staff member’s roles to a specific role entitlement.

If we need to map each individual Relationship then we need to remodel to the following. Notice there is no longer a fan trap.

To demonstrate this, imagine we need to add a new Entitlement to a specific Role of ‘Physio’.

With the Fan Trap model, we can add the additional Entitlement to the Role and the set of People who are currently allocated this Role would immediately inherit the new Entitlement.

However, with the new model, we would have to find all the People who have been allocated the Role and add them the new Entitlement to each staff member explicitly.

This shows a clear use case of where a Fan Trap can yield significant benefits, if we always want specific roles to have the same entitlements for all staff members.

However, it wouldn’t be the correct way to model the data if we need finer controls over entitlements and don’t want them directly dictated by the roles.

Recursive Relationships are widespread in the real world and likely to appear in your Logical Data Models, so this next section will detail some of their use cases and rules.

Recursive Relationships are often used to record some sort of hierarchical structure within data.

For example, the above model fragment shows that each staff member may be managed by one and only one other ‘master’ staff member.

Shown diagrammatically we have the following situation:

There are two special Node Types:

These types of nodes stem from both ends of the Relationship being optional. Having either end as Mandatory is illegal, given that there would be no end to the hierarchy.

Another key use of Recursive Relationships in data models is to represent taxonomies. It refers to the way in which things are often categorised.

For example, categorising the football positions:

Positions
Defenders
Left back
Centre back
Left Centre Back
Right Centre Back
Right back
Midfield
Left midfielder
Right midfield
Centre midfield
Attack
Left wing
Right wing
Striker
Goalkeeper

Another less obvious type of Recursive Relationships is materials combining to form a larger material, referred to in Engineering as Bill of Materials structure (BOM).

This describes how a list of components is required to make up a larger component. This larger component can then be a part of a larger component.

For example, think of a football kit. A set of studs is used to help form the underside of a boot which comes together with other components such as the laces and tongue to form a boot. These, matched with the socks, shorts and shirt form a kit.

We have already described how a Recursive Relationship can have no mandatory ends.

A subtle implication of this is that:

– They cannot be used within the Unique Identifier of an Entity.

This is because a requirement for a Relationship to be Identifying, is it having a mandatory end, as described in blog: Logical Data Modelling: Identifying Entities with Relationships.

– No One to One Recursive Relationships

This is an extension of there should not be any One to One Relationships in a fully normalised Logical Data Model.

– No Many to Many Recursive Relationships

Though not illegal, this leads from M-M Relationships needing to be resolved in a Normalised Logical Data Model.

The above shows that not only can each staff member manage one or more other staff members, but now each staff member can be managed by more than one other staff member. This could be valid in certain real world scenarios. Visualised by adding an extra arrow from staff C to staff F for example.

The temporal element of your models should always be considered. If it is possible for a member to stop managing another member, and then this needs to be reinstated, we would need to add Start Date to the Unique Identifier as shown in the model.

Notice that the models discussed do not prevent the Detail Instance being the Master Instance of its Master Instance at any level above it in the hierarchy. Further, currently there is no way to limit the ‘type’ of staff members that can be managers of other staff members.

We need to consider if these situations are valid or if certain rules need to be imposed on these relationships. We will revisit this topic in a future blog that will cover ‘Rule based modelling’.

Advanced relationship scenarios such as Fan Traps and Recursive structures covered in this blog allow modellers to capture complex, real-world business scenarios – but they must be used with care.

Understanding when and how to apply these patterns can greatly improve the accuracy, flexibility, and maintainability of Logical Data Models.