Logical Data Modelling: Normalisation

This latest blog in the series of Logical Data Modelling explores the concepts of Normalisation – a fundamental technique in producing Logical Data Models, which should be fully normalised in their final form. We will aim to answer how to realise Normalisation is required and how we carry it out. There are several levels of Normalisation. We will follow the most common level, being ‘Third Normal Form’.

When we carefully think about the Attributes in our Entities, we will notice some of them don’t truly belong in the Entity we initially put them in. Put simply, in Normalised Data Models, the Attributes within each Entity are wholly dependent on the Entity instance.

Words to the effect of the following are often used to describe Third Normal Form: ‘The Attributes of an Entity must depend on the Key of the Entity, the whole Key and nothing but the Key’.

What we mean by Attributes ‘belonging’ to the Entity is that their values must be related purely to the Entity instance, implying that their definition must have a dependency on the Unique Identifier of the Entity. We want to identify ‘ill-fitting’ Attributes and recognise the need for new Entities and Relationships. There are two ways that Attributes indicate an additional Entity is required. We will gain either an

Let’s pick up the example of the Organisation Entity from the previous blog on identifying entities. We needed to add Registration Authority to Registration Number to ensure uniqueness:

However, the repeating ‘Companies House’ suggests that the Attribute contains data that is actually describing the instance of another Entity. There is some ‘thing’ whose single instance definition exists across more than one instance of the Organisation Entity. Therefore, its value cannot be wholly dependent on the Unique Identifier of this Entity. We need a new Entity! Repeated values in an Attribute suggest the creation of a new Master entity.

Below illustrates the Normalisation technique we can use to resolve this Relationship and is a common resolution pattern.

Another clue that there was the need for a new Master Entity was the qualification of several Attributes with the prefix ‘Registration’. Because we are taking out repeating data instances out of the Organisation Entity and representing them only once in the new Master Entity, we are reducing data redundancy. You may need further convincing that this yields enough benefit, given we are in the age of ultra cheap storage, and that this makes it more complicated to retrieve the data. But, let me stress, this is not an implemented physical system nor will this be used in the presentation layer. Further, once creating this new Registration Authority Master Entity, you will quickly begin to identify additional Attributes that we can add to enrich the Entity with.

We may decide to record the addresses for organisations. For instance, the registered and postal address:

These repeated groups of Address Attributes signify the need for normalisation. In general ‘Registered XXX’ and ‘Postal XXX’ indicate a repeating set of attributes because we need to qualify which repeating set they belong to i.e. Registered or Postal. We need to create a new Normalised Detail Entity: Organisation Address. With this new model we can seamlessly add as many Address types to the Organisation instance as required, without needing to add new Address Attribute groups to the Organisation Entity:

If visualising the data for the above Entity, alarm bells should be ringing. It is clear that the Address Type Attribute will contain repeated values across Entity instances. Hence, we should normalise again and gain a new Master Entity as outlined earlier from which we will gain an Organisation Address Type Entity!

As illustrated above, the Data Modelling process is highly iterative. It often begins with broad, high-level Entities that provide a useful foundation. However, as the model matures and techniques such as normalisation are applied – these Entities are refined into more accurate, structured, and meaningful components, bringing the model closer to truly representing the underlying business.

Future blogs will introduce some more advanced modelling techniques such as using relationships as Unique Identifiers, Recursive Relationships and more.