As software engineers, we deal with data abstraction every day. We try to make sense of fuzzy real-world concepts and turn them into structured information, such as variables, functions, classes, and structs, that software can process. Working with data almost always involves storing it somewhere. One common option is a relational database, often called a SQL database. In this post, we will explore the normal forms of relational databases, what they are, and their benefits and drawbacks. We will cover only the first three because they are the most common in modern software engineering.

First normal form⌗

Let’s start with the foundation: the first normal form (1NF). A table satisfies 1NF only when no column contains multiple values. More formally, each value must be atomic: taking part of it should not reveal another meaningful fact about the data. There are several types of non-atomic columns:

Repeating group⌗

A repeating group is a column containing a set, list, or array of values. The format does not matter; if we interpret the value as a list, it is a repeating group. The example below contains such a column and therefore violates 1NF.

[user_information]
------------------------------------------------
| user_id | phone_numbers        | signup_date |
------------------------------------------------
|       1 | (+84) 001, (+84) 002 |  2025-04-21 |
|       2 | (+10) 012            |  2025-04-21 |
|       3 | null                 |  2025-04-21 |
|       4 | (+11) 002            |  2025-04-21 |
|       5 |                      |  2025-04-21 |
------------------------------------------------

The phone_numbers column contains multiple phone numbers separated by commas. This is fine if we never query the individual numbers, but that is rarely the case. Eventually, we might want to count numbers beginning with (+01) or check whether a particular number is already taken. Those queries are non-trivial and often require string matching or parsing.

This format can also lead to data redundancy. If several users or accounts can share a phone number, we must store the same number in multiple places, taking up more space. How can we normalize this? Let’s look at the tables below.

[user_information]
-------------------------
| user_id | signup_date |
-------------------------
|       1 |  2025-04-21 |
|       2 |  2025-04-21 |
|       3 |  2025-04-21 |
|       4 |  2025-04-21 |
|       5 |  2025-04-21 |
-------------------------

[user_phone_numbers]
----------------------------
| user_id | phone_number   |
----------------------------
|       1 | (+84) 001      |
|       1 | (+84) 002      |
|       2 | (+10) 012      |
|       4 | (+11) 002      |
----------------------------

We have moved phone_numbers into a separate table and connected the two tables through user_id. This helps reduce redundancy and satisfies 1NF. The original meaning of repeating groups refers to columns containing arrays, but the term can also cover groups of columns representing the same attribute, as in the next example.

[user_information]
-----------------------------------------------------------
| user_id | phone_number_1 | phone_number_2 | signup_date |
-----------------------------------------------------------
|       1 | (+84) 001      | (+84) 002      |  2025-04-21 |
|       2 | (+10) 012      | null           |  2025-04-21 |
|       3 |                | null           |  2025-04-21 |
|       4 | (+11) 002      | null           |  2025-04-21 |
|       5 | null           |                |  2025-04-21 |
-----------------------------------------------------------

The two columns, phone_number_1 and phone_number_2, represent the same attribute: phone numbers. Each holds a single value, so strictly speaking, this does not violate 1NF. Still, it is an anti-pattern in schema design. It limits each user to two phone numbers. What if we need more? It also creates sparse data if only a few users have two numbers while most have one, wasting storage space. For these reasons, some literature also treats this as a violation of 1NF.

Table as an attribute⌗

[user_information]
-------------------------------------------------
| user_id | name     | children                  |
-------------------------------------------------
|         |          |                          |
|         |          |  ----------------------  |
|         |          |  | user_id | name     |  |
|       1 | John Doe |  ----------------------  |
|         |          |  |       3 | Doe Doe  |  |
|         |          |  ----------------------  |
|         |          |                          |
|---------|----------|--------------------------|
|         |          |                          |
|         |          |  ----------------------  |
|         |          |  | user_id | name     |  |
|       1 | Jane Doe |  ----------------------  |
|         |          |  |       3 | Doe Doe  |  |
|         |          |  ----------------------  |
|         |          |                          |
|---------|----------|--------------------------|
|       3 | Doe Doe  |  null                    |
-------------------------------------------------

Another way to create a multiple-value column is to store a table, or part of a table, as its value. Looking at the example above, you might think this is impossible because most, if not all, relational databases disallow it. That used to be true, but increasingly widespread support for JSON makes it conceptually possible. The example below uses a different format to represent the same information.

