Pirate Wiki

Toggle Theme

Normalization ​

Normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.

The core rule of normalization is DRY (Don't Repeat Yourself). If data is duplicated, updating it requires changing multiple rows, which risks data anomalies.

The Normal Forms ​

While there are many normal forms, most databases are designed to be in Third Normal Form (3NF).

First Normal Form (1NF) ​

To satisfy 1NF:

  1. The table must have a Primary Key.
  2. Every column must hold atomic (indivisible) values. (e.g., You cannot have a comma-separated list of values in a single column).
  3. There are no repeating groups (e.g., phone1, phone2, phone3 columns are bad).

Second Normal Form (2NF) ​

To satisfy 2NF:

  1. The table must be in 1NF.
  2. All non-key columns must depend on the entire Primary Key. (This mainly applies to tables with composite primary keys). If a column only depends on part of the composite key, it should be moved to a separate table.

Third Normal Form (3NF) ​

To satisfy 3NF:

  1. The table must be in 2NF.
  2. There are no transitive dependencies. Every non-key column must depend only on the primary key, not on another non-key column.

Example of breaking 3NF: A users table with columns: id, city, zip_code. Since city is determined by zip_code (and not directly by the user's id), it is a transitive dependency. Fix: Move zip_code and city to a separate locations table, and use zip_code as a Foreign Key in the users table.

Denormalization ​

Normalization is computationally expensive during reads because retrieving complete data often requires JOIN operations across multiple tables.

Denormalization is the strategic, intentional introduction of redundancy to improve read performance.

TIP

Always normalize first. Only denormalize when you have identified a specific read-performance bottleneck that indexing and caching cannot solve. For OLTP (Online Transaction Processing) systems, 3NF is standard. For Data Warehouses (OLAP), denormalized schemas (like Star or Snowflake) are preferred.