paulserban.eu

Writing Edition

Paul Serban

Writing, snippets & book notes

Vol. 2026

Relational Databases Explained: What, How, When, and Why

A practical guide to the relational model, SQL fundamentals, and the engineering decisions behind choosing a relational database

Introduction

Almost every backend engineer touches a relational database within their first week on the job, yet surprisingly few can explain why the relational model won and stayed dominant for over five decades. Postgres, MySQL, SQLite, SQL Server, and Oracle all trace their lineage back to a single 1970 paper by Edgar F. Codd, "A Relational Model of Data for Large Shared Data Banks," published in Communications of the ACM. That paper proposed something radical for its time: instead of navigating data through pointers and hierarchies, you could describe data as sets of tuples and query it declaratively, letting the database figure out the fastest way to answer your question.

This article is a grounded, practical walkthrough of relational databases for engineers who want more than a surface-level "SQL is tables" understanding. We'll cover the theoretical foundation, the mechanics of how a relational engine actually processes a query, working code examples in SQL, TypeScript, and Python, the trade-offs that make relational databases a poor fit in some situations, and a set of best practices drawn from real production systems. By the end, you should be able to reason about schema design, transaction behavior, and indexing decisions with the same confidence you'd bring to choosing a data structure in an algorithms course.

What Problem Are Relational Databases Solving?

Before the relational model, the dominant database paradigms were hierarchical (IBM's IMS) and network-based (CODASYL). Both required application code to know the physical structure of the data - to explicitly walk parent-child pointers or traverse a graph of records to find what it needed. This tightly coupled the application to the storage layout: if you changed how data was organized on disk, you had to rewrite the code that read it. Codd's insight was to separate the logical view of data (rows and columns, sets of facts) from its physical representation, and to let a query language express what you want rather than how to get it.

That separation is the single most important idea in the relational model, and it's still the reason SQL databases remain useful today. When you write SELECT * FROM orders WHERE customer_id = 42, you are not telling the database which index to use, which disk block to read, or how to join across tables if a join were involved. You are describing the result you want, and a component called the query optimizer decides the execution strategy. This is what allows database vendors to introduce new indexing schemes, storage formats, or parallel execution engines without breaking your application code - the query looks identical whether it runs on 1990s hardware or a modern NVMe-backed cluster.

The practical problem this solves for engineering teams is data integrity under concurrent, evolving access patterns. Multiple services, batch jobs, and human operators might all touch the same tables simultaneously. A relational database gives you a shared, consistent, queryable source of truth with enforced rules (foreign keys, uniqueness constraints, data types) so that invalid states - an order referencing a customer that doesn't exist, a negative account balance, duplicate primary keys - are rejected at the data layer instead of relying on every piece of application code to remember to check. This is fundamentally a risk-reduction tool as much as a data-storage tool.

The Relational Model: Core Concepts

Tables, Rows, and Keys

A relational database organizes data into relations, which in practice you know as tables. Each table has a fixed set of typed columns (the schema) and an arbitrary number of rows, where each row represents one fact or entity instance. Every table should have a primary key - one or more columns whose values uniquely identify a row. Primary keys are what make relationships between tables possible: a orders table doesn't repeat all of a customer's details on every row; it stores a customer_id that references the primary key of a customers table. This reference is called a foreign key, and the database can be configured to enforce referential integrity, refusing to insert an order for a customer that doesn't exist, or refusing to delete a customer who still has orders (unless you explicitly cascade the deletion).

This decomposition into linked tables is what "relational" actually refers to - not the fact that tables relate to each other conceptually, but the mathematical notion of a relation (a set of tuples) from set theory. Codd built SQL's theoretical foundation on relational algebra, a small set of operations - selection, projection, union, join, and a few others - that can be composed to answer arbitrarily complex questions. When you write a SQL query with a JOIN and a WHERE clause, you are, whether you realize it or not, expressing a relational algebra expression, and the query planner translates it into a physical execution plan.

Normalization

Normalization is the discipline of structuring tables to minimize redundant data and avoid update anomalies. The most commonly cited levels are the first three normal forms. First normal form (1NF) requires that each column hold atomic values - no arrays or nested records crammed into a single cell. Second normal form (2NF) requires that non-key columns depend on the entire primary key, not just part of it, which mostly matters for composite keys. Third normal form (3NF) requires that non-key columns depend only on the primary key, not on other non-key columns - eliminating what's sometimes called a "transitive dependency."

In practice, most production schemas aim for 3NF and then deliberately denormalize specific tables where read performance matters more than storage efficiency or write-side consistency. A classic example is storing a total_amount on an orders row even though it could be derived by summing order_items. That's a normalization violation on paper, but it avoids an expensive aggregation on every read of an order list, at the cost of needing to keep the derived value in sync. Understanding normalization isn't about religiously chasing 3NF everywhere - it's about knowing exactly which redundancy you're introducing and why, so you can defend that decision to the next engineer who reads the schema.

ACID and Transactions

The other pillar of the relational model is the transaction: a group of operations that succeed or fail as a single unit. ACID - Atomicity, Consistency, Isolation, Durability - describes the guarantees a transactional database makes. Atomicity means a multi-statement transaction either commits fully or rolls back fully; there's no partial state where you debited one account but didn't credit the other. Consistency means the database moves from one valid state to another, respecting all constraints. Isolation governs what concurrent transactions can see of each other's uncommitted changes, and is usually the most misunderstood of the four - most databases default to a level weaker than full serializability (e.g., PostgreSQL's default is Read Committed, not Serializable) for performance reasons, which means classic anomalies like non-repeatable reads are possible unless you explicitly request a stricter isolation level. Durability means that once a transaction commits, it survives a crash, typically implemented via a write-ahead log that's flushed to disk before the commit is acknowledged.

