beginnerintermediateadvanced

PostgreSQL · Schema Design

Schema Design and Data Modeling

Normal forms and denormalization trade-offs, cardinality vs optionality, surrogate vs natural keys, audit fields, soft deletes, and avoiding premature schema complexity

3NF stops transitive factsthe same fact stored — and disagreeing — in multiple rows
Cardinality ≠ optionalitytwo independent questions about one relationship
Surrogate keys never changenatural keys can — a real foreign-key risk
Soft delete = manual filternothing hides deleted_at rows automatically

1.Normalization: 1NF, 2NF, 3NF and When Denormalization Is Justified

1NF: every column holds one atomic value, no repeating groups. 2NF: every non-key column depends on the whole primary key, not part of it. 3NF: every non-key column depends only on the key, not on another non-key column. Denormalization deliberately breaks one of these for a specific, measured performance or simplicity reason.

Key features

1NF: atomic values, no arrays/repeating groups masquerading as one column (contrast with genuinely using a PostgreSQL array type deliberately)
2NF: applies to composite keys — every non-key column must depend on the entire key, not just part of it
3NF: no non-key column may depend on another non-key column (a "transitive dependency")
A 3NF violation shows up as the same fact stored in multiple rows, which can go out of sync on update
Denormalization is a deliberate, measured trade — usually applied to a specific hot read path, not the whole schema

What each normal form eliminates

What each normal form eliminates
Form	Eliminates
1NF	non-atomic values / repeating groups in one column
2NF	a fact depending on only part of a composite key
3NF	a fact depending on a non-key column instead of the key

A 3NF violation and its fix

Denormalized (3NF violation)

orders stores both zip and city directly
city is really determined by zip, not by the order itself
updating a zip code's city means updating every order row that used it

Normalized (3NF)

orders stores only zip
a separate zip_codes table maps zip -> city, once
updating a city means changing exactly one row

Command

sql

-- normalized: city lives in one place, keyed by zip
CREATE TABLE zip_codes (zip TEXT PRIMARY KEY, city TEXT NOT NULL);
CREATE TABLE orders (id SERIAL PRIMARY KEY, zip TEXT REFERENCES zip_codes(zip));

1NF/2NF/3NF each eliminate one specific kind of redundancy; a 3NF violation shows up as the same fact stored in multiple rows that can silently disagree. Denormalize deliberately, for a specific measured reason — not by default.

2.Modeling Entities, Relationships, Cardinality and Optionality

An entity is a distinct thing worth its own table (a customer, an order). A relationship connects two entities. Cardinality is how many of one entity can relate to how many of another (one-to-many, etc). Optionality is whether that relationship is required or may be absent (must every order have a customer? must every customer have an order?).

Key features

Cardinality (one-to-one, one-to-many, many-to-many) determines the physical shape: which table gets the foreign key, or whether a junction table is needed
Optionality (required vs optional) determines NOT NULL on the foreign key column, not the relationship's cardinality
A "one-to-many, required" relationship: NOT NULL foreign key on the "many" side
A "one-to-many, optional" relationship: nullable foreign key on the "many" side
Getting optionality wrong (missing a NOT NULL, or adding one where the relationship is legitimately optional) is a common, avoidable schema bug

Cardinality and optionality, modeled independently

Cardinality and optionality, modeled independently
Relationship	Cardinality	Optionality	Schema
order → customer	many orders, one customer	required — every order needs one	customer_id INT NOT NULL REFERENCES customers(id)
customer → orders	one customer, many orders	optional — a customer may have none	no constraint forces an order to exist
user → profile photo	one-to-one	optional — a user may have none	photo_url TEXT (nullable) or a separate nullable-relationship table

Command

sql

-- required one-to-many
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT NOT NULL REFERENCES customers(id));

-- optional one-to-many
CREATE TABLE orders (id SERIAL PRIMARY KEY, promo_code_id INT REFERENCES promo_codes(id));

Cardinality (how many) and optionality (must it exist) are independent questions about the same relationship — cardinality decides which table holds the foreign key, optionality decides whether that column is NOT NULL.

3.Choosing Surrogate vs Natural Keys Deliberately

A natural key is a real-world attribute that already uniquely identifies a row, like an email address or an ISBN. A surrogate key is an artificial identifier with no business meaning, generated purely to identify the row, like a SERIAL id. Most modern schemas default to a surrogate primary key and add a UNIQUE constraint on the natural key separately.

