Relational Databases Explained: Fundamentals Every Engineer Should MasterA practical, engineering-first tour of the relational model, ACID guarantees, normalization, indexing, and the trade-offs that shape real-world systems

A practical, engineering-first tour of the relational model, ACID guarantees, normalization, indexing, and the trade-offs that shape real-world systems

Introduction

Relational databases have been the default choice for storing structured data for over five decades, and despite waves of NoSQL enthusiasm, distributed ledgers, and vector stores, they remain the backbone of most production systems. PostgreSQL, MySQL, SQL Server, and Oracle Database still power the majority of transactional workloads at companies of every size, from three-person startups to global banks. Understanding why this technology has persisted - and how to use it well - is not a nostalgic exercise. It is a core competency for any engineer who touches data.

This article walks through the relational model from first principles: what makes it "relational," how the theory maps onto real schema design decisions, why ACID transactions matter operationally rather than just academically, and where the common failure modes lie. The goal is not to reproduce a textbook, but to connect the underlying theory to the decisions you actually make when you design a schema, write a query, or debug a slow endpoint at 2 a.m.

Context: Why the Relational Model Exists

Before relational databases, most data was stored in hierarchical or network models, where relationships between records were encoded as physical pointers or nested structures. IBM's IMS, introduced in the 1960s, is a well-known example. These systems were fast for the access patterns they were designed for, but they were brittle: if your query pattern changed, you often had to redesign the physical data structure, because the logical structure and the physical storage were tightly coupled.

In 1970, Edgar F. Codd, a researcher at IBM, published "A Relational Model of Data for Large Shared Data Banks" in Communications of the ACM. Codd's insight was to separate the logical representation of data (tables of tuples, described using set theory and predicate logic) from the physical storage layer. Applications would interact with data through a declarative language, and the database engine would be responsible for figuring out the most efficient way to retrieve it. This separation is the reason SQL queries can be optimized automatically and why schemas can evolve without a full rewrite of every application that touches them.

The practical consequence of Codd's model is that a "relation" is simply a table: an unordered set of rows (tuples), each with a fixed set of typed columns (attributes). There is no concept of one row "pointing to" another at the physical level. Instead, relationships are expressed logically through shared column values - most commonly via foreign keys - and the database enforces consistency between them. This is a subtle but important distinction from document or graph databases, where relationships are frequently embedded directly into the data structure itself, trading flexibility for a different set of constraints.

The Relational Model in Depth

Tables, Keys, and Integrity Constraints

At its core, a relational database organizes data into tables, where each table represents an entity type (customers, orders, products) and each row represents a specific instance of that entity. Every table should have a primary key: one or more columns whose values uniquely identify each row. Primary keys are what allow other tables to reference a specific row through a foreign key, which is a column (or set of columns) in one table that must match a primary key value in another table. This is the mechanism of referential integrity - the guarantee that an order can never reference a customer that doesn't exist.

Constraints extend beyond keys. NOT NULL constraints prevent missing required data. UNIQUE constraints prevent duplicate values in columns that should be distinct, like email addresses. CHECK constraints enforce domain-specific rules, such as ensuring a price column is never negative. These constraints matter because they push data validation down into the storage layer itself, where it cannot be bypassed by a buggy application, a forgotten code path, or a direct database migration script. Relying solely on application-level validation is a common mistake - application code changes far more often than database constraints, and every new code path is a new opportunity to insert invalid data.

ACID: The Transactional Contract

ACID - Atomicity, Consistency, Isolation, Durability - describes the guarantees a relational database makes about transactions, and it is worth understanding each property individually because they solve distinct problems. Atomicity means a transaction either fully commits or fully rolls back; there is no partial state where half of a multi-step operation succeeded. Consistency means the database moves from one valid state to another, respecting all constraints, triggers, and cascading rules. Isolation governs how concurrent transactions interact with each other, and it is the property most engineers underestimate because it has configurable levels with real trade-offs. Durability guarantees that once a transaction commits, the result survives a crash, typically enforced through write-ahead logging.

