Normalization refers to a concept from database design with the aim of eliminating redundancies, i.e. duplications in the database. This saves storage space and also prevents anomalies from occurring.

What is a relational database?

In a database, large amounts of data, usually structured, are stored and made available for query. It is almost always an electronic system. Theoretically, however, analog information collections, such as a library, are also databases. As early as the 1960s, the need for central data storage arose because things such as access authorization to data or data checking should not be done within an application, but separately from it.

The most intuitive way to store this information is in tabular form, with rows and columns. For many data, such as in accounting, this representation is also suitable, since the data always have a fixed form. Such databases in tabular form are called relational databases, derived from the mathematical concept of relations.

What are the goals of normalizing the database?

Normalization of a database is intended to achieve the following goals:

• Elimination of redundancies: Normalization allows duplicate data to be deleted without the database itself losing information content. This saves storage resources and thus also leads to faster queries. It also reduces the potential for errors, since all redundant records would always have to be changed when a change is made.
• Data model: Normalization often also automatically results in a clear and uniform data model. This is because a large table is often divided into several, manageable tables, resulting in familiar schemas such as the star schema or snowflake schema.

What are the normal forms?

In practice, three normal forms are of particular importance. Because often, if these are fulfilled, the database is performant, and only relatively little work had to be invested. Thus, the cost-benefit ratio is comparatively high up to the third normal form. In theory, however, there are up to five normal forms, but in this article, we will limit ourselves to the first three.

Here it is also important that the normal forms build on each other. This means that a high normal form is only fulfilled if all (!) preceding normal forms are also fulfilled.

1. Normal Form (1NF)

The 1st normal form is achieved when all records are atomic. This means that each data field may contain only one value. It should also be ensured that each column contains only values of the same data type (numeric, text, etc.). The following examples would have to be modified accordingly to have a database in 1st normal form:

• Address: “Main Street 1, 12345 Berlin” –> Street: “Main Street”, House number: “1”, Zip code: “12345”, City: “Berlin”.
• Invoice amount: “128,45 €” –> Amount: “128,45”, Currency: “€”.

2. Normal Form (2NF)

The 2nd normal form is satisfied if the first normal form is satisfied, and also each column in a row is fully functionally dependent on the primary key. The primary key denotes an attribute that can be used to uniquely identify a database row. This includes, for example, the invoice number to identify an invoice or the ID number to identify a person.

In concrete terms, this means that all characteristics that are not exclusively dependent on the primary key must be outsourced. In practice, this often leads to a so-called star schema.

In our example, the customer name does not depend on the primary key “order_id” of the original table. Therefore, the customer name must be swapped out in a new table. Only the foreign key “customer_id” then references the new table, so no information is lost.

3. Normal Form (3NF)

The third normal form is present, if the two preceding normal forms are fulfilled, and there are additionally no so-called transitive dependencies. A transitive dependency exists if an attribute that is not a primary key not only depends on it but also on other attributes.

In our example, if we have a table where the invoice number, the product number, and the price are given, we most likely have a transitive dependency. In fact, the price of the product does not really depend on the invoice number, but rather on the product number, since a fixed price is defined for each product.

This dependency can be resolved by moving the products to a new table, thus removing the price attribute from the original table.

What are the limitations of Normalization?

Normalization plays a crucial role in structuring databases to ensure data integrity and efficient storage. However, it is important to recognize that while normalization offers significant benefits, it also has certain limitations that need to be considered. This section discusses the limitations of database normalization and highlights key considerations for efficient data management.

1. Performance Impact: One limitation of highly normalized databases is the potential impact on query performance. With increased normalization, complex joins and multiple table lookups may be required to retrieve data, which can slow down query execution. Additionally, a higher number of tables resulting from normalization can introduce additional complexity and potentially affect overall performance.
2. Data Redundancy: Normalization aims to eliminate data redundancy by splitting data into separate tables. However, this can lead to the need for additional joins to fetch related information, resulting in increased complexity and potential performance issues. In some cases, denormalization techniques may be employed to optimize query performance at the expense of some data redundancy.
3. Increased Complexity: Higher levels of normalization can introduce increased complexity in database design and query construction. Database administrators and developers must possess a strong understanding of normalization principles to manage the intricate relationships between normalized tables effectively.
4. Maintenance Challenges: Modifying the structure of highly normalized databases can be complex and time-consuming. Updates or deletions may require changes across multiple tables, increasing the risk of inconsistencies if not carefully managed.
5. Flexibility and Adaptability: Normalization can pose challenges when accommodating changes or new requirements in the database structure. Adding new attributes or relationships may necessitate modifications to existing tables, potentially affecting the entire schema.
6. Balancing Normalization and Performance: Achieving higher levels of normalization may not always be the optimal choice for all database systems. Striking a balance between normalization for data integrity and denormalization for performance optimization is crucial. Careful consideration of the specific requirements of the system is necessary.
7. Trade-Offs with Reporting and Analysis: Highly normalized databases may pose challenges when generating complex reports or performing analytical tasks. Aggregating data from multiple tables may require additional processing steps, affecting the efficiency of reporting and analysis operations.
8. Context-Specific Considerations: The appropriateness of normalization depends on the nature of the data and the specific use case. Certain types of data, such as logs or audit trails, may not benefit significantly from higher levels of normalization due to their unique characteristics.

While normalization is an essential technique for ensuring data integrity, it is important to understand its limitations. Careful evaluation of the trade-offs between normalization and performance, flexibility, maintenance, and reporting is necessary to design an efficient database system. Striking the right balance between normalization and other considerations is key to achieving effective and streamlined data management.

What is the concept of Denormalization?

Denormalization is a database optimization technique that involves intentionally introducing redundancy into a normalized database schema. While normalization aims to eliminate data redundancy and ensure data integrity, denormalization strategically reintroduces redundancy to improve query performance, simplify data retrieval, and enhance overall system efficiency.

In a denormalized schema, data is intentionally duplicated or combined into fewer tables, reducing the need for complex joins and enabling faster data retrieval. Denormalization can take several forms, including the following:

1. Flattening Tables: This involves combining multiple related tables into a single table, reducing the need for joining operations. This approach simplifies queries and can improve performance, especially for read-intensive workloads.
2. Adding Redundant Data: This approach includes duplicating data from related tables into one table, reducing the need for joins to fetch related information. This redundancy enhances query performance, especially for frequently accessed data.
3. Introducing Derived Columns: Denormalization involves calculating and storing derived or computed values directly in the denormalized table. This eliminates the need for complex calculations during query execution, improving performance.

Denormalization is often employed in scenarios where query performance is critical, such as data warehousing, reporting, and decision support systems. However, it is essential to consider the trade-offs associated with denormalization, including increased storage requirements, potential data inconsistencies, and additional complexity in data maintenance and updates.

It is important to note that denormalization should be applied judiciously and based on careful analysis of the specific requirements of the application or system. It is not a one-size-fits-all solution and should be implemented with consideration for the nature of the data, the types of queries performed, and the performance goals of the database system.

By selectively denormalizing a database, developers can strike a balance between the benefits of normalization and the need for improved query performance, enabling efficient data retrieval and optimizing the overall performance of the system.

This is what you should take with you

• Normalization of a database means the systematic elimination of redundancies.
• The normalization saves storage space and improves the performance of queries.
• In practice, only the first three normal forms are often implemented, since these have the highest cost-benefit ratio.

Other Articles on the Topic of Normalization

On Wikipedia, there is a detailed article about the normalization of databases.