Relational Database Normalization: From 1st to 3rd Normal Form in Practice

Eliminating Redundancies and Ensuring Structural Integrity

A poorly structured database design may seem functional at the beginning of development, but as the system grows in production and reaches millions of records, serious problems arise such as excessive data redundancy, performance loss in queries, and worst of all, update anomalies that corrupt the integrity of company information.

To avoid these disastrous scenarios, we use the formal process of Database Normalization, based on mathematical and relational rules known as Normal Forms. In this article, we will detail the first three normal forms, essential for any developer or data analyst.

What Are Data Anomalies?

Before applying formal rules, it is important to understand what we want to avoid. Unnormalized tables typically suffer from three types of anomalies:

• Insertion Anomaly: Inability to insert essential data because other mandatory attributes are not yet available.
• Update Anomaly: When changing a repeated piece of data across multiple rows needs to be done in multiple places, risking leaving inconsistent records if partially failed.
• Deletion Anomaly: Removing a record accidentally deletes other important corporate data that was stored in the same row.

1st Normal Form (1NF): Atomicity of Values

The first rule of normalization requires that all attributes of a table contain only atomic, indivisible values, eliminating repeating groups or columns that store comma‑separated lists within a single field.

Practical violation example: An orders table with a column called telefones_contato storing "19999998888, 19388881111" violates 1NF.

Solution (1NF): Create a related child table for phone numbers, where each number occupies an individual row linked by a foreign key.

2nd Normal Form (2NF): Elimination of Partial Dependencies

To be in Second Normal Form, the table must meet two criteria:

1. Be compliant with 1NF.
2. All non‑key columns must depend on the entire primary key, not just a part of it (which only occurs in composite primary keys).

If a column depends only on one of the fields that make up a composite primary key, it suffers from partial dependency and must be moved to a separate table.

3rd Normal Form (3NF): Elimination of Transitive Dependencies

Third Normal Form is the gold standard for most transactional (OLTP) relational systems. To achieve it, the table must:

1. Be compliant with 2NF.
2. Have no transitive dependencies — that is, no non‑key column may depend on another non‑key column.

Practical example of correction for 3NF:

Imagine a funcionarios (employees) table with columns: id_funcionario (PK), nome, id_departamento, and nome_departamento.

Here a transitive dependency occurs: nome_departamento depends on id_departamento, not directly on the employee. If the department name changes, we would have to update hundreds of rows in the employees table.

Solution (3NF): Split into two clean tables:

• departamentos table: id_departamento (PK) and nome_departamento.
• funcionarios table: id_funcionario (PK), nome, and id_departamento (FK).

Conclusion

Rigorously applying normalization up to Third Normal Form ensures a clean, scalable data model free of dangerous redundancies and prepared to run high‑performance, absolutely consistent relational queries.