[user_information]
------------------------------------------------------
| user_id | name     | children                       |
------------------------------------------------------
|       1 | John Doe | {"id": 3, "name": "Doe Doe" } |
|       2 | Jane Doe | {"id": 3, "name": "Doe Doe" } |
|       3 | Doe Doe  | null                          |
------------------------------------------------------

This structure violates 1NF because children is non-atomic, and it makes the data redundant. When we update Doe Doe's information, we must also update the copies stored with John Doe and Jane Doe. If we are not careful, those copies can become inconsistent. To satisfy 1NF, we can remodel the tables as follows.

[user_information]
----------------------
| user_id | name     |
----------------------
|       1 | John Doe |
|       2 | Jane Doe |
|       3 | Doe Doe  |
----------------------

[user_children]
----------------------
| user_id | child_id |
----------------------
|       1 |        3 |
|       2 |        3 |
----------------------

The new table represents relationships between entities. When Doe Doe's information changes, we no longer need to update copies stored with John Doe or Jane Doe. We can also query and modify relationships without complex string matching or parsing. Another option is a more fine-grained relationship table, as shown below. Remember, more fine-grained does not necessarily mean better.

[user_information]
----------------------
| user_id | name     |
----------------------
|       1 | John Doe |
|       2 | Jane Doe |
|       3 | Doe Doe  |
----------------------

[user_relationships]
---------------------------------------
| user_id | relation  | other_user_id |
---------------------------------------
|       1 | parent_of |             3 |
|       2 | parent_of |             3 |
|       3 |  child_of |             1 |
|       3 |  child_of |             2 |
---------------------------------------

JSON columns used as in the example above violate 1NF, but that is not always a bad trade-off. In some cases, denormalizing improves read speed. This technique, called embedding, stores a snapshot of related data alongside the main data to avoid joins or multiple queries. Stale snapshots can introduce inconsistencies, though. It is up to us to choose the right tool for the job.

Number of attributes is not fixed⌗

JSON allows another way to violate 1NF: storing data with a variable number of attributes.

[user_information]
--------------------------------------------------------------------------------------
| user_id | name     | misc_info                                                     |
--------------------------------------------------------------------------------------
|       1 | John Doe | {"date_of_birth": "2000-01-01"}                               |
|       2 | Jane Doe | {"date_of_birth": "2000-01-01", "hobbies": ["read", "write"]} |
|       3 | Doe Doe  | {"hobbies": ["running"]}                                      |
--------------------------------------------------------------------------------------

In this example, do John Doe, Jane Doe, and Doe Doe each have three attributes: user_id, name, and misc_info? Or does John Doe have user_id, name, and date_of_birth, while Jane Doe has those three plus hobbies? In the first interpretation, misc_info is a single atomic value, so it does not violate 1NF. In the second, it clearly does, making the table harder to query and manage and more prone to data discrepancies.

At this point, we might be tempted to think JSON is evil. But, as discussed above, it depends on how we use it. Treating a JSON value as atomic is generally fine. Treating each key/value pair as an independent attribute, such as using date_of_birth inside misc_info as if it were a regular column, can cause long-term issues as the data model evolves and the system grows.

The upside⌗

The point of 1NF is to eliminate repeating groups and multiple-value columns. I hope the examples show how these structures can complicate the data model and lead to complex queries later. 1NF also helps improve consistency by reducing redundancy. With fewer places to update the same information, it is easier to keep it consistent throughout the database. For example, Doe Doe's information stays the same regardless of which table we query.

This benefit will come up repeatedly throughout the post. After all, the main purpose of normal forms is to improve consistency by reducing redundancy and data anomalies: insert, update, and delete anomalies.

The downside⌗

Although 1NF helps prevent inconsistencies and simplify data models, it has drawbacks. As discussed earlier, embedding data can sometimes perform better than the references that 1NF implicitly encourages. Some NoSQL databases, such as MongoDB, even encourage embedding over referencing. 1NF is also awkward for tree-like data. It often requires complex self-joins and techniques such as path enumeration, nested sets, and adjacency lists, or multiple database queries followed by joins at the application level.

These drawbacks also apply to higher normal forms. Normalization encourages splitting data into tables of atomic values, often requiring multiple JOINs that can slow down reads. Referential keys also complicate sharding across physical or logical locations because we must preserve their integrity.

