Normalization, functional dependencies and the constraints that hold them

Normalization is about one idea: each fact is stored once. When a fact is stored in many rows, the copies can disagree, and then the database contains a contradiction no query can resolve.

A functional dependency A → B means: rows that agree on A must agree on B. A postcode determines its city; a product id determines the product's name. The normal forms are rules about which dependencies a table may contain.

The normal forms, briefly

• 1NF — every column holds one atomic value, and the table has a key. A tags column containing 'sql,tuning' breaks it.
• 2NF — 1NF, and no non-key column depends on only part of a composite key. In order_lines(order_id, product_id, qty, product_name), the name depends on product_id alone.
• 3NF — 2NF, and no non-key column depends on another non-key column. In customers(id, postcode, city), city depends on postcode, not directly on the key.
• BCNF — every determinant is a candidate key. It differs from 3NF only when a table has overlapping candidate keys.

What breaking them costs

• Update anomaly — change a product's name in one row and the other rows still have the old one.
• Insert anomaly — you cannot record a postcode's city until a customer lives there.
• Delete anomaly — delete the last order for a product and you lose the product's name.

The fix is decomposition: move the dependent columns into their own table, keyed by their determinant, and reference it with a FOREIGN KEY. The schema then enforces the rule: a PRIMARY KEY or UNIQUE constraint stops two rows claiming different values for the same key.

Checking the data before you add the constraint

On a legacy table, the dependency you want to enforce is usually already violated somewhere, and the new table's key will refuse the data until each contradiction is resolved. Below, a customers table repeats a city on every row, and the city should move to a postcodes table keyed by postcode. Loading the distinct pairs straight in fails; loading one chosen city per postcode (here the most frequent spelling, ignoring NULLs) succeeds:

1CREATE TABLE customers (id INT PRIMARY KEY, postcode NVARCHAR(10) NOT NULL, city NVARCHAR(30) NULL);2INSERT INTO customers VALUES3  (1, N'0105', N'Tbilisi'), (2, N'0105', N'Tbilisi'), (3, N'0105', N'Tblisi'),4  (4, N'6000', N'Batumi'), (5, N'6000', NULL), (6, N'4600', N'Kutaisi');

1DECLARE @log TABLE (attempt INT, outcome NVARCHAR(200));2DECLARE @postcodes TABLE (postcode NVARCHAR(10) PRIMARY KEY, city NVARCHAR(30) NULL);3 4BEGIN TRY5  INSERT INTO @postcodes (postcode, city)6  SELECT DISTINCT postcode, city FROM customers;7  INSERT INTO @log VALUES (1, N'loaded');8END TRY9BEGIN CATCH10  INSERT INTO @log VALUES (1, CONCAT(N'error ', ERROR_NUMBER(), N', nothing loaded'));11END CATCH;12 13INSERT INTO @postcodes (postcode, city)14SELECT postcode, city15FROM (16  SELECT postcode, city,17         ROW_NUMBER() OVER (PARTITION BY postcode ORDER BY COUNT(*) DESC, city) AS pick18  FROM customers19  WHERE city IS NOT NULL20  GROUP BY postcode, city21) AS ranked22WHERE pick = 1;23INSERT INTO @log24SELECT 2, STRING_AGG(CONCAT(postcode, N' = ', city), N', ') WITHIN GROUP (ORDER BY postcode)25FROM @postcodes;26 27SELECT attempt, outcome FROM @log ORDER BY attempt;

Note that SELECT DISTINCT treats a NULL city as a value of its own, so a postcode stored once with a city and once without also produces two pairs. Which value wins is a business decision; the point of the key is that it forces you to make it once.

A column holding a list is repaired with STRING_SPLIT. Since SQL Server 2022 it takes a third argument, 1, that adds an ordinal column with each item's position, so the original order is not lost.

1CREATE TABLE articles (id INT PRIMARY KEY, title NVARCHAR(40) NOT NULL, tags NVARCHAR(200) NOT NULL);2INSERT INTO articles VALUES (1, N'Seeks', N'indexes,tuning,plans'), (2, N'Locks', N'isolation');

1SELECT a.id AS article_id, s.ordinal AS position, TRIM(s.value) AS tag2FROM articles AS a3CROSS APPLY STRING_SPLIT(a.tags, ',', 1) AS s4ORDER BY a.id, s.ordinal;

Denormalising on purpose

Reporting tables, cached totals and audit snapshots are often denormalised deliberately: an invoice must keep the price it was issued at, even after the product's price changes, so copying the price onto the invoice line is correct, not a 3NF violation. The difference is intent and ownership: the copy records a fact about the invoice, and nothing tries to keep it in sync.

Common mistakes

• Calling every repeated value a violation. The invoice-line price is a different fact from the current price.
• Looking at rows instead of distinct pairs. Three rows saying the same city are not a contradiction; two different (postcode, city) pairs are, and DISTINCT counts a NULL city as one of them.
• Splitting a list with STRING_SPLIT and no ordinal when the order matters; without the third argument the output order is not guaranteed.

What interviewers ask

• Explain 2NF and 3NF with an example. 2NF removes dependencies on part of a composite key; 3NF removes dependencies between non-key columns.
• When would you denormalise? For read-heavy reporting or to record a point-in-time fact, accepting the cost of keeping copies consistent or deliberately not syncing them.
• How would you find data that breaks a rule before adding a constraint? Group by the determinant and keep groups with more than one distinct dependent value.