Relational databases are still the default choice for a huge share of production systems, and not just because teams are used to them. They solve a practical set of hard problems: storing structured data, protecting important rules, grouping related changes into transactions, and answering flexible questions with SQL.

When you place an order, transfer money, reserve a seat, or update inventory, a relational database is often doing the careful work behind the scenes. These systems need more than "put this data somewhere." They need the data to stay correct.

Relational databases are strongest when data has a clear shape and clear relationships. Users have orders. Orders have payments. Products have inventory. They are also a good fit when the database must protect business rules, such as "an order must belong to a real user" or "an account balance cannot go below zero."

They are not ideal for every workload, but they are the baseline worth understanding first.

This chapter explains how relational databases model data, why they are so useful, and where they start to struggle.

The relational model was introduced by Edgar F. Codd in 1970. The idea was simple but powerful: represent data as tables, then let the database decide the best way to store and read that data.

Before relational databases, applications often had to follow low-level links between records or fit data into rigid tree-like structures. The relational model gave developers a cleaner view: think in tables, rows, columns, and relationships while the database handles the storage details.

Tables, Rows, and Columns

A relational database organizes data into tables. Each table usually represents one kind of thing: users, orders, products, payments, invoices, or shipments.

Each row represents one instance of that thing. Each column represents one attribute.

Users Table

Several details matter. Columns have names and types. A column might store integers, text, timestamps, booleans, decimals, or structured values such as JSON.

Rows do not have a guaranteed order. Unless a query uses ORDER BY, the database can return rows in any order. Columns are referenced by name, so you should not rely on the stored column order when reasoning about data.

Tables can also enforce rules. These rules are called constraints, and they reject invalid records before bad data becomes part of the system.

Primary Keys

A primary key uniquely identifies a row in a table. It cannot be NULL, and no two rows in the table can have the same primary key.

Primary key choice affects storage, indexes, debugging, APIs, and how data is copied between systems. There is no single best key type.

Many applications use generated IDs such as integers, UUIDs, or time-ordered IDs. Natural keys are useful when the real-world value is truly stable, but stable natural keys are rarer than they first appear. Emails change. Product codes change. Business rules change.

Foreign Keys and Relationships

A foreign key is a column, or set of columns, that points to the primary key of another table. It is how a relational database protects relationships between records.

Here, one user can have many orders. The database can reject an order whose user_id does not exist. In plain English: an order cannot point to a user that is not there. The formal term for this is referential integrity.

Foreign keys also let you define what should happen when a referenced row changes:

These choices are business rules, not just database settings. For example, deleting a user should usually not delete that user's completed financial records.

Relationship Types

Relational modeling commonly uses three relationship shapes.

One-to-One: Each row in one table maps to at most one row in another table. This is useful when optional or sensitive fields should be separated.

One-to-Many: One row maps to many rows in another table. A user has many orders. A customer has many invoices.

Many-to-Many: Rows on both sides can relate to many rows on the other side. This is modeled with a join table, often called a junction table.

The enrollments table represents the relationship itself. Join tables often carry their own fields, such as enrolled_at, status, or grade.

A relational schema defines the structure and rules of the data: tables, columns, data types, indexes, primary keys, foreign keys, uniqueness rules, defaults, and checks.

This is one of the major strengths of relational databases. The database can reject bad writes even when application code has a bug.

Schema Definition

Schemas are defined with SQL statements such as CREATE TABLE. This part of SQL is often called Data Definition Language, or DDL. The example below uses PostgreSQL syntax. Other databases spell auto-incrementing keys differently: MySQL uses BIGINT AUTO_INCREMENT, SQL Server uses BIGINT IDENTITY, and Oracle traditionally uses sequences or GENERATED AS IDENTITY.

This schema protects several rules:

Schema-on-Write

Relational databases use schema-on-write, which means data is checked before it is stored. If a write breaks the schema, the database rejects it.