Isolation deserves particular attention because the SQL standard defines four isolation levels - Read Uncommitted, Read Committed, Repeatable Read, and Serializable - and each database implements them slightly differently. PostgreSQL, for instance, uses Multi-Version Concurrency Control (MVCC) to implement Read Committed as its default, which means readers never block writers and writers never block readers, but it also means two reads within the same transaction can see different data if another transaction commits in between. This is precisely the kind of detail that turns into a subtle production bug: a report-generation job that reads the same table twice within a transaction and gets inconsistent totals because it was using Read Committed instead of Repeatable Read.

Normalization and Its Purpose

Normalization is the process of structuring tables to reduce data redundancy and prevent update anomalies. The most commonly cited forms are First Normal Form (1NF, eliminating repeating groups and ensuring atomic column values), Second Normal Form (2NF, eliminating partial dependencies on a composite key), and Third Normal Form (3NF, eliminating transitive dependencies where a non-key column depends on another non-key column rather than the primary key). In practice, most production schemas target 3NF as a reasonable default, deviating deliberately - and documenting why - when denormalization is needed for read performance.

The purpose of normalization is not academic purity; it is preventing a specific class of bug. If a customer's address is duplicated across every order row, updating that address requires updating every order, and missing even one row creates an inconsistency that is very difficult to detect later. Normalization pushes that data to a single source of truth (the customers table) and lets orders reference it by foreign key. The cost is that reading a full order with customer details now requires a join, which is where the tension between normalization and performance becomes concrete, and where many teams later choose to selectively denormalize specific fields for read-heavy paths.

Implementation: Schema Design and Query Patterns in Practice

Theory only becomes useful once it shapes actual schema and query decisions. Consider a simplified e-commerce schema with customers, orders, and order line items. A normalized design in PostgreSQL might look like this:

CREATE TABLE customers (
    customer_id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    full_name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    order_id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    placed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_item_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_sku TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0)
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Notice the ON DELETE CASCADE clause and the indexes on foreign key columns - a detail that is easy to overlook. Most databases do not automatically index foreign key columns, which means a join or a cascading delete on an unindexed foreign key can trigger a full table scan on a large table. This is one of the most common performance issues found in schema reviews.

Application code interacting with this schema should wrap multi-step writes in explicit transactions rather than relying on auto-commit for each statement. Here is a realistic example in TypeScript using the pg driver, showing how a transaction protects an operation that must either fully succeed or fully fail - deducting inventory and creating an order together:

import { Pool, PoolClient } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

interface OrderItem {
  productSku: string;
  quantity: number;
  unitPriceCents: number;
}

