Logical Data Modelling: Identifying Entities with Relationships

Following from the previous blogs, relying on Attributes alone cannot always adequately define the Unique Identifier for every Entity. For instance, some Entities will not have any Attributes!

For many Entities, it’s Relationships will be needed to uniquely identify it.

This blog will describe how to use Relationships to form a part of an Entity’s Unique Identifier.

To ensure the uniqueness of an Entity’s instances, its Relationship to one and only one Master instance can be used.

Relationships used in this way are called Identifying Relationships.

In Barker notation there are two key features to point out:

The Identifying relationship can help ensure uniqueness of the Detail instances, but one on its own cannot fully uniquely identify it.

Given the Relationship Cardinality is many at the Detail End, there could be more than one Detail instance associated with each Master instance. So, we still need to distinguish one from another.

In addition to an Identifying Relationship, a Detail Entity must have as a minimum either:

The first option to complete the Unique Identifier of an Entity is an additional Identifying Attribute to the Detail Entity. Which in turn follows one of two patterns:

It is possible to use a numeric Attribute which increments with the number of Detail instances. Typically, it starting and incrementing by 1.

Yet again, there are two patterns for this:

Surrogate Key Attribute

Here, the Detail instances (the line items) have a Line Number Attribute added. Notice, the extra attribute is clearly not globally unique – it repeats across different orders.

Instead, each Order Line Item is uniquely identified by the combination of the Line Number and its Identifying Relationship to one and only one Order (the master Entity).

This does the job but contravenes a fundamental principle that the Identifying Attribute should have real world meaning. An alternative, could be to use the time when the Line Item was created. The use of a surrogate key is used in the absence of better alternatives for this example.

These include:

Natural Attribute

Although rare, sometimes the additional Identifying Attribute can be ‘Natural’.

An example that illustrates this is when uniquely identifying a floor of a football Stadium.

Stadium floors exist within the context of a single Stadium, for instance the Valley. However, how do you uniquely identify the ground floor of the Valley across all Stadiums, given that all stadiums will have a ground floor?

Again, the Detail Entity, in this case the Stadium level, is uniquely identified by having a relationship to one and one Master Entity, the Stadium, and additionally an identifying attribute, the floor number.

Notice we use the Floor Number rather than the Floor Name as the name is subject to changes in the future.

There are many examples where the Detail Entity already has one or more other Attributes that can act in combination with an Identifying Relationship to ensure uniqueness and don’t follow the sequenced-based identifier seen above.

Returning to our Organisation Entity covered in Logical Data Modelling: Identifying Entities.

We saw how an organisation can have a Unique Identifier that is formed of a Registration Number and Registration Authority Name.

However, this was before we learnt that this Entity would need to be normalised, signified by the repeating Attribute value of ‘Companies House’.

Normalising this and adding our Identifying Relationship syntax we arrive at the following:

Given that the registration numbers could overlap between different Registration authorities, it is the combination of the Registration Number and the Identifying Relationship to a single Registration Authority that guarantees the Organisation’s uniqueness.

Rather than using an additional Attribute to uniquely identify an Entity, we can use an additional Identifying Relationship.

To explain how an additional Identifying Relationship can be used as part of a Unique Identifier. Let’s return to our simple Team and Player model covered in the previous blog: Logical Data Modelling: Complex Relationships.

If we want to extend this to allow us to include other members of staff and apply normalisation, we can arrive at the following:

Bringing in this new Employment Entity will allow more flexible modelling of real life scenarios – as we will see in the following sections.

We can see that the Employment Entity is uniquely identified by being associated with one and only one Person and to one and only one ‘Organisational Unit’.

In Logical Data Modelling: Normalisation, we introduced a standard resolution Tool for M:M Relationships using an intersection Entity.

What wasn’t included, was that the two new Relationships must be Identifying and hence form at least part of the Unique Identifier of the Entity.

It follows from each instance of the Intersection Entity representing a unique instance of a Relationship between the two original Entity instances.

So, we will amend our resolution pattern by adding two Identifying Relationships as follows.

Following from our previous example, how do we model the situation in which Chris Powell left as a player and then rejoined as a player?

Our current model wouldn’t be able to uniquely identify these employments. We have now encountered a case where the minimum requirements to create Unique Identifiers using Identifying Relationships do not suffice.

We must always ask ourselves if the Relationship represented by the Intersection Entity can exist more than once. In this case, it is not enough to define the uniqueness of each of these Employments only using the Identifying Relationships.

The resolution to this is as follows:

Notice we have added Start Date as an Identifying Attribute.

For example:

In the absence of Start Date, the repeating periods of employment between Charlton Athletic and Chris Powell would violate the Unique Identifier.

Adding the Start Date as part of the Unique Identifier resolves this issue of repeated Employments. That is, each intersection instance between an organisational unit and Person can be identified by the Start Date.

Identifying Entities using Relationships adds a powerful tool to the Logical Data Modeller. It enables flexibility when Attributes alone aren’t sufficient, and allows us to capture more realistic, complex business scenarios.

My next blog will introduce some more advanced modelling scenarios such as Fan Traps and Recursive Relationships.