This creates some work when the schema changes, but it prevents silent data drift. Without it, different versions of the application can write different shapes of data, and the database can slowly fill with inconsistent records.

Schema changes are normal in production systems. A common pattern is to make changes in small safe steps: add nullable columns first, deploy code that writes both old and new fields if needed, backfill data safely, then remove old structures later.

Normalization

Normalization means organizing data so each important fact lives in one main place. The goal is to reduce duplication and avoid awkward bugs when data changes. It is not about chasing academic purity.

Consider this denormalized order table:

This design creates several problems. If Alice changes her email, several rows must be updated. If one row is missed, the database now disagrees with itself.

If Bob's only order is deleted, the only record of Bob disappears with it. If a new customer has not placed an order yet, there is nowhere clean to store that customer. These problems are often called update, delete, and insert anomalies.

A normalized design separates customers, products, and orders:

Normalization is the usual starting point for systems that handle important business changes. Denormalization, which means deliberately duplicating data, is still useful. Caches, search indexes, reporting tables, precomputed views, and read models often duplicate data to make reads faster. The key word is deliberately.

Transactions let a database apply multiple changes as one unit. ACID is the usual shorthand for the guarantees people expect from a transaction.

Atomicity

Atomicity means all-or-nothing. Either every operation in the transaction succeeds, or the database acts as if none of them happened.

If the second update fails, the first update is rolled back. The system does not leave money halfway between accounts.

Consistency

Consistency means a transaction should leave the database in a valid state. The database enforces the rules it knows about, such as foreign keys, unique constraints, checks, and data types.

This does not mean the database understands every business rule. If your application allows a nonsensical update that still passes the database rules, the database may accept it. Good schema design and application logic work together.

Isolation

Isolation controls what transactions can see when several transactions run at the same time. Stronger isolation prevents more weird edge cases, but it can also make transactions wait or retry more often.

Common edge cases include:

SQL defines four standard isolation levels. Each level gives stronger protection than the one before it:

Do not assume every database implements these names exactly the same way. PostgreSQL, MySQL/InnoDB, SQL Server, and Oracle differ in important details.

Durability

Durability means that once a transaction commits, the database can recover it after a crash.

Databases usually achieve this with a write-ahead log, or WAL. The database records the change in a crash-safe log before treating the transaction as committed. If the server crashes before changed pages are fully written to data files, recovery replays the log.

Durability also depends on configuration and storage behavior. Settings such as commit mode, replication mode, and disk flush guarantees affect how much data could be lost in a bad failure.

SQL is declarative. That means you describe the result you want, and the database decides how to compute it.

Declarative Queries

Consider a query that returns all orders over $100 placed by users in California.

The query does not say whether to scan users first, scan orders first, use an index, or choose a specific join method. The database chooses a plan based on indexes, row-count estimates, and its estimate of the cheapest way to answer the query.

Common Query Patterns

Aggregations compute values across groups:

Joins combine related rows:

Window functions compute values across related rows without collapsing the result set into one row per group:

Query Optimizer

The query optimizer is the part of the database that turns SQL into an execution plan. You can inspect the plan with EXPLAIN. In many databases, EXPLAIN ANALYZE runs the query and shows real timing.

The database moves the query through several stages before returning results:

The optimizer's choices depend heavily on available indexes. Indexes make many queries fast, but every index adds write cost and storage cost. A well-designed relational schema has indexes for important query patterns, not indexes on every column.

Relational databases can scale a long way, especially with careful schema design, indexing, batching, connection pooling, and query tuning. But scaling writes across many machines is harder than scaling reads.

Vertical Scaling

The simplest scaling path is a larger machine: more CPU, more memory, faster disks, and better network capacity.

Vertical scaling is attractive because the application does not need to know that data is spread across machines. Transactions, joins, indexes, and constraints keep working in the familiar way.

The limits are practical. Hardware eventually hits a ceiling, large machines become expensive, and maintenance or failover becomes more serious. One primary database can also become a write bottleneck.

