7 min read

Database normalization: 1NF, 2NF and 3NF with an example

Normalization is the process of organising tables so each fact is stored once. It prevents update anomalies: if a member's email is copied onto every loan, changing it means updating every row, and missing one leaves the data contradicting itself.

The example below follows a community library that tracks loans on a single sheet. Columns that cause the problem at each stage are highlighted.

Unnormalized form (UNF)

One row per loan, with everything copied in. The authors column holds a list of values in a single cell.

loan_sheet

memberemailtitleisbnauthorsbarcodeshelfborrowedduereturned
Lena Moyolena@mail.orgGood Omens9780060853983Gaiman (UK); Pratchett (UK)C-0142F-GAI2026-09-022026-09-162026-09-15
Ravi Shahravi@mail.orgThings Fall Apart9780385474542Achebe (Nigeria)C-0077F-ACH2026-09-052026-09-19
Lena Moyolena@mail.orgThings Fall Apart9780385474542Achebe (Nigeria)C-0078F-ACH2026-10-012026-10-15

First Normal Form (1NF): one value per cell

A table is in 1NF when every cell holds a single, atomic value and each row can be uniquely identified. Splitting the author list gives one row per author, identified by the composite key (barcode, borrowed, author).

loan_sheet

barcode (PK)borrowed (PK)author (PK)nationalitymemberemailtitleisbnshelfduereturned
C-01422026-09-02GaimanUKLena Moyolena@mail.orgGood Omens9780060853983F-GAI2026-09-162026-09-15
C-01422026-09-02PratchettUKLena Moyolena@mail.orgGood Omens9780060853983F-GAI2026-09-162026-09-15
C-00772026-09-05AchebeNigeriaRavi Shahravi@mail.orgThings Fall Apart9780385474542F-ACH2026-09-19

The table is now in 1NF, but data repeats everywhere: the member's name and email appear on every row of every loan.

Second Normal Form (2NF): no partial dependencies

A table is in 2NF when it is in 1NF and every non-key column depends on the whole primary key. With a composite key, look for columns that depend on only part of it. Here, the shelf depends only on the barcode, and nationality depends only on the author. Each moves to a table keyed by what it describes.

loans

barcode (PK)borrowed (PK)memberemailduereturned
C-01422026-09-02Lena Moyolena@mail.org2026-09-162026-09-15
C-00772026-09-05Ravi Shahravi@mail.org2026-09-19

copies

barcode (PK)isbntitleshelf
C-01429780060853983Good OmensF-GAI
C-00779780385474542Things Fall ApartF-ACH

book_authors

isbn (PK)author (PK)nationality
9780060853983GaimanUK
9780060853983PratchettUK
9780385474542AchebeNigeria

Third Normal Form (3NF): no transitive dependencies

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. In the loans table, the email depends on the member, and the member depends on the loan: a transitive dependency. Members, books and authors each get their own table, linked by foreign keys.

members

member_id (PK)nameemailphone
1Lena Moyolena@mail.org555-0101
2Ravi Shahravi@mail.org555-0144

books

book_id (PK)isbntitlepub_year
19780060853983Good Omens1990
29780385474542Things Fall Apart1958

authors

author_id (PK)namenationality
1Neil GaimanUK
2Terry PratchettUK
3Chinua AchebeNigeria

book_authors

book_id (PK)author_id (PK)
11
12
23

copies

copy_id (PK)book_idbarcodeshelf
11C-0142F-GAI
22C-0077F-ACH

loans

loan_id (PK)member_idcopy_idborrowedduereturned
1112026-09-022026-09-162026-09-15
2222026-09-052026-09-19NULL

Every fact is now stored once. A member's email lives in one row of members, however many books they borrow.

A quick test for each form

  • 1NF: does any cell hold a list? Is there a key that identifies each row?
  • 2NF: with a composite key, does any column depend on only part of it?
  • 3NF: does any column depend on something other than the key, such as another column?

Reading only gets you so far. Practise it now, with instant feedback.

Step through it in the lab