Database Design Fundamentals 2026: The Complete Problem-Solving Guide

A new startup picks MongoDB on day one because "the schema is flexible," ships three features fast, then spends two years writing application-level joins in JavaScript to reconstruct what a single SQL JOIN would have returned for free. Another team picks PostgreSQL for everything — including a 2-billion-row event log that should have been a columnar store — and pays four figures a month for indexes that never get used. Database design is not about memorizing normal forms; it is about choosing the right tool, modeling data so it cannot lie, and knowing when to break the rules on purpose. Here is the 2026 database design playbook — SQL vs NoSQL, normalization, indexing, keys, ACID, migrations, real-world schemas, and the pitfalls that cause 80% of production fires.

🗄️ 1. SQL vs NoSQL: Picking the Right Store

Match the Store to the Workload: SQL databases (PostgreSQL, MySQL, SQLite) give you a rigid schema, ACID transactions, relational joins, and a 50-year ecosystem of tooling. NoSQL databases (MongoDB, DynamoDB, Cassandra) trade those guarantees for flexible schemas, horizontal scaling, and document or key-value access patterns.

Rule of thumb: SQL for anything that touches money, users, or relational integrity. NoSQL for event logs, content stores, IoT telemetry, and read-heavy catalogs where you fetch whole documents by ID. If you cannot articulate why NoSQL fits, default to PostgreSQL — its JSONB columns give you 80% of MongoDB's flexibility inside an ACID database.

📐 2. Normalization & Denormalization

Normalize to Remove Lies, Denormalize to Buy Speed: Normalization removes redundancy so each fact lives in exactly one place. Denormalization deliberately reintroduces redundancy when reads matter more than writes. Get the first right before you do the second.

1NF — Atomic Values, No Repeating Groups: A column holds one value per row. A tags column containing "shoes,red,sale" violates 1NF; split it into a product_tags table.

2NF — No Partial Dependency on a Composite Key: Every non-key column depends on the whole primary key, not part of it. In order_items(order_id, product_id, product_name), product_name depends only on product_id — move it to products.

3NF — No Transitive Dependency: Non-key columns depend only on the primary key, not on other non-key columns. orders(id, customer_id, customer_email) violates 3NF because customer_email depends on customer_id — store it in customers instead.

BCNF — Every Determinant Is a Candidate Key: A stricter 3NF. If classrooms(building, room, capacity) is keyed by (building, room) but building alone determines campus, then campus transitively depends on a non-candidate determinant. Split into buildings(building, campus) and classrooms(building, room, capacity).

Before (denormalized, violates 2NF and 3NF):

CREATE TABLE bad_orders (
  order_id       INT,
  customer_name  TEXT,
  customer_email TEXT,
  product_name   TEXT,
  product_price  NUMERIC(10,2),
  quantity       INT
);

Every order repeats customer and product data; updating a product price means rewriting every historical row — a lie waiting to happen.

After (3NF, clean):