For many systems, vertical scaling plus good indexing is enough for a long time.

Read Replicas

Read replicas copy data from the primary database. Writes go to the primary. Read-only queries can go to replicas.

Read replicas help when the workload is read-heavy. They do not remove the write bottleneck.

Replicas come with trade-offs. Replica lag means a replica may not have the latest committed write yet. After a user changes data, the next read may need to go to the primary so the user sees their own change.

Failover requires coordination and can still lose recent writes depending on how replicas copy data. Operational work also grows, since backups, monitoring, schema changes, and connection management now involve more nodes.

Sharding

Sharding splits rows across multiple database servers. Each shard owns part of the data.

Sharding can scale reads and writes, but it changes the system. From the application's point of view, the database is no longer one simple place.

Sharding introduces several challenges. The shard key matters because a poor key can overload one shard or force many queries to touch several shards. Joins across shards require fetching data from multiple places and combining it elsewhere.

Transactions across shards become distributed transactions, which are slower and harder to operate. Rebalancing, which means moving data between shards, is risky under live traffic. Global rules such as unique constraints and foreign keys are harder to enforce across shard boundaries.

A good shard key spreads load evenly, keeps related data together, and matches common queries. Sharding is a tool for real scale pressure, not a default starting point.

Several relational databases are widely used in production. Their differences matter, but the best choice usually depends more on team experience, ecosystem, hosting model, and workload than on a feature checklist.

PostgreSQL

PostgreSQL is a strong general-purpose choice. It has excellent SQL support, useful extensions, JSONB, many indexing options, full-text search, and a rich ecosystem with tools such as PostGIS and pgvector.

It is a good fit when you want a reliable default, complex queries, strong data modeling, and room to add specialized features without leaving the relational world.

MySQL

MySQL is widely used for web applications and managed cloud databases. InnoDB, its common storage engine, provides transactions, row-level locking, indexes, and replica support.

It is a good fit when your team already knows it, your workload is web-oriented, and you value its operational familiarity and hosting ecosystem.

SQL Server

SQL Server is common in Microsoft-centered environments. It provides strong enterprise features, mature tooling, high availability options, security features, and deep integration with .NET and Microsoft analytics tools.

It is a good fit for enterprise systems where the organization already runs Microsoft infrastructure and values integrated tooling.

Oracle Database

Oracle Database remains important in large enterprises with demanding transaction workloads, long-running legacy systems, and strict operational requirements.

It is a good fit when an organization already has Oracle expertise, licensing, tooling, and applications built around it.

Choose a relational database when data has relationships. Users, orders, payments, invoices, permissions, and inventory all connect naturally through tables.

Choose it when correctness matters. Transactions and constraints help protect against double-spending, overselling, invalid references, and half-finished updates.

Choose it when queries are varied. SQL handles joins, reporting, filtering, sorting, and aggregation without forcing you to build a separate access path for every question.

Choose it when the database holds the official copy of the data and you want durable storage with backups, migrations, replication, and mature recovery tooling.

And choose it when the expected scale is within normal operational bounds. A well-indexed relational database can handle a lot of traffic before it stops being enough.

When to Consider Alternatives

Consider another database type, or an additional specialized store, when:

The usual architecture is not "relational or specialized." It is often a relational database as the official data store, with caches, search indexes, analytics stores, or vector indexes built from it.

Relational databases remain central because they combine several strengths. Tables and relationships model business data clearly, schema rules keep invalid data out, and transactions keep multi-step changes correct during failures and concurrent requests.

SQL expresses complex queries without making the application scan and combine records by hand. Indexes and the query optimizer help the database choose efficient ways to run those queries.

Their operational maturity is just as important: backups, replicas, recovery, tooling, and expertise are widely available. The practical lesson is to start with relational unless the requirements clearly point elsewhere. Once you understand what relational databases do well, it becomes much easier to explain why another database is needed.