Database Normalization: 1NF, 2NF, and 3NF

Database normalization gives each fact in a relational database one reliable home. It reduces duplicate data and prevents rows from contradicting each other. The first three normal forms, 1NF, 2NF, and 3NF, provide a practical way to find those problems.

The examples connect each form to functional dependencies and the update, insert, and delete anomalies it prevents.

This article is based on the "Normalization" chapter of Learn SQL, where you can practice designing and querying the tables.

Database Normalization Explained

Database normalization structures tables to improve data integrity and reduce data redundancy.

"Data integrity" refers to the accuracy and consistency of data. For example, if a user's age is stored in a database, rather than their birthday, that data becomes incorrect automatically with the passage of time.

It would be better to store a birthday and calculate the age as needed. A birthday is raw data - it never changes. An age is precomputed data - it becomes stale over time.

"Data redundancy" occurs when the same piece of data is stored in multiple places. For example: saving the same file multiple times to different hard drives. Data redundancy can be problematic, especially when data in one place is changed such that the data is no longer consistent across all copies of that data.

Normalization addresses three common data anomalies:

• Update anomaly: changing one fact requires updates in multiple rows.
• Insert anomaly: you can't record one fact until an unrelated fact exists.
• Delete anomaly: deleting one fact accidentally removes the only copy of another.

These anomalies show up in nearly every application, even when an ORM generates most queries.

Functional Dependencies in Database Normalization

Normal forms describe functional dependencies: relationships where one set of columns determines another. The notation customer_id -> customer_email means each customer_id has exactly one corresponding customer_email.

The left side is the determinant. A key is a determinant that uniquely identifies a row. A candidate key is any minimal set of columns that can identify a row; one usually becomes the primary key.

In the context of database normalization, we're going to use the term "primary key" slightly differently. When we're talking about SQLite, a "primary key" is a single column that uniquely identifies a row.

When we're talking more generally about data normalization, the term "primary key" means the collection of columns that uniquely identify a row. That can be a single column, but it can actually be any number of columns that form a composite key. A primary key is the minimum number of columns needed to uniquely identify a row in a table.

Whether you use integer IDs, UUID primary keys, or composite keys, normalization depends on what the values mean and determine, not their SQL types.

First Normal Form (1NF)

To be compliant with first normal form (1NF for short), a database table simply needs to follow two rules:

• It must have a unique primary key.
• A cell can't have a nested table as its value (depending on the database system you're using, this may not even be possible).

This table does not adhere to 1NF:

It has two identical rows, so there isn't a unique primary key for each row.

The simplest way (but not the only way) to get into first normal form is to add a unique id column.

It's worth noting that if you create a "primary key" by ensuring that two columns are always "unique together," that works too.

First normal form is simply a good idea. I've never built a database schema where each table isn't at least in first normal form.

Second Normal Form (2NF)

A table in second normal form (2NF) follows all the rules of first normal form, and one additional rule that applies only to composite primary keys:

• All columns that are not part of the primary key are dependent on the entire primary key, and not just one of the columns in the primary key.

A dependency on only part of the key is called a partial dependency.

In this table, the primary key is a combination of first_name + last_name.

This table does not adhere to 2NF. The first_initial column is entirely dependent on the first_name column, rendering it redundant.

One way to convert the table above to 2NF is to add a new table that maps a first_name directly to its first_initial. This removes any duplicates!

You should probably default to keeping your tables in second normal form. That said, there are good reasons to deviate from it, particularly for performance reasons. The reason being that when you have to query a second table to get additional data, it can take a bit longer. My rule of thumb is: optimize for data integrity and data de-duplication first. If you have speed issues, de-normalize accordingly.

Third Normal Form (3NF)

A table in third normal form (3NF) follows all the rules of second normal form, and one additional rule:

• All columns that aren't part of the primary key are dependent solely on the primary key.

Notice that this is only slightly different from second normal form. In second normal form we can't have a column completely dependent on only part of the primary key, and in third normal form we can't have a column that is entirely dependent on anything that isn't the primary key. A dependency between two non-key columns is called a transitive dependency.

In this table, the primary key is simply the id column.