async function placeOrder(
  customerId: number,
  items: OrderItem[]
): Promise<number> {
  const client: PoolClient = await pool.connect();
  try {
    await client.query("BEGIN");

    const orderResult = await client.query<{ order_id: number }>(
      `INSERT INTO orders (customer_id, status) VALUES ($1, 'pending') RETURNING order_id`,
      [customerId]
    );
    const orderId = orderResult.rows[0].order_id;

    for (const item of items) {
      // SELECT ... FOR UPDATE locks the row to prevent a race condition
      // where two concurrent orders both read the same available stock.
      const stock = await client.query(
        `SELECT quantity_available FROM inventory
         WHERE product_sku = $1 FOR UPDATE`,
        [item.productSku]
      );

      if (stock.rows[0].quantity_available < item.quantity) {
        throw new Error(`Insufficient stock for ${item.productSku}`);
      }

      await client.query(
        `UPDATE inventory SET quantity_available = quantity_available - $1
         WHERE product_sku = $2`,
        [item.quantity, item.productSku]
      );

      await client.query(
        `INSERT INTO order_items (order_id, product_sku, quantity, unit_price_cents)
         VALUES ($1, $2, $3, $4)`,
        [orderId, item.productSku, item.quantity, item.unitPriceCents]
      );
    }

    await client.query("COMMIT");
    return orderId;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

The FOR UPDATE row lock here is doing real work: without it, two concurrent requests could both read the same "available" stock count, both pass the check, and both proceed to oversell the product. This is a textbook example of why understanding isolation and locking is not optional trivia - it directly determines whether your checkout flow is correct under concurrent load. Wrapping the whole sequence in a transaction also means that if the insert into order_items fails partway through, the earlier inventory deduction is rolled back automatically rather than left in an inconsistent state.

Trade-offs and Common Pitfalls

Relational databases are not free of trade-offs, and pretending otherwise leads to painful surprises in production. The most significant tension is between normalization and read performance. A fully normalized schema minimizes redundancy and update anomalies, but it also means that common read paths require multiple joins across several tables. For high-traffic read endpoints, this join cost can become the dominant factor in latency, which is why many teams introduce selective denormalization, materialized views, or read replicas rather than normalizing everything to the letter of the theory. The decision to denormalize should always be deliberate and documented, because it reintroduces the exact redundancy risks that normalization was designed to prevent.

Scaling relational databases horizontally is also fundamentally harder than scaling stateless application servers. Vertical scaling (bigger hardware) has limits, and sharding a relational database - splitting rows across multiple physical databases by some partition key - breaks the convenience of cross-shard joins and transactions, forcing application-level workarounds. This is why many teams reach for read replicas and connection pooling before considering sharding, and why some workloads genuinely are better served by purpose-built systems: time-series data, graph traversal queries, or massive analytical scans over petabytes may be a poor fit for a general-purpose relational engine, even a well-tuned one. Recognizing when the relational model is the wrong tool is as important as knowing how to use it well.

Best Practices for Working with Relational Databases

Index deliberately rather than reflexively. Every index speeds up specific read patterns but slows down every write to that table and consumes additional storage, so indexes should be added in response to observed query patterns (via EXPLAIN ANALYZE in PostgreSQL or equivalent tools) rather than added preemptively on every column that might someday be queried. A table with a dozen unused indexes is a common and avoidable source of write latency.

Keep transactions short and predictable. Long-running transactions hold locks and, in MVCC-based databases like PostgreSQL, prevent old row versions from being cleaned up (vacuumed), which can bloat tables over time. A transaction that calls out to an external API or waits on user input in the middle of its lifecycle is a common anti-pattern; external calls should generally happen before or after the transactional database work, not inside it.

Use migrations as code, and make them reversible where possible. Tools like Flyway, Alembic (for Python), and Prisma Migrate treat schema changes as versioned, auditable artifacts rather than manual ALTER statements run by hand against production. This matters enormously at team scale, where multiple engineers are modifying the same schema, and where reproducing an exact schema state in a new environment needs to be deterministic. Below is a small example of an idiomatic Python data-access pattern using SQLAlchemy's Core (not the ORM layer) for a case where explicit SQL control matters, such as a bulk reporting query:

from sqlalchemy import create_engine, text
from sqlalchemy.engine import Engine

def get_monthly_revenue(engine: Engine, year: int, month: int) -> list[dict]:
    query = text("""
        SELECT
            c.customer_id,
            c.full_name,
            SUM(oi.quantity * oi.unit_price_cents) AS total_cents
        FROM orders o
        JOIN customers c ON c.customer_id = o.customer_id
        JOIN order_items oi ON oi.order_id = o.order_id
        WHERE o.status = 'paid'
          AND EXTRACT(YEAR FROM o.placed_at) = :year
          AND EXTRACT(MONTH FROM o.placed_at) = :month
        GROUP BY c.customer_id, c.full_name
        ORDER BY total_cents DESC
    """)

    with engine.connect() as conn:
        result = conn.execute(query, {"year": year, "month": month})
        return [dict(row._mapping) for row in result]

This pattern - explicit SQL with parameterized queries, rather than string concatenation - also closes off SQL injection as an attack vector, which remains one of the most common and most preventable vulnerabilities in database-backed applications.

Key Takeaways

• Design your schema around normalized entities first, and denormalize deliberately and only where a measured read-performance problem justifies it.
• Always index foreign key columns; most databases do not do this automatically, and unindexed joins are a frequent source of slow queries.
• Understand your database's default isolation level and change it explicitly when your logic depends on stronger consistency guarantees.
• Wrap multi-step writes in transactions, and keep those transactions short - no external network calls or user-facing waits inside them.
• Manage schema changes through versioned migration tools rather than ad hoc scripts, so every environment's schema state is reproducible.

Analogies and Mental Models

A useful mental model for the relational model is a well-run reference library. Each book (row) lives on a shelf (table) organized by a specific classification scheme, and rather than photocopying the author's biography into the back of every one of their books, the library keeps a single authors catalog and has each book reference the author by an ID card number. If the author's biography needs correcting, it's corrected once, in one place, and every book automatically reflects the update. This is normalization: a single source of truth referenced by key, rather than the same fact copied everywhere it's needed.

Transactions, meanwhile, are best understood through the analogy of a bank teller processing a transfer between two accounts. The teller does not debit one account, walk away, and come back later to credit the other; the entire operation happens as one indivisible unit, and if anything interrupts it midway, the teller reverses whatever partial work was done. That indivisibility is atomicity, and it is precisely why financial systems were among the earliest and most demanding adopters of transactional relational databases - a partially applied transfer is not a minor bug, it is a direct loss of money.

The 80/20 Insight

If you strip away the entire ecosystem of the relational model - vendor-specific SQL dialects, replication topologies, exotic index types - down to the small set of ideas that account for most real-world outcomes, three things dominate: model your data with correctly normalized tables and foreign keys before optimizing anything else; understand and deliberately choose your isolation level rather than accepting the default by accident; and index based on actual query patterns using EXPLAIN, not intuition. Engineers who internalize just these three habits avoid the overwhelming majority of relational database bugs and performance incidents seen in production systems. Everything else - replication strategies, sharding, exotic index types like GiST or BRIN - matters, but it matters far less often, and it matters far less if the fundamentals are already solid.

Conclusion

Relational databases endure not because of inertia, but because the relational model solves a genuinely hard problem well: representing structured, interrelated data with strong consistency guarantees, in a way that lets the underlying engine handle optimization while the application layer stays declarative. Codd's core insight - separating logical structure from physical storage - is still the reason a schema can evolve, a query planner can improve, and an index can be added, all without rewriting the application that depends on the data.

None of this is a reason to treat the relational model as a universal hammer. Understanding its trade-offs - the tension between normalization and read performance, the operational cost of long transactions, the real difficulty of horizontal scaling - is what separates an engineer who can use SQL from an engineer who can design a data layer that holds up under real production load. The fundamentals covered here - keys, constraints, ACID, normalization, and deliberate indexing - are not a beginner's checklist to move past quickly. They are the foundation that every advanced technique, from read replicas to distributed SQL, is built on top of.

References

• Codd, E. F. (1970). "A Relational Model of Data for Large Shared Data Banks." Communications of the ACM, 13(6), 377-387.
• PostgreSQL Global Development Group. PostgreSQL Documentation - Concurrency Control (MVCC), Transactions, and Indexes. https://www.postgresql.org/docs/current/
• ISO/IEC 9075 (SQL Standard), maintained jointly by ISO and IEC.
• Date, C. J. An Introduction to Database Systems (8th Edition). Addison-Wesley.
• Kleppmann, M. (2017). Designing Data-Intensive Applications. O'Reilly Media.
• SQLAlchemy Documentation - Core Expression Language. https://docs.sqlalchemy.org/
• node-postgres (pg) Documentation. https://node-postgres.com/
• Flyway Documentation. https://documentation.red-gate.com/fd