Database Normal Forms: A Plain-English Guide to Cleaner Data

Most of us don’t think about database design — until we have to use one.

Duplicates accumulate, updates become problematic, and suddenly a simple search produces contradictory results.

Database design issues cause most problems, and database normal forms provide the answer through normalization.

Application of editorial mathematician Edgar F.

Codd in the early 1970s, normalization is a method of organizing relational database tables such that they make using data more simple, accurate, and logical.

This section is an overview of what the norms are, why they are in place, and how each normal form deals with one specific problem similar to what caused the original issue — without requiring an advanced computer science major to comprehend.

First and Second Database Normal Forms: Laying Groundwork

First and second normal forms establish the basics — rules relating to primary keys and how design flaws can make data hard to work with.

First normal form (1NF): Each column has a single value and can be uniquely identified:

A table is in the first normal form when (1) each column contains only one value per row and (2) each row is uniquely identifiable.

This sounds uncontroversial, but is simple to violate.

A customer table that stores all phone numbers in a comma-separated column is an example.

A comma-separated phone number list is a 1NF violation, as it makes searching or filtering by number complicated.

The fix is simple: (1) Columns must hold atomic (indivisible) data (2) There must be no repetition of column families (like Phone1, Phone2, Phone3), (3) every row must have a unique primary key, and (4) don’t store lists or sets in column values Getting to 1NF is the price of entry for creating a relational database.

Second normal form (2NF): Mononyms still buggy:

Second normal form is based on the first.

A table is in second normal form when it passes (1) 1NF and (2) all non-key columns depend solely on the entire primary key (as opposed to just part of it).

2NF violation occurs only if a single table uses a composite primary key — made of more than one column.

Say you hold order information in an “Orders” table, with a plural primary key of OrderID and ProductID, but one of the columns — “SupplierName” — depends only on ProductID.

That’s a partial dependence, and breaks 2NF.

The way round this is to isolate pieces of information in relevant sub-tables as appropriate.

Growing More Complex: Third Database Normal Forms:

Once the first two normal forms are achieved, there is somewhat more refinement to be done.

Third normal form (3NF) and Boyce-Codd normal form (BCNF) investigate subtler relational issues between columns that can lead to inconvenient change anomalies.

Third normal form (3NF): Eliminate transitive dependencies:

A table is in third normal form when (1) it’s in second normal form and (2) it contains no transitive dependencies.

Transitive dependencies happen when a non-key column in a table depends upon another non-primary key column, when it would be better if (as was the case with our first and second normal forms outlined) it depended directly on the primary key.

Say you hold an employees table that contains EmployeeID, DepartmentID and DepartmentLocation.

EmployeeID is the primary key, and DepartmentID depends on that — perfect.

However, DepartmentLocation depends on DepartmentID, not directly on EmployeeID.

This transitive dependency breaks (1) the data becomes varied if a department’s location changes, (2) deletion of any employee record causes data loss and (3) a new empty department can’t be added to the table.

The answer is to isolate all tables containing a subset of data: moving department location to the relevant department table.

Boyce-Codd normal form (BCNF): a nominal improvement over 3NF:

Boyce-Codd normal form is an improved concept of 3NF calculations aimed at some anomalies that 3NF allows.

In particular, every candidate key must be a determinant to ensure the absence of ambiguity in existing candidate or primary keys.

While most data that satisfies 3NF is BCNF-compliant in practice, some complex tables with multiple candidate keys — but the vast majority of databases aim for 3NF and above, and that’s fine.

Pushing beyond the basics: normal forms few people use:

Indeed, most databases in industry aim for third normal form or Boyce-Codd Normal Form, and most are absolutely fine for most applications.

However, there are some higher normal forms dealing with technically complex scenarios involving data anomalies — and knowledge of these is enlightening.

Fourth normal form (4NF): addressing multi-valued dependencies:

Fourth normal form comes into play when considering multi-valued dependencies.

If one column in a table can determine multiple values in another, without further structure, it leads to what is essentially a hidden set of empty values.

For e.g., perhaps a table exists which tracks employees, the skills they have and what language(s) they speak.

Having three skills and two languages, for one employee — you would have to fill 6 empty rows in your table, when the skills and languages have nothing to do with each other.

Splitting the above table into a skills table and a languages table entirely takes care of the issue.

Fifth normal form (5NF): solving join dependencies:

Fifth normal form — also termed project join normal form — deals with join dependencies.

While the oddest of the normal forms, the vast majority of 5NF discussions are academic, and 5NF doesn’t typically apply.

In principle, a join dependency occurs when combining data from one set of tables into a larger one does not provide any benefit, unless you simply satisfy candidate keys.

Producing de-normalized tables moves a step away from 5NF, and read-heavy systems use them when they value query speed, even if they need to forgo a little normalization.

Wrap-up:

What normal forms can do for you?

Most real-world data resides in a 3NF or even BCNF-compliant database, and this approach has no issues.

However, a handful of normal forms can extract even highly inconsistent data into logical tables, including: — 4NF, which addresses multi-valued dependencies — 5NF, which deals with join dependencies — Denormalization, which loosens relational rules for speed, and when you know a table should be denormalized, your application will probably be happy.

In conclusion, it proves to be extremely practical. They remain logical longer when requirements change. Database design (even if only of legacy systems) is a worthwhile task if by putting your tables through these checks, you make even the most casual design trustworthy.

Focus on 1 st to 3 rd forms initially; these apply to most real data use cases. Once you have got these right, you’ll have laid the foundations for the most robust database design.

Meta Description: Database normal forms organize relational tables to eliminate data redundancy and anomalies. Learn 1NF through 5NF with practical examples and implementation tips.

You can read more about technology from our blogs here and here. You can read more about software career from our blog here and here.