This table is in second normal form because first_initial is not dependent on a part of the primary key. However, because it is dependent on the name column, it doesn't adhere to third normal form.

The way to convert the table above to 3NF is to add a new table that maps a name directly to its first_initial. Notice how similar this solution is to 2NF.

The same exact rule of thumb applies to the second and third normal forms.

Comparing 1NF, 2NF, and 3NF

The creator of "database normalization," Edgar F. Codd described different "normal forms" a database can adhere to. The most common ones are:

• First normal form (1NF)
• Second normal form (2NF)
• Third normal form (3NF)
• Boyce-Codd normal form (BCNF)

In short, first normal form is the least normalized form, and Boyce-Codd is the most normalized form. The more normalized a database, the better its data integrity, and the less duplicate data you'll have.

In my opinion, the exact definitions of 1st, 2nd, 3rd and Boyce-Codd normal forms simply are not all that important in your work as a back-end developer.

However, what is important is to understand the basic principles of data integrity and data redundancy that the normal forms teach us. Let's go over some rules of thumb that you should commit to memory – they'll serve you well when you design databases and even just in coding interviews.

1. Every table should always have a unique identifier (primary key)
2. 90% of the time, that unique identifier will be a single column named id
3. Avoid duplicate data
4. Avoid storing data that is completely dependent on other data. Instead, compute it on the fly when you need it.
5. Keep your schema as simple as you can. Optimize for a normalized database first. Only denormalize for speed's sake when you start to run into performance problems.

Table Relationships and Normalization

Normalization usually splits one wide table into related tables. Relational databases are powerful because of the relationships between the tables. These relationships help us to keep our databases clean and efficient. A relationship between tables assumes that one of these tables has a foreign key that references the primary key of another table.

There are 3 primary types of relationships in a relational database:

1. One-to-one
2. One-to-many
3. Many-to-many

A one-to-one relationship most often manifests as a field or set of fields on a row in a table. For example, a user will have exactly one password. Settings fields might be another example of a one-to-one relationship. A user will have exactly one email_preference and exactly one birthday.

When talking about the relationships between tables, a one-to-many relationship is probably the most commonly used relationship. A one-to-many relationship occurs when a single record in one table is related to potentially many records in another table. The one → many relation only goes one way; a record in the second table cannot be related to multiple records in the first table! For example:

• A customers table and an orders table. Each customer has 0, 1, or many orders that they've placed.
• A users table and a transactions table. Each user has 0, 1, or many transactions that they've taken part in.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  amount REAL NOT NULL,
  customer_id INTEGER,
  CONSTRAINT fk_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
);

A many-to-many relationship occurs when multiple records in one table can be related to multiple records in another table. For example:

• A products table and a suppliers table – Products may have 0 to many suppliers, and suppliers can supply 0 to many products.
• A classes table and a students table – Students can take potentially many classes and classes can have many students enrolled.

Joining tables help define many-to-many relationships among data in a database. As an example, when defining the relationship above between products and suppliers, we would define a joining table called products_suppliers that contains the primary keys from the tables to be joined.

Then, when we want to see if a supplier offers a specific product, we can look in the joining table to see if the IDs share a row.

When enforcing specific schema constraints, we may need to enforce the UNIQUE constraint across two different fields.

CREATE TABLE product_suppliers (
  product_id INTEGER,
  supplier_id INTEGER,
  UNIQUE(product_id, supplier_id),
  FOREIGN KEY (product_id) REFERENCES products (id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers (id)
);

This lets multiple rows share the same product_id or supplier_id, but it prevents any two rows from having both the same product_id and supplier_id. That combination of the two IDs is the joining table's "primary key" in the normalization sense: a composite key.

When Should You Denormalize a Database?

Denormalization deliberately stores redundant or derived data to speed up specific reads. It can reduce joins, aggregations, or repeated calculations, but reintroduces consistency work.

Start with a normalized schema. Measure a real performance problem, identify the expensive query, and denormalize only if indexes and query improvements aren't enough. Define how every duplicate value stays synchronized. A cached order total maintained in a transaction is defensible; copying customer details into every order "just in case" isn't.

Apply schema changes through repeatable database migrations so production data moves safely with the design.