Functional dependency⌗

I found it hard to understand the next normal forms without first understanding functional dependency. It is central to the second and third normal forms, so let’s look at it first.

Formally, a property A is functionally dependent on B (B -> A) if, at any given time, each value of B maps to exactly one value of A. Knowing B lets us determine A.

[books]
----------------------------------------------------------------
| isbn   | author   | title                                    |
----------------------------------------------------------------
| 000001 | John Doe | John Doe's Introduction to Life          |
| 000002 | John Doe | John Doe's Introduction to Life (Vol. 2) |
| 000003 | Jane Doe | How to Laugh!                            |
| 000004 | Jane Doe | The Memory.                              |
----------------------------------------------------------------

Here, title is functionally dependent on isbn (isbn -> title) because each isbn identifies one title. Similarly, author depends on isbn (isbn -> author). But title is not functionally dependent on author: knowing the author does not identify a single title. For Jane Doe, the title could be How to Laugh! or The Memory. We can write isbn -> [author, title] to express that isbn determines both.

Full functional dependency⌗

Assume A is functionally dependent on a property or set of properties B (B -> A). This dependency is full only if A does not also depend on any proper subset of B.

Full functional dependency usually comes up with composite keys. A property fully dependent on a composite key requires the whole key to determine its value; no proper subset is sufficient.

[ratings]
-----------------------------------------
| isbn   | user_id | user_name | rating |
-----------------------------------------
| 000001 |   00002 | A. Readr  |      4 |
| 000001 |   00001 | Avid R.   |      5 |
| 000002 |   00002 | A. Readr  |      3 |
-----------------------------------------

Assume isbn and user_id form a composite key. rating depends on that key (isbn, user_id -> rating) because each combination identifies one rating. This is also a full functional dependency because isbn or user_id alone can correspond to multiple ratings. Book 000001 has ratings of 4 and 5 from different users. User 00002 has given ratings of 4 and 3 to different books.

Partial functional dependency⌗

Assume A is functionally dependent on a property or set of properties B (B -> A). If this dependency is not full, it is partial.

[ratings]
-----------------------------------------
| isbn   | user_id | user_name | rating |
-----------------------------------------
| 000001 |   00002 | A. Readr  |      4 |
| 000001 |   00001 | Avid R.   |      5 |
| 000002 |   00002 | A. Readr  |      3 |
-----------------------------------------

Using the example from the full functional dependency section, with the same composite key (isbn, user_id), let’s examine user_name. Like rating, it depends on the composite key (isbn, user_id -> user_name): each combination identifies one name. Unlike rating, this dependency is not full. We can determine user_name from user_id alone, a proper subset of the key. This is a partial functional dependency.

Second normal form⌗

A table satisfies the second normal form (2NF) only when:

  • It already satisfies the first normal form.
  • No non-key column is partially dependent on the key.

Why does 2NF disallow partial functional dependencies? Because they cause redundancy, or duplicate data. Let’s revisit the example in the functional dependency section.

[ratings]
-----------------------------------------
| isbn   | user_id | user_name | rating |
-----------------------------------------
| 000001 |   00002 | A. Readr  |      4 |
| 000001 |   00001 | Avid R.   |      5 |
| 000002 |   00002 | A. Readr  |      3 |
-----------------------------------------

As discussed earlier, user_name is partially dependent on the composite key because user_id alone determines it. This violates 2NF and duplicates the user’s name across records. If user 00002 changes their name, we must update multiple records. This is an update anomaly. We might argue that redundancy is not a consistency problem here because the SQL query updates all matching records atomically.

UPDATE 'ratings'
SET
    'user_name' = "A whole new name"
WHERE
    'user_id' = '00002';

The problems go beyond consistency and can lead to lost information. Consider user 00099, who has not rated any books. In the non-normalized table, we have no way to store and retrieve their user_name. This is an insert anomaly. Similarly, if user 00001 deletes their only rating, we lose the information that their name is Avid R.. This is a delete anomaly.

An update anomaly can also slow down writes. Updating multiple records may require loading and modifying multiple physical files, locking more rows, scanning more pages or indexes, and using more CPU and RAM. How can we change this structure to satisfy 2NF?

