Database Design and Normalization

After this lesson, you will be able to: Apply 1NF / 2NF / 3NF, decide when to denormalize, draw ER diagrams, and pick data types thoughtfully.

Bad schema is forever. Good schema is invisible, queries are clean, indexes do their job, no nightly cleanup scripts. This lesson teaches the thinking.

Prerequisites:Transactions and ACID

Normalization (1NF / 2NF / 3NF)

1NF: each cell has one value (no comma-separated lists in a column). 2NF: every non-key column depends on the whole primary key (no partial dependencies). 3NF: no transitive dependencies (non-key column → non-key column). Beyond 3NF (BCNF, 4NF, 5NF), rare in practice. 3NF is the working default.

Bad → Good schema

Denormalized starting point; normalize step by step.

sql

-- BAD: 1NF violation (comma list)
CREATE TABLE bad_users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT,
  tags TEXT   -- 'admin, beta, premium', bad
);

-- BAD: 3NF violation (transitive dep, city implies country)
CREATE TABLE bad_users2 (
  id SERIAL PRIMARY KEY,
  name TEXT,
  city TEXT,
  country TEXT     -- redundant; country can be derived from city
);

-- GOOD: 3NF
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  city_id INT REFERENCES cities(id)
);
CREATE TABLE cities (
  id SERIAL PRIMARY KEY,
  name TEXT,
  country_id INT REFERENCES countries(id)
);
CREATE TABLE countries (id SERIAL PRIMARY KEY, name TEXT);

-- Many-to-many (1NF compliant tags):
CREATE TABLE tags (id SERIAL PRIMARY KEY, name TEXT UNIQUE);
CREATE TABLE user_tags (
  user_id INT REFERENCES users(id) ON DELETE CASCADE,
  tag_id  INT REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (user_id, tag_id)
);

When to denormalize

Reads dominate writes 100:1 → denormalize for read speed. Reporting / analytics DBs → star schema, denormalized. Caching computed values (e.g., 'follower_count' on users) → denormalize + maintain via triggers or app code. Default: stay normalized. Denormalize ONLY after a measured perf problem.

Choosing data types

INT vs BIGINT: INT (32-bit) maxes at 2.1 billion. Use BIGINT for ids if you'll scale past 2B rows. TEXT vs VARCHAR(n): Postgres treats them the same internally; just use TEXT. TIMESTAMP vs TIMESTAMPTZ: ALWAYS TIMESTAMPTZ (timezone-aware). Storing local times is a recipe for pain. DECIMAL vs FLOAT: DECIMAL for money (exact). FLOAT for science (approx). JSONB vs JSON: JSONB indexable + faster. Use JSONB always (in Postgres).

Common mistakes

Comma-separated values in a column (1NF violation; every operation gets harder). INT primary keys at scale (max 2.1B; tough migration). TIMESTAMP without TZ (timezone confusion bugs). Premature denormalization (overcomplicates everything). Skipping foreign keys (data integrity becomes app-level).

←Transactions and ACID

Back to SQL

PostgreSQL-Specific Features→