Database
Beginner 13 min read Database

Database Design & Normalization

Turn messy flat data into a clean relational schema with normalization. Master primary and foreign keys, 1NF/2NF/3NF, relationships, and when it pays to denormalize for performance.

normalization1NF2NF3NFprimary keyforeign keydenormalization
Prerequisites: SQL Fundamentals
Introduction

What is this?

Database design maps a problem's entities and relationships to a stable relational schema. Normalization organizes columns into tables to remove redundancy and dependency anomalies by applying normal forms: 1NF (atomic values, no repeating groups), 2NF (no partial dependencies on a composite key) and 3NF (no transitive dependencies). A normalized schema writes cleanly; real systems often denormalize selectively for read-heavy workloads.

Motivation

Why do you need it?

  • Each fact has a single source of truth, so an update touches exactly one row instead of hundreds.
  • Normalization prevents update, insert and delete anomalies that silently corrupt data over time.
  • Explicit primary and foreign keys give the optimizer strong join paths and the database real integrity.
  • A well-designed schema absorbs new requirements with small, safe migrations.
  • Typed columns and constraints surface bad data at write time rather than years later.
Think about it

Mental model

Imagine

A filing cabinet with one folder per topic

Think of

A normalized schema

You file each fact exactly where it belongs and cross-reference with labels (keys) instead of stuffing photocopies into every related folder.

Mechanics

How it works

  • Identify entities such as customers, orders and products, and map each to a table with a primary key.
  • Apply 1NF: make every column atomic and eliminate repeating groups by extracting rows or child tables.
  • Apply 2NF: remove partial dependencies — non-key columns must depend on the whole composite key, not a part of it.
  • Apply 3NF: remove transitive dependencies — non-key columns must not depend on other non-key columns.
  • Connect tables with foreign keys and pick explicit ON DELETE behavior (CASCADE, SET NULL, RESTRICT).
  • Denormalize deliberately — duplicated columns or precomputed aggregates — only where reads demand it.

Decomposing a flat, redundant table into normalized relations.

Flat order_items table
1NF
2NF
3NF
customers / orders / order_items
JOIN to rebuild the view
Reference

Key commands

\d orders

Inspect the orders table's columns, types and constraints.

\d+ order_items

Show extended detail including indexes and foreign keys.

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'order_items'::regclass;

List every constraint on a table with its full definition.

ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

Add a foreign key to an existing table with explicit delete behavior.

SELECT customer_id, count(*) FROM orders GROUP BY customer_id HAVING count(*) > 1;

Probe for duplicate relationships during schema auditing.

DROP TABLE IF EXISTS order_items_flat;

Drop a legacy table once the normalized design replaces it.

Hands-on

Examples

From flat table to 3NF

From flat table to 3NF
-- Before: customer and product facts repeat on every order line
CREATE TABLE order_items_flat (
  order_id       INT,
  customer_name  TEXT,
  customer_email TEXT,
  product_name   TEXT,
  unit_price     NUMERIC(10,2),
  quantity       INT
);
 
-- After 3NF: split into customers, orders and order_items
CREATE TABLE customers (
  id    BIGSERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);
 
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT REFERENCES customers(id) ON DELETE CASCADE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE order_items (
  order_id     BIGINT REFERENCES orders(id) ON DELETE CASCADE,
  product_name TEXT NOT NULL,
  unit_price   NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
  quantity     INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_name)
);

The flat table repeats customer and product data on every line. The normalized version splits those facts into customers, orders and order_items, each owning the columns that depend on its own key, and reconnects them with foreign keys — a customer email now lives in exactly one place.

Real world

In production

  • Billing systems model customers, subscriptions and invoices as normalized tables joined by keys.
  • E-commerce backends split catalog, inventory and orders so a price change touches exactly one row.
  • Regulated systems keep audit and event tables separate from mutable business data for compliance.
  • Data warehouses apply star schemas with normalized dimension tables feeding a wide fact table.
Guidelines

Best practices

  • Design for 3NF by default: every non-key column depends on the key, the whole key and nothing but the key.
  • Use surrogate integer or UUID primary keys, but keep natural identifiers as UNIQUE constraints.
  • Model relationships with foreign keys and choose ON DELETE behavior explicitly.
  • Avoid generic 'details' JSON columns for data you filter on — use typed columns or child rows.
  • Name tables and columns consistently (snake_case, singular tables) and document the schema.
Watch out

Common mistakes

  • Copying a spreadsheet into one wide table with repeating columns like phone1, phone2, phone3.
  • Storing values that should be entities — a country name string instead of a referenced lookup table.
  • Over-normalizing to 5NF/6NF and paying heavy join costs for a theoretical purity win.
  • Using ENUM columns for data that changes over time, which needs a table rebuild to extend.
Root cause analysis

Common issues

Error
duplicate key value violates unique constraint
Root cause

An INSERT conflicts with an existing unique value.

Fix

Deduplicate the input first, or use ON CONFLICT DO UPDATE/DO NOTHING.

Error
update or delete on table "customers" violates foreign key constraint
Root cause

Child rows still reference the customer you tried to delete.

Fix

Delete or reassign child rows first, or add ON DELETE CASCADE.

Error
null value in column "email" violates not-null constraint
Root cause

A NOT NULL column received no value.

Fix

Supply the value, or relax the constraint if the field is genuinely optional.

When things break

Debugging tips

  • Query information_schema.columns to see the whole schema without clicking through tables.
  • Hunt for duplicate data with GROUP BY ... HAVING count(*) > 1.
  • Test constraints inside a transaction (BEGIN; INSERT bad data; ROLLBACK;) so nothing persists.
  • Join-heavy plans with tiny row counts hint that data was split too far or keys were mislabeled.
Interviews

Interview checkpoint

Practice

Hands-on lab

Normalizing a Legacy Schema
30 min · beginner
In progress
Objectives
  • Identify redundancy and dependency anomalies in a flat table
  • Decompose it into 3NF relations
  • Add primary keys, foreign keys and unique constraints
  • Reconstruct the original view with a JOIN query

Normalizing a Legacy Schema

30 min · beginner

0/5 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/3
1

Which normal form eliminates transitive dependencies between non-key columns?

2

Duplicating a customer's email into the orders table is safe as long as the data matches today.

3

Orders store customer_name and customer_email on every order. The customer changes their email. What goes wrong?

Quick reference

Cheat sheet

Normal forms
1NFAtomic values, no repeating groups
2NFNo partial key dependencies
3NFNo transitive dependencies
BCNFEvery determinant is a candidate key
Key vocabulary
primary keyUnique identifier of a row
foreign keyReferences another table's primary key
composite keyPrimary key made of multiple columns
surrogate keySystem-generated id (BIGSERIAL, UUID)
natural keyBusiness identifier like email or SKU
Go deeper

Official documentation

PostgreSQL CREATE TABLE
docs
Foreign Keys tutorial
docs
Constraints documentation
docs
Use The Index Luke — table scans
docs