[ratings]
-----------------------------
| isbn   | user_id | rating |
-----------------------------
| 000001 |   00002 |      4 |
| 000001 |   00001 |      5 |
| 000002 |   00002 |      3 |
-----------------------------

[user_information]
-----------------------
| user_id | user_name |
-----------------------
|   00001 | Avid R.   |
|   00002 | A. Readr  |
|   00099 | Anon U.   |
-----------------------

We have split the original table into two: one for ratings and one for user information. Moving user_name out of the ratings table eliminates its partial dependency on the composite key, and the tables satisfy 2NF. Updating a user now requires changing only one record, eliminating the update anomaly. We can store user 00099's information without any ratings, eliminating the insert anomaly. User 00001 can also delete their ratings without losing their user information, eliminating the delete anomaly.

Splitting tables to eliminate partial dependencies can affect read performance. Suppose we need the reviewer’s name with every rating. Storing user_name alongside ratings may be reasonable if we query them frequently because it avoids repeated joins. We could use the following structure.

[ratings_with_user_name]
-----------------------------------------
| isbn   | user_id | user_name | rating |
-----------------------------------------
| 000001 |   00002 | A. Readr  |      4 |
| 000001 |   00001 | Avid R.   |      5 |
| 000002 |   00002 | A. Readr  |      3 |
-----------------------------------------

[user_information]
-----------------------
| user_id | user_name |
-----------------------
|   00001 | Avid R.   |
|   00002 | A. Readr  |
|   00099 | Anon U.   |
-----------------------

This structure avoids insert and delete anomalies but reintroduces the update anomaly. Denormalization may improve read performance while degrading write performance and allowing inconsistent data. The right trade-off depends on the use case.

Third normal form⌗

A table satisfies the third normal form (3NF) when:

  • It is in the second normal form.
  • No non-key column is transitively dependent on the key column.

A transitive dependency occurs when a non-key column depends on another non-key column, which in turn depends on the key. More formally, consider a table with attributes A, B, and C, where C is the key:

  • Non-key A depends on non-key B (B -> A).
  • Non-key B depends on key C (C -> B).
  • Therefore, A is transitively dependent on C (C -> B -> A).

Put simply, 3NF requires that no non-key column depend on another non-key column in the same table. It addresses the same kinds of problems as 2NF. Let’s look at an example.

[book_with_publisher]
--------------------------------------------------------------------------------------------
| isbn   | title                                    | publisher_id | publisher_name        |
--------------------------------------------------------------------------------------------
| 000001 | John Doe's Introduction to Life          |        P0001 | First Time Publishing |
| 000002 | John Doe's Introduction to Life (Vol. 2) |        P0002 | Pub. Ltd.             |
| 000003 | How to Laugh!                            |        P0001 | First Time Publishing |
| 000004 | The Memory.                              |        P0003 | Yet Another Publisher |
--------------------------------------------------------------------------------------------

In book_with_publisher, isbn is the key. The table violates 3NF because publisher_name, a non-key column, depends on publisher_id, another non-key column. It has the same problems discussed in the 2NF section:

  • Insert anomaly: We cannot store information about a publisher that has not published any books.
  • Delete anomaly: Removing book 000002 also removes the information about publisher P0002.
  • Update anomaly: Changing publisher_name requires updating multiple records.
  • Data redundancy: The same publisher name is stored in multiple places.

To satisfy 3NF, we need to eliminate the transitive dependency isbn -> publisher_id -> publisher_name by splitting the data into these two tables:

[books]
--------------------------------------------------------------------
| isbn   | title                                    | publisher_id |
--------------------------------------------------------------------
| 000001 | John Doe's Introduction to Life          |        P0001 |
| 000002 | John Doe's Introduction to Life (Vol. 2) |        P0002 |
| 000003 | How to Laugh!                            |        P0001 |
| 000004 | The Memory.                              |        P0003 |
--------------------------------------------------------------------

[publishers]
----------------------------------------
| publisher_id | publisher_name        |
----------------------------------------
|        P0001 | First Time Publishing |
|        P0002 | Pub. Ltd.             |
|        P0003 | Yet Another Publisher |
|        P0004 | Anon Publishing       |
----------------------------------------

As with 1NF and 2NF, this improves data integrity and reduces redundancy. The same trade-off applies: more tables can mean more joins for some queries, which may slow down reads. When and how much to normalize depends on balancing these factors for the use case.

References⌗