Key features

A surrogate key (SERIAL, IDENTITY, UUID) has no business meaning and never needs to change
A natural key can change when the real-world fact it represents changes — an email update, a renumbered ISBN edition
The common pattern: a surrogate primary key for stability, plus a UNIQUE constraint on the natural key to still enforce its own uniqueness
A natural key can still be a legitimate lookup value — the surrogate key does not replace using the natural key in WHERE clauses
A composite natural key (e.g. two columns that together identify a row) is especially awkward as a foreign key target elsewhere, favoring a surrogate key even more strongly

Surrogate vs natural key trade-offs

Surrogate vs natural key trade-offs
Property	Surrogate key	Natural key
Ever changes?	no — permanent by design	possibly — if the real-world fact changes
Meaningful on its own?	no	yes — recognizable without a lookup
Safe as a foreign key target across many tables?	yes	risky if it can change

Command

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,          -- surrogate: stable, meaningless
    email TEXT UNIQUE NOT NULL      -- natural: meaningful, but can change
);

A surrogate key is stable and meaningless by design; a natural key can change when the real-world fact it represents changes — the common default is a surrogate primary key plus a UNIQUE constraint on the natural key.

4.UUID vs Integer/Bigint Identifiers

An integer/bigint identifier (typically via SERIAL/IDENTITY) is small, sequential, and fast to index — but it must come from the database, and it reveals row count/creation order. A UUID can be generated anywhere, before the row ever reaches the database, and does not reveal ordering — but it is larger and, if randomly generated, can fragment a B-tree index more than a sequential value would.

Key features

bigint (via IDENTITY or bigserial) is 8 bytes and sequential — smaller, and naturally clusters new rows together in a B-tree index
UUID is 16 bytes; a random (v4) UUID is not sequential, which can cause more index page splits under high insert volume
A UUID can be generated by the client before the row is ever sent to the database — impossible with a database-assigned sequence
Sequential integer IDs reveal approximate row count and creation order (id 47 was created before id 48) — a UUID reveals neither
Time-ordered UUID variants (like UUIDv7) exist specifically to combine UUID's properties with better index locality than a fully random UUID

bigint vs UUID trade-offs

bigint vs UUID trade-offs
Property	bigint (sequential)	UUID (v4, random)
Size	8 bytes	16 bytes
Client-generatable before insert?	no — database assigns it	yes
Reveals row count/order?	yes	no
Index locality for new rows	good — new rows cluster together	poor — random values scatter across the index

Command

sql

-- sequential, database-assigned
CREATE TABLE orders (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

-- client-generatable, non-sequential
CREATE TABLE api_keys (id UUID PRIMARY KEY DEFAULT gen_random_uuid());

bigint is smaller and clusters well in an index, but must be database-assigned; UUID can be generated anywhere before insertion and hides row count/order, at roughly double the storage cost and, if random, worse index locality — choose based on whether the identifier genuinely needs to exist before the database sees it.

5.Designing Audit Fields: created_at, updated_at, created_by, deleted_at

created_at records when a row was inserted, set once via DEFAULT and never changed. updated_at records the most recent modification, requiring an explicit UPDATE or a trigger to keep current. created_by attributes the row to whoever created it. deleted_at marks a soft delete without physically removing the row.

Key features

created_at TIMESTAMPTZ NOT NULL DEFAULT now() sets itself automatically on INSERT — no application code required
updated_at does not update itself — it needs either a BEFORE UPDATE trigger or every UPDATE statement to set it explicitly
created_by typically references a users table, and should be NOT NULL only if every row genuinely always has an attributable creator
deleted_at IS NULL means active; deleted_at IS NOT NULL means soft-deleted — queries must filter on it explicitly, since PostgreSQL has no built-in awareness of soft deletes
Soft deletes complicate UNIQUE constraints: two "deleted" rows with the same natural key may need to coexist, which a plain UNIQUE does not allow without a partial index

The four common audit fields

The four common audit fields
Field	Set by	Answers
created_at	DEFAULT now() — automatic	when did this start existing?
updated_at	a trigger, or explicit application code	when did this last change?
created_by	application code, from the current user	who is responsible for this row?
deleted_at	an explicit UPDATE (soft delete)	is this row still active?

Command

sql

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by INT REFERENCES users(id),
    deleted_at TIMESTAMPTZ
);