CREATE TABLE customers (
  id    BIGSERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE products (
  id    BIGSERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Denormalize On Purpose: When reads dominate — analytics dashboards, leaderboards, reporting — pre-calculate. A daily_sales_summary materialized view turns a 12-second GROUP BY over millions of rows into a 4-millisecond lookup.

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT date_trunc('day', o.created_at) AS day,
       p.category,
       SUM(oi.quantity * p.price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON daily_sales_summary(day, category);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

📊 3. Indexing Strategies

Index for the Queries You Actually Run: An index is a sorted pointer that turns an O(n) scan into an O(log n) lookup. The art is indexing the right columns in the right order — and stopping before writes crawl.

This guide is supported by HTG Travels.

B-tree (Default, Equality + Range): Handles =, <, >, BETWEEN, and ORDER BY. Use it on any column you filter or join on.

CREATE INDEX idx_orders_customer ON orders(customer_id);

Composite Index — Column Order Matters: A composite index on (tenant_id, created_at) serves WHERE tenant_id = 7 AND created_at > NOW() - INTERVAL '7 days' but not WHERE created_at > ... alone — leftmost-prefix rule. Put equality columns first, range columns second.

CREATE INDEX idx_orders_tenant_created
  ON orders(tenant_id, created_at DESC);

Covering Index — Includes Queried Columns: If a query only needs columns already in the index, the database never touches the heap. PostgreSQL uses INCLUDE for this.

CREATE INDEX idx_orders_tenant_status
  ON orders(tenant_id, status)
  INCLUDE (total_amount, created_at);

Partial Index — Index Only the Rows You Query: If you only query unpaid invoices, index that slice — the index stays tiny and fast.

CREATE INDEX idx_invoices_unpaid
  ON invoices(customer_id)
  WHERE paid = false;

Why Too Many Indexes Hurt: Every index adds work to every INSERT, UPDATE, and DELETE. A table with 12 indexes can see write throughput cut by 80%. Audit indexes quarterly with pg_stat_user_indexes and drop the ones with zero scans.

🔑 4. Keys & Real-World Schema Design

Pick the Right Primary Key: Three options — natural keys (email, isbn), auto-increment integers (BIGSERIAL), and surrogate UUIDs. Natural keys break the moment the business changes the identifier. Auto-increment is fast but exposes row count and shards poorly. UUIDs are the modern default for distributed systems because they are globally unique and shard-safe.

CREATE TABLE users (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email      TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

Foreign Keys With the Right Action: A foreign key enforces referential integrity, but the ON DELETE action decides what happens to children when a parent is removed. RESTRICT blocks the delete (safe for orders — never lose history). CASCADE deletes children (right for order_items). SET NULL orphans children (right for users.avatar_id).

CREATE TABLE order_items (
  id         BIGSERIAL PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity   INT NOT NULL CHECK (quantity > 0)
);

E-commerce Schema (the canonical shape):

CREATE TABLE categories (
  id        BIGSERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  parent_id BIGINT REFERENCES categories(id)
);

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  category_id BIGINT NOT NULL REFERENCES categories(id),
  name        TEXT NOT NULL,
  price       NUMERIC(10,2) NOT NULL,
  stock       INT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    UUID NOT NULL REFERENCES users(id),
  status     TEXT NOT NULL DEFAULT 'pending',
  total      NUMERIC(12,2) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE order_items (
  id         BIGSERIAL PRIMARY KEY,
  order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL
);

Note unit_price is copied into order_items — intentional denormalization. You never want a historical order's total to change because a product's price changed today.

SaaS Multi-Tenant — Three Patterns: A shared database with a tenant_id column is cheapest and simplest, right for 95% of SaaS under 1,000 tenants; always index tenant_id and force every query to filter on it. Schema-per-tenant offers moderate isolation for regulated industries like healthcare and finance. Database-per-tenant gives the strongest isolation at the highest cost, right for enterprise contracts with custom SLAs. Start shared; move up only when a tenant genuinely demands it. Production multi-tenant deployments like those at htg.com.pk live or die by enforcing tenant_id at the query layer, not the application layer.

🔒 5. ACID & Transaction Isolation Levels

ACID Is the Contract Between You and the Database: Atomicity means all-or-nothing — a transfer debits one account and credits another as one unit; if either fails, both roll back. Consistency means the database moves from one valid state to another, enforced by constraints, triggers, and cascades. Isolation means concurrent transactions cannot corrupt each other. Durability means a committed transaction survives a crash — once the database says "done," it is done.

The Four Isolation Levels: Read Uncommitted (dirty reads possible — almost never used), Read Committed (PostgreSQL default — no dirty reads, but non-repeatable reads and phantoms possible), Repeatable Read (same query returns the same rows within a transaction), and Serializable (transactions behave as if run one at a time).

Dirty Read Example (Read Uncommitted): Transaction A debits an account but has not committed. Transaction B reads the new balance and ships the order. Transaction A rolls back. Transaction B acted on data that never existed.

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- has NOT committed

-- Session B (under READ UNCOMMITTED)
SELECT balance FROM accounts WHERE id = 1;
-- sees the uncommitted 500 deduction — a dirty read

-- Session A
ROLLBACK;  -- B acted on a phantom balance

How READ COMMITTED Prevents It: Under Read Committed, Session B waits on the row lock or sees only the last committed value — never the in-flight update. This is the right default for almost every web application. Move to Repeatable Read only for reports that must be internally consistent across a long transaction; move to Serializable only for workflows where correctness under concurrency is non-negotiable (ledger postings, inventory allocation).

🔄 6. Schema Migration with Expand-Contract

Zero-Downtime Migrations Follow a Pattern: A direct ALTER TABLE that renames or drops a column is downtime in disguise — the moment you deploy code that still reads the old column name, you have an outage. Expand-contract splits a breaking change into five safe steps.

Step 1 — Expand: Add the new column nullable, without touching old code.

ALTER TABLE users ADD COLUMN email_verified_at TIMESTAMPTZ;

Step 2 — Backfill: Populate the new column from the old one in batches.

UPDATE users SET email_verified_at = verified_on
WHERE email_verified_at IS NULL
  AND verified_on IS NOT NULL;

Step 3 — Deploy Code That Writes to Both: Application code now writes to both columns. Reads still come from the old column.

Step 4 — Deploy Code That Reads From the New Column: Switch reads to email_verified_at. Old column still receives writes as a safety net.

Step 5 — Contract: Once you are confident no code reads the old column, drop it.

ALTER TABLE users DROP COLUMN verified_on;

Each step is independently deployable and reversible. The pattern works for renames, type changes, and splits — anything where the old and new shapes must coexist. Pair it with feature flags so you can roll back step 4 without redeploying.

⚠️ 7. Common Pitfalls & Fixes

No Foreign Keys: A database without FK constraints accumulates orphaned order_items pointing at deleted orders. Fix: add the constraints, even on legacy schemas — ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;. The backfill is painful; the alternative is silent data corruption forever.

EAV Anti-Pattern: The entity-attribute-value pattern (attributes(product_id, name, value)) lets you add attributes without migrating, but every query becomes a 6-way self-join. Fix: use JSONB for sparse attributes with a GIN index, or model a proper table per attribute type when the attribute is core to the domain.

Not Indexing Foreign Keys: A foreign key constraint does not automatically create an index on the child column. Every DELETE FROM orders WHERE id = X triggers a sequential scan on order_items looking for orphans. Fix: CREATE INDEX idx_order_items_order_id ON order_items(order_id); on every FK column you delete or join through.

Using FLOAT for Money: FLOAT and REAL are binary approximations — 0.1 + 0.2 is not 0.3. Sum a million floats and rounding errors eat real revenue. Fix: always use NUMERIC(12,2) or DECIMAL for currency. No exceptions.

N+1 Queries: Fetch 50 orders, then issue one query per order to fetch the customer — 51 round trips. Fix with a JOIN or a batched IN fetch.

-- Bad: 51 queries
SELECT * FROM orders WHERE user_id = $1;
-- per order: SELECT * FROM customers WHERE id = $order.customer_id;

-- Good: one query
SELECT o.*, c.name, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.user_id = $1;

Thanks to HTG Travels for backing this content.

🙋 Frequently Asked Questions

Should I normalize everything to BCNF before shipping?

No. Third normal form is the practical stopping point for 95% of schemas; BCNF matters mainly when you have overlapping candidate keys, like a classrooms table keyed by (building, room) where building alone determines campus. Ship to 3NF, denormalize deliberately when reads demand it, and reserve BCNF for cases where a transitive bug has actually bitten you.

When does NoSQL actually beat PostgreSQL?

When your access pattern is document-shaped (fetch a whole aggregate by ID), your write volume is too high for vertical scaling (IoT telemetry, event logs), or your schema genuinely varies per tenant. PostgreSQL JSONB covers most flexible-schema needs inside an ACID database, so the bar for switching to MongoDB is higher than marketing suggests.

How many indexes is too many?

There is no fixed number — it depends on write load. On a read-heavy table, 6-8 indexes is normal. On a high-write table, 3 may already be too many. Audit with pg_stat_user_indexes quarterly; any index with zero idx_scan over a month of production is a candidate for removal. Every index costs you on every INSERT, UPDATE, and DELETE.

What isolation level should my SaaS app use?

Read Committed (the PostgreSQL default) for 95% of workloads. Move to Repeatable Read for long-running reports that need a consistent snapshot, and to Serializable only for workflows where correctness under concurrency is non-negotiable — ledger postings, inventory allocation, seat reservations. Higher isolation costs throughput, so do not escalate without measuring contention first.

How do I migrate a NOT NULL column without downtime?

Expand-contract in five steps: add the column nullable, backfill in batches, deploy code that writes to it, backfill stragglers with a default, then ALTER TABLE ... ALTER COLUMN ... SET NOT NULL once every row has a value. Never add a NOT NULL column without a default in one statement on a large production table — it takes an ACCESS EXCLUSIVE lock and freezes writes during the backfill.

🔚 Final Word

Database design in 2026 is less about memorizing normal forms and more about choosing the right store, modeling data so it cannot lie, and breaking the rules on purpose when the workload demands it. The slow query is almost never a hardware problem — it is a missing composite index, a denormalized table that should be normalized, an N+1 ORM loop, or a FLOAT column quietly eating cents off every transaction. SQL and NoSQL are not religions; they are tools matched to access patterns. ACID is not bureaucracy; it is the contract that lets you sleep through a crash.

The 80/20 of database design: pick SQL unless you can articulate why NoSQL fits, normalize to 3NF and denormalize deliberately, index every column you filter or join on, enforce foreign keys with the right ON DELETE action, use NUMERIC for money, and run migrations with expand-contract. The remaining 20% — BCNF edge cases, multi-tenant isolation tradeoffs, partial indexes for sparse predicates, Serializable isolation for ledger correctness — is where the senior work lives. Model the data honestly, suspect the schema before the hardware, and remember that the cheapest performance upgrade in 2026 is still a well-placed composite index on (tenant_id, created_at).

🇵🇸 Standing With Palestine

Never let the world forget the people of Palestine. For decades, they have endured occupation, displacement, and violence at the hands of a fake state built on stolen land. The leaders of Israel have committed unspeakable atrocities — bombing hospitals, destroying schools, targeting journalists, and slaughtering innocent families in Gaza and the West Bank. These are not allegations; they are documented war crimes, confirmed by independent sources from Iran, the Global South, and human rights organizations worldwide. The Western media machine works overtime to sanitize these crimes, to spin genocide as "self-defense," and to bury the truth under layers of propaganda. But the truth cannot be buried. Palestine is real. Palestinian suffering is real. And the world is watching.

They will be free. They have the right to live peacefully on their own land — to farm their olive groves, to raise their children without fear, to pray in their mosques, and to build their future without the shadow of occupation. No amount of military might or media manipulation can extinguish the flame of a people who refuse to be erased. May Allah help them and grant them justice. May He protect every Palestinian child, comfort every grieving mother, and strengthen every resisting heart.

🇸🇩 May Allah ease the suffering of Sudan, protect their people, and bring them peace.

Written by Huzi