SQL Server › Normalization

What is the third normal form, and what kind of dependency does it specifically eliminate?

Intermediate

Answer

The third normal form requires a table to already satisfy second normal form, and additionally requires that non key columns do not depend on other non key columns, eliminating what is called a transitive dependency, where one column's value depends on another column that is not itself the primary key.

-- Violates third normal form: City depends on ZipCode, not directly on CustomerId
CREATE TABLE Customers (CustomerId INT PRIMARY KEY, ZipCode VARCHAR(10), City VARCHAR(50));

-- Fixed: City moved to a separate zip codes table
CREATE TABLE ZipCodes (ZipCode VARCHAR(10) PRIMARY KEY, City VARCHAR(50));
CREATE TABLE Customers (CustomerId INT PRIMARY KEY, ZipCode VARCHAR(10));

Real-world example A customer database originally stored both a zip code and its corresponding city directly on the customer table, but was redesigned to move the city information into a separate zip codes table, removing the transitive dependency and properly reaching third normal form.

Common follow-ups: How do you identify a transitive dependency in an existing table design?;Is achieving third normal form typically considered sufficient for most practical applications?

Data Types & Schema Design;Constraints (Primary Key Foreign Key Check & Unique)