Implementation: From Schema to Query

Understanding the theory only pays off when you can translate it into a working schema and query patterns your application actually uses. Below is a small but realistic e-commerce schema, followed by examples of interacting with it from both SQL directly and from application code in TypeScript and Python.

-- Schema: customers, orders, order_items
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_sku VARCHAR(64) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0)
);

-- Index to speed up the common lookup: "orders for a given customer"
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- A query joining across all three tables, aggregating totals per order
SELECT
    o.id AS order_id,
    c.email,
    o.status,
    SUM(oi.quantity * oi.unit_price_cents) AS total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'pending'
GROUP BY o.id, c.email, o.status
ORDER BY o.created_at DESC
LIMIT 50;

This schema demonstrates several things discussed above in a single artifact: primary keys (id columns), foreign keys enforcing referential integrity (customer_id, order_id), a CHECK constraint enforcing a business rule at the data layer (quantity must be positive), and an explicit index chosen because we know orders will frequently be filtered by customer_id. Note that ON DELETE CASCADE is a deliberate choice - it means deleting an order automatically deletes its line items, which is appropriate here because an order item has no meaning without its parent order.

Most application code doesn't hand-write SQL for every operation; it goes through a driver or an ORM (Object-Relational Mapper). Here's the same domain modeled with TypeScript using Prisma, a widely used ORM, followed by a Python example using SQLAlchemy's Core API for a case where you want more direct control over the generated SQL:

// TypeScript with Prisma
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function getPendingOrdersWithTotals(limit = 50) {
  // Prisma translates this into a JOIN + GROUP BY under the hood
  const orders = await prisma.order.findMany({
    where: { status: 'pending' },
    include: {
      customer: { select: { email: true } },
      items: true,
    },
    orderBy: { createdAt: 'desc' },
    take: limit,
  });

  return orders.map((order) => ({
    orderId: order.id,
    email: order.customer.email,
    totalCents: order.items.reduce(
      (sum, item) => sum + item.quantity * item.unitPriceCents,
      0
    ),
  }));
}

# Python with SQLAlchemy Core, for explicit control over the query shape
from sqlalchemy import create_engine, select, func
from sqlalchemy.orm import Session
from models import orders, customers, order_items  # Table objects defined elsewhere

engine = create_engine("postgresql+psycopg2://user:pass@localhost/shop")

def pending_orders_with_totals(limit: int = 50):
    stmt = (
        select(
            orders.c.id.label("order_id"),
            customers.c.email,
            func.sum(order_items.c.quantity * order_items.c.unit_price_cents).label("total_cents"),
        )
        .join(customers, customers.c.id == orders.c.customer_id)
        .join(order_items, order_items.c.order_id == orders.c.id)
        .where(orders.c.status == "pending")
        .group_by(orders.c.id, customers.c.email)
        .order_by(orders.c.created_at.desc())
        .limit(limit)
    )
    with Session(engine) as session:
        return session.execute(stmt).all()

The ORM version reads closer to the domain model and is easier to keep in sync as the schema evolves, but it hides the exact SQL being generated, which can matter when you're chasing down a slow query. The SQLAlchemy Core version is more verbose but leaves you in direct control of the join order and aggregation, which is valuable once you're past prototyping and into performance tuning. Neither approach is universally correct; the right choice depends on how much your team values query transparency versus development velocity.

Trade-offs and Common Pitfalls

