Normal Forms and Their Application in Enterprise Data Warehouses (EDW)

Introduction

Normal forms are usually mentioned either in interviews for data engineering and analytics roles related to enterprise data warehouse design, or in more practical debates about how “correctly” a particular data model has been designed in a warehouse used by a project.

Normalization is not a set of abstract rules for the sake of theory. It is a way to understand how data should be decomposed into structures so that the same facts are not duplicated unnecessarily, do not contradict each other, and do not create additional problems as the warehouse evolves.

In this article, I want to review the main normal forms and show how normalization helps structure data in enterprise data warehouses: from raw and redundant tables to models where business entities, attributes, relationships, and dependencies are separated more deliberately.

Let us start with the basics: what is a “normal form” and why is it called that?

A “normal form” usually means a rule, or a set of rules, that a data structure must satisfy in order to be considered more organized from the point of view of storage logic.

The word “normal” here does not mean that everything else is “abnormal”. It rather refers to a certain reference structure brought into a more correct shape, where part of the redundancy, dependencies, and potential anomalies during insert, update, or delete operations have been eliminated.

In other words, each next normal form is an attempt to clean up and organize the model a little further.

A short historical note and the connection with mathematical theory

I want to point out an important detail: normal forms did not originate from the everyday practice of DBA specialists, but from an earlier and rather strict relational theory, which is closer to mathematics.

The foundation of this direction was laid by Edgar F. Codd, who published the paper “A Relational Model of Data for Large Shared Data Banks” in 1970. In that work, the relational model was explicitly based on the theory of relations.

Incidentally, one of the normal forms, BCNF, even contains the scientist’s name in its title. More precisely, normal forms rely on two mathematical foundations:

• set theory, including relations
• first-order predicate logic, as a basis for the formal description of objects, relationships, and data dependencies

Edgar F. Codd

What normal forms exist, and why are they arranged in this order? The classic normalization sequence usually looks like this: “1NF, 2NF, 3NF, BCNF, 4NF, 5NF, DKNF, and 6NF”. They are arranged this way for a reason. They represent consecutive levels of increasingly strict requirements for the data structure.

First, the most obvious problems are removed, such as non-atomic values and repeating groups. Then come transitive dependencies. Toward the end, the forms deal with more subtle and rare anomalies related to multivalued dependencies, join dependencies, and maximally fine-grained decomposition of the data structure.

What are BCNF and DKNF? Why do they have names without numbers? Why are they placed where they are in the sequence?

BCNF stands for Boyce-Codd Normal Form. It is named not by number, but after the researchers, because historically it appeared as a separate clarification and strengthening of 3NF, rather than simply as the next numbered step.

DKNF stands for Domain-Key Normal Form. It also has a semantic rather than numerical name, because it is defined through a different principle: all constraints must follow only from domains and keys, without additional special constraints. In the theoretical sequence, it is usually placed after 5NF as an even stricter level of normalization. In practice, however, it is discussed much less often, because it is considered more theoretical and rarely provides noticeable practical benefit in ordinary database design.

Next, let us look at each normal form separately, including the point where normalization effectively does not exist yet.

No normalization: what does it look like in data?

Before moving on to specific normal forms, it is useful to define the starting point — the situation where there is essentially no normalization yet. Strictly speaking, it is more correct here to talk not about “zero normal form” as a full-fledged normal form, but about an unnormalized structure, where the data has already been collected and may even be suitable for storage or initial loading, but has not yet been brought into a more orderly shape.

*A note. *If we approach the topic from a strictly theoretical point of view, based on set theory, then a relation in the relational model is already considered to be in 1NF. In this article, by the state “before 1NF” I mean not a relation in the strict sense, but a raw or unnormalized data structure, which is usually where practical analysis begins.

In practice, this often looks like:

• one flat table
• a JSON document or a raw extract where several different entities and repeating values are placed together

For example, one record may contain all of the following at the same time:

• customer
• customer phone numbers
• list of products
• delivery address
• manager
• manager’s department name
• order status history