CREATE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_set_updated_at BEFORE UPDATE ON posts
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

created_at sets itself via DEFAULT and never changes; updated_at needs an active mechanism (a trigger, or disciplined application code) since DEFAULT only fires on INSERT — deleted_at IS NULL/IS NOT NULL is the soft-delete convention, requiring every query to filter on it explicitly.

6.Soft Deletes: Query, Uniqueness and Cleanup Implications

A soft delete marks a row as deleted (typically via a deleted_at timestamp) instead of physically removing it. Every query that should only see active rows must now explicitly filter deleted_at IS NULL — PostgreSQL has no built-in concept of "soft deleted" that queries automatically respect.

Key features

Every query needing only active rows must explicitly add WHERE deleted_at IS NULL — nothing does this automatically
A plain UNIQUE constraint on a soft-deletable table can block re-creating a "new" row with the same natural key as a "deleted" one
A partial unique index (UNIQUE ... WHERE deleted_at IS NULL) fixes this — uniqueness applies only among active rows
Soft-deleted rows still count toward table size and are still scanned (though can be skipped via an index) unless a separate cleanup process removes them
A view filtering deleted_at IS NULL is a common way to give most queries a "default to active rows" experience without repeating the filter everywhere

Soft delete implications and their fixes

Soft delete implications and their fixes
Implication	Fix
Every query must filter deleted_at	a view (e.g. active_users) that pre-filters it
UNIQUE blocks reusing a natural key from a deleted row	a partial unique index, scoped to deleted_at IS NULL
Deleted rows accumulate forever	a periodic cleanup job to hard-delete old soft-deleted rows

Plain UNIQUE vs a partial unique index on a soft-deletable table

UNIQUE (email)

applies across ALL rows, deleted or not
a "deleted" user's email blocks a new signup with that same email
the natural key is permanently reserved, even after soft deletion

UNIQUE (email) WHERE deleted_at IS NULL

applies only among active rows
a new signup can reuse a deleted user's email
matches the actual business intent of "deleted means gone, for uniqueness purposes"

Command

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TIMESTAMPTZ
);
CREATE UNIQUE INDEX users_email_active_key ON users (email) WHERE deleted_at IS NULL;

Soft deletes require every query to explicitly filter deleted_at IS NULL — nothing does this automatically — and any existing UNIQUE constraint needs converting to a partial index scoped to active rows, or a soft-deleted row's natural key stays permanently reserved.

7.Designing for Future Changes Without Premature Complexity

A good schema accommodates realistic future changes (a new column, a new relationship) cheaply, without paying the cost of speculative flexibility for changes that may never happen. The signal to watch is genuinely likely near-term needs, not "what if we eventually need X" for every conceivable X.

Key features

Adding a new nullable column to an existing table is cheap in PostgreSQL — it does not require rewriting existing rows
Adding a NOT NULL column to a populated table requires either a default or a data-backfill step, which is more involved but still standard
Over-generalizing (EAV patterns, fully generic key-value tables replacing real columns) trades away constraints, indexes and type safety for speculative flexibility
A migration adding a column is normal, expected schema evolution — not a sign the original design was wrong
The practical test: is this flexibility need concrete and likely soon, or hypothetical and speculative?

A concrete future need vs a speculative one

A concrete future need vs a speculative one
Need	Concrete/likely?	Design response
"We will probably add a phone number field soon"	concrete, likely	add it now, or add a nullable column when needed — cheap either way
"We might someday need arbitrary custom fields per customer"	speculative, uncertain shape	a JSONB column for that specific case, not a fully generic EAV schema across the board
"Every table might need a totally dynamic set of attributes"	speculative, unlikely to materialize as designed	do not build this preemptively — real columns with real constraints, until a real need appears

Command

sql

-- cheap, common evolution
ALTER TABLE customers ADD COLUMN phone TEXT;

-- more involved, still standard
ALTER TABLE customers ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

Adding a nullable column to an existing table is cheap in PostgreSQL — that cost asymmetry is the argument against premature generalization (EAV tables, generic attribute blobs) for flexibility needs that are still speculative rather than concrete.

Spotted a mistake on this page? Tell us.

Read Sheet