Relational databases are not a default-correct choice for every workload, and understanding their limitations is as important as understanding their strengths. The most common failure mode is schema rigidity colliding with a fast-changing product. When every new feature requires a migration - adding a column, backfilling data, updating application code in lockstep - teams sometimes reach for a schemaless document store to avoid the friction. This is often a mistake in disguise: the flexibility just moves from the database layer to the application layer, where you now have to defensively check for missing or inconsistently shaped fields at read time. A well-designed relational schema with nullable columns and sensible defaults handles evolving requirements just fine in the vast majority of cases.

A second, more subtle pitfall is misunderstanding isolation levels and assuming "the database will handle it" for concurrency. A classic bug pattern is the read-then-write race condition: an application reads a row's current value, computes a new value in application code, and writes it back - for example, decrementing inventory. Under Read Committed isolation (Postgres and most databases' default), two concurrent transactions can both read the same starting inventory count and both proceed to write, resulting in overselling. The fix is either an atomic UPDATE inventory SET quantity = quantity - 1 WHERE id = ? AND quantity > 0 that lets the database do the read-and-write as one operation, or explicit row locking with SELECT ... FOR UPDATE, or using the Serializable isolation level and handling the resulting serialization failures with retries. Engineers who haven't internalized this distinction ship subtle correctness bugs that only appear under load.

The N+1 query problem is the third pitfall worth naming explicitly, because ORMs make it easy to write accidentally. If you fetch a list of orders and then, for each order, lazily fetch its customer in a loop, you've issued one query to get N orders and then N more queries to get each customer - N+1 total round trips instead of a single join. This is invisible in development with a handful of rows and devastating in production with thousands. Most ORMs, including Prisma's include and SQLAlchemy's joinedload, provide eager-loading mechanisms specifically to collapse this into one query, but you have to know to use them.

Finally, relational databases scale vertically more naturally than horizontally. A single Postgres or MySQL instance can be scaled a long way with better hardware, read replicas, and connection pooling (via tools like PgBouncer), but true horizontal write scaling requires sharding, which reintroduces much of the complexity that distributed NoSQL systems were built to handle natively - cross-shard joins and transactions become the application's problem again. Teams should be honest about whether they actually have the write volume that justifies this complexity before adopting it preemptively.

Best Practices for Working with Relational Databases

Index deliberately rather than reflexively. Every index speeds up reads on the columns it covers but slows down writes, since the database must update the index on every insert, update, and delete, and consumes additional storage. A good habit is to add indexes based on actual query patterns - columns that appear in WHERE, JOIN, and ORDER BY clauses for your slowest or most frequent queries - and to periodically check for unused indexes with tools like PostgreSQL's pg_stat_user_indexes, since an unused index is pure overhead. Composite indexes should generally be ordered with the most selective or most frequently filtered column first, since that ordering determines whether the index can be used for a given query at all.

Treat migrations as first-class, version-controlled code, not manual production changes. Tools like Prisma Migrate, Alembic (Python), Flyway, and Liquibase let you express schema changes as ordered, reversible scripts that run identically in every environment. This matters enormously for team collaboration: a schema change reviewed in a pull request, tested in CI against a throwaway database, and applied through a deployment pipeline is categorically safer than an engineer running ALTER TABLE by hand against production during an incident. Backward-compatible migration patterns - adding a nullable column before making it required, deploying application code that can handle both old and new schema shapes during a rollout - are essential once you have more than one service or more than one instance running your application code simultaneously.

Finally, be explicit about transaction boundaries in application code rather than letting an ORM's defaults decide for you. Wrap exactly the operations that need atomicity together - no more, no less. A transaction held open across a slow external API call (a payment gateway, an email send) will hold locks and connections longer than necessary, and under load this becomes a source of connection pool exhaustion and lock contention that's hard to diagnose after the fact. A useful mental model is to keep transactions as short-lived as possible and to do any slow, non-database work either before opening the transaction or after committing it.

Key Takeaways

Conclusion

Relational databases have remained the default choice for transactional, structured data for over fifty years not because of inertia, but because the core idea - separating logical data from physical storage, and enforcing correctness rules at the data layer - solves a problem that doesn't go away as systems get more complex. SQL's declarative nature means the same query can benefit from decades of optimizer improvements without a single line of application code changing, and constraints like foreign keys and checks catch entire categories of bugs before they ever reach a bug tracker.

None of this means relational databases are the right tool for every job; workloads with genuinely unstructured or highly variable data, or write volumes that outpace a single writer's capacity, are legitimate reasons to reach for something else. But that decision should be made with a clear understanding of what you're giving up - often, referential integrity and transactional guarantees that are easy to underestimate until you're debugging a production data inconsistency at 2 a.m. For the large majority of applications that need consistent, queryable, structured state, a well-designed relational schema, used with an understanding of normalization, indexing, and transaction isolation, remains one of the most reliable foundations you can build on.

References

Resources