For initial loading or data exchange, such a format may be convenient. But in fact, this is the absence of normalization, because all possible relationships are heavily mixed together. Where certain parts of the data could be separated into distinct entities, we instead get a kind of dumping ground at the input, at least from the point of view of hierarchy and data decomposition.

The problem here is not that the data itself is “bad”. The problem is that the structure does not yet separate atomic values, repeating groups, and different entities. This is usually where the move toward normalization begins: first, the most obvious mixtures and repetitions are removed, and then the model becomes stricter step by step.

First Normal Form (1NF): what does it improve compared with the previous step?

At the previous step, the data still looked like an unnormalized structure, where one record could contain many pieces of data that clearly needed to be decomposed according to relationships and hierarchy. 1NF makes the first minimal step toward order. It requires values to become more atomic, and repeating groups to stop being stored in a single field or inside a single row as a list.

Put simply, if one record previously contained, for example, several phone numbers of a customer or several products in an order, then in 1NF such values must be separated so that one field contains one value, while repetitions are represented as separate rows or separate related entities.

Example before 1NF:

What is the problem?

• the phones field contains several values at once
• the products field also contains a list
• one row stores several repeating groups at the same time

Let us normalize the non-atomic products field:

What improved?

• one field now contains one value
• lists disappeared
• the structure became more predictable for storage and processing

However, atomicity of values alone is still not enough. Even if nested lists have already been removed, it does not mean that the structure has become logically clean. It may still contain fields that relate not to the whole record, but only to part of its key. This brings us to the second normal form.

Second Normal Form (2NF): what does it improve compared with 1NF?

If 1NF removes lists inside fields, then 2NF checks the next level of order: do the remaining data fields relate to the whole record, and not only to one part of it? Otherwise, some fields will still be stored in the wrong place.

In other words, every field in a row must describe the entire row as a single fact, not just one part of that fact.

In a simplified example, this looks as follows:

• if a row describes “a product in an order”, then the remaining fields must relate specifically to the fact “this product in this order”
• if some field relates only to the order or only to the product, then it does not describe the entire row, but only part of it

In the next example, a table row describes a product in an order. There are no lists inside fields anymore, but the customer name is still duplicated in every row, even though semantically it relates to the whole order:

To bring the data to 2NF, the order data and the data about products inside the order must be separated. The customer name relates to the whole order, not to each product line. Therefore, it should be stored separately. As a result, duplication disappears, and the structure becomes cleaner:

A note. In this simplified example, after decomposition the structure already looks not only like 2NF, but also like a stricter form. This is normal. In simple educational examples, fixing a 2NF problem often removes some of the following problems as well. However, in real models, customer data is usually moved into a separate entity, while in our simplified example customer_name is still stored directly in the orders table.

Summary for 1NF and 2NF. 1NF answers the question: how exactly are values stored — one value per field, or as lists and nested sets? 2NF answers a different question: do the remaining fields relate to the whole record as a single fact, or only to one part of it? In other words, 1NF brings basic order to the storage format, while 2NF brings order to the logic of the record itself.

But this is still not enough. Even if order data has already been separated from product line data, the order table itself may still contain fields that do not directly relate to the order, but to another entity, for example, the customer. This is where it becomes convenient to move on to 3NF and look at an example where the customer entity is explicitly separated.

Third Normal Form (3NF): what does it improve compared with 2NF?

When discussing 2NF, we already separated order data from product line data. But this is still not enough, because the order table itself may still contain fields that no longer relate to the order, but to an object connected with it — for example, the customer. This is exactly what 3NF fixes by solving the problem of transitive dependency. This form requires that the table contain data about the entity itself, while information about related entities should be moved separately.

Put simply, 2NF separates parts of one record, while 3NF separates entities themselves. If an order table stores not only order_id and customer_id, but also customer_name, then the customer name becomes tied to the order, although semantically it belongs not to the order, but to the customer.

• example of a direct dependency on the key: order_id => order_date
• through another field: order_id => customer_id => customer_name

In that case, the 3NF table design would look as follows:

Why is 3NF usually associated with the concept of transitive dependency? Because at this level of normalization, it becomes important not only how data is stored inside a record, but also through which intermediate fields some attributes become connected to the key. If a field depends on the key not directly, but through another non-key field, this usually means that data from another entity has entered the table. These are exactly the cases that 3NF tries to eliminate.

Boyce-Codd Normal Form (BCNF): how is it stricter than 3NF?

BCNF is usually considered a strengthened version of 3NF. In practice, the difference between them is felt less strongly than the difference between 2NF and 3NF, because BCNF does not introduce a completely new type of problem. Rather, it cleans up rare cases that 3NF may still allow. To put it very simply, BCNF removes situations where one field inside a table already determines another field, and because of that, part of the data is stored unnecessarily.

In short, BCNF is 3NF without the remaining logical loopholes. If a table already looks normal, but one field inside it still uniquely determines another field, BCNF will require this dependency to be moved out separately.

To demonstrate a BCNF case, we can add not a new entity, but a second way of identifying the same product. For example, in addition to the product field in the order line, we can also store product_code:

The row still describes the same fact — a product in an order. So we are not breaking the 3NF logic around a different entity. However, a subtler dependency appears inside the row: “product_code => product”. If we bring this structure to BCNF, normalization would look as follows:

Fourth Normal Form (4NF): what does it improve?

In BCNF, a row may still store one fact, but with an unnecessary internal dependency. In 4NF, the problem is different: one row starts artificially combining two independent facts. For example, a restaurant separately prepares different types of pizza and separately delivers orders to different delivery zones.

If we try to store this in one table of the following form:

• restaurant_id | pizza | delivery_zone

then a row starts to look like a single fact, although in reality it is just the intersection of two independent lists — that is, two independent facts:

This is exactly what 4NF fixes. Such data must be separated, for example, into: “restaurant_pizzas(restaurant_id, pizza)” and “restaurant_zones(restaurant_id, delivery_zone)”:

Fifth Normal Form (5NF): what does it improve?

If 4NF removes the mixing of independent facts in one table, then 5NF goes even further and deals with cases where one table stores a complex composite fact that at first glance looks whole:

The idea here is that such a fact can be reconstructed without loss from several simpler tables, while the original table does not add any new logic to them. In other words, 5NF is needed where one general relationship looks like a complete fact, but in reality turns out to be the result of joining several simpler relationships.

Normalizing such a table to 5NF would look as follows:

Domain-Key Normal Form (DKNF): what does it improve?

If 5NF still mainly talks about how to decompose an overly complex relationship, DKNF takes another step toward the general logical cleanliness of the model. Here, the point is not so much another decomposition of tables, but making sure that the structure does not rely on additional special rules, exceptions, or hidden business logic.

Why is it called domain-key normal form? Because it describes a model where all rules can be explained by only two things:

• what values are allowed in fields
• which fields form a unique record

If correctness requires some additional special conditions, then this is no longer DKNF.

Sixth Normal Form (6NF): what does it improve?

6NF takes decomposition to the limit: one independent property is stored separately from others. That is why it fits especially well with temporal data, where different properties of the same entity change at different times and are more convenient to store separately.

Anchor modeling is built on the same logic. It is a fairly scalable logical model for enterprise data warehouses, where the anchor defines the entity, attributes store properties separately, and relationships are also separated. Data is decomposed into attribute tables, where usually one atomic value of one property is stored. You can study the anchor modeling methodology in more detail using the following links:

• https://www.anchormodeling.com/
• https://en.wikipedia.org/wiki/Anchor_modeling

Conclusion

Normal forms are best understood not as a “dry theoretical ladder”, but as consecutive steps for bringing order to data. This is quite important when building enterprise data warehouses.

Personally, I like anchor modeling with its maximum degree of normalization, but in practice it does not always fit physical realities well.

For example, MPP relational databases such as Greenplum, especially with AO/AOCO tables, do not work particularly well with this kind of logical model. But this is a topic for another article, which I am planning to write later.