Database Normalization and 3NF Schema Design

Why Do We Need to Normalize?

Before diving into the "how," let's understand the "why." A poorly designed database can suffer from several issues that make it difficult to manage and can lead to corrupt or inconsistent data. These issues are often called anomalies.

To get a practical sense of these problems, please watch the beginning of the following video.

As the video explains, the goal of normalization is to structure tables to eliminate data redundancy (storing the same information in multiple places) and thereby improve data integrity (the accuracy and consistency of data).

The Wikipedia article on Database Normalization provides a formal definition for the three main types of anomalies that arise from redundancy:

In summary:

• Insertion Anomaly: You can't add new data because some other, unrelated data is missing. (e.g., can't add a new faculty member until they are assigned a course).
• Update Anomaly: You have to change the same piece of information in multiple rows. If you miss one, the data becomes inconsistent. (e.g., a faculty member changes their name, and you have to update every course record they are associated with).
• Deletion Anomaly: Deleting a record unintentionally removes other, unrelated information. (e.g., deleting the last course a faculty member teaches also erases the faculty member from the database).

Normalization is a systematic process for designing tables to eliminate these anomalies. The process is organized into a series of steps called normal forms. A database is "in 1NF" if it satisfies the rules of the first normal form, "in 2NF" if it also satisfies the rules of the second, and so on.

The Foundation: The Relational Model

The principles of normalization are rooted in the relational model, which was devised by Edgar F. Codd in 1970. His goal was to abstract away the physical storage of data, allowing developers to work with a logical, consistent model. This separation of logical and physical schemas is a cornerstone of modern database systems and directly relates to the goal you expressed of understanding the system from the "root" level.

The key takeaway is that data is stored in relations (which we commonly call tables), which are unordered sets of tuples (rows). Each tuple is composed of attributes (columns). Normalization provides the rules for organizing these attributes into relations.

First Normal Form (1NF): Atomicity

The first and most fundamental rule is about the structure of your columns.

Rule for 1NF:

1. All attribute values must be atomic. This means a single cell cannot hold a list, an array, or a set of values. Each cell must contain one, and only one, value.
2. Each row must be unique, which is enforced by a primary key.

Let's see what this means in practice.

The most common 1NF violation you'll see is the "repeating group." Consider this unnormalized table of book subjects from the Wikipedia example:

This violates 1NF because the Subject column is not atomic; it contains a list of subjects. To bring this into 1NF, we create a separate table to hold the subjects for each book, ensuring each cell has only one value.

Book Table (1NF)

Book_Subject Table (1NF)

Now, every attribute is atomic. We have a clear structure that is easy to query and maintain.

Second Normal Form (2NF): Eliminating Partial Dependencies

Once a table is in 1NF, we can move to the next step.

Rule for 2NF:

1. The table must be in 1NF.
2. All non-key attributes must be fully functionally dependent on the entire primary key.

This rule is only relevant for tables that have a composite primary key (a primary key made up of two or more columns). It means that no non-key column should depend on only a part of the composite key.

Let's look at the Price table from the Wikipedia example. After satisfying 1NF and adding more data, we might have a table with a composite key of {Title, Format}.

Price Table (1NF, but not 2NF)

Here, the primary key is {Title, Format}.

• Price depends on the entire key (a hardcover has a different price than an e-book).
• However, Author and Author Nationality depend only on Title. They have nothing to do with the Format. This is a partial dependency.

This leads to anomalies. If we want to change the author's nationality, we have to do it in multiple places. To fix this and achieve 2NF, we split the table:

Book Table (2NF)

Price Table (2NF)

Now, all non-key attributes in each table are fully dependent on their respective primary keys.

Third Normal Form (3NF): Eliminating Transitive Dependencies

Even in 2NF, a table can have update anomalies. 3NF addresses this.

Rule for 3NF:

1. The table must be in 2NF.
2. There must be no transitive dependencies. This means every non-key attribute must depend only on the primary key, and not on any other non-key attribute.

A popular mnemonic for remembering the rules for 1NF, 2NF, and 3NF together is that every non-key attribute "must depend on the key, the whole key, and nothing but the key."

• "The key" (covered by having a primary key in 1NF).
• "The whole key" (removes partial dependencies for 2NF).
• "Nothing but the key" (removes transitive dependencies for 3NF).

Let's revisit our Book table, which is currently in 2NF:

Book Table (2NF, but not 3NF)

Here, Author Nationality depends on Author, which is a non-key attribute. Author in turn depends on Title (the primary key). This Title -> Author -> Author Nationality chain is a transitive dependency. If E.F. Codd were to change nationality (unlikely!), you'd have to update it for every book he wrote.

To achieve 3NF, we split the table again:

Book Table (3NF)

Author Table (3NF)

Now, every non-key attribute in each table depends only on the primary key.

By achieving 3NF, we have created a robust schema that minimizes redundancy and prevents the common data anomalies. For most transactional backend systems, 3NF is the gold standard for schema design.

Conclusion

Today, you've learned the fundamental principles of database normalization, a critical skill for designing resilient backend systems. By progressing through the normal forms, you systematically eliminate data redundancy and protect your database from insertion, update, and deletion anomalies.

Key Takeaways:

• Normalization is the process of organizing database tables to reduce redundancy and improve data integrity.
• First Normal Form (1NF) requires all attribute values to be atomic and rows to be unique (via a primary key).
• Second Normal Form (2NF) requires a table to be in 1NF and for all non-key attributes to depend on the entire composite primary key, eliminating partial dependencies.
• Third Normal Form (3NF) requires a table to be in 2NF and for all attributes to depend only on the primary key, eliminating transitive dependencies.
• The mantra to remember is that attributes must depend on "the key, the whole key, and nothing but the key."

Preview of the Next Lesson

While a 3NF schema is excellent for data integrity and write-heavy transactional systems (OLTP), it can sometimes lead to slower read performance. Why? Because to retrieve a complete picture of the data, you often need to perform multiple JOIN operations across these nicely separated tables.

In the next lesson, we will explore the other side of the coin: denormalization. We will learn when and how to strategically break the rules of normalization to optimize for fast read performance, a common requirement in analytics and reporting systems (OLAP).