Premium Video

This video is available to premium subscribers only

Unlock Full Access

In this chapter, we will learn what a relational database is, how it organizes data into tables, how keys, relationships, and normalization work, how SQL queries are executed, and how relational databases scale.

Suppose we run an online store and keep all of our orders in one big spreadsheet. Each row has the order id, the product, the price, the customer's name, their email, and their shipping address.

Problems appear quickly:

In 1970, Edgar Codd, a researcher at IBM, proposed a better way to organize data. He called it the relational model.

Before it, applications often had to follow low-level links between records or fit data into rigid tree-like structures. The relational model let developers think in tables, rows, columns, and relationships while the database handled the storage details.

It became the foundation for almost every major database of the following decades, and it is still the most widely used approach today.

A relational database stores data in tables. In the formal model, a table is called a relation, which is where the name comes from.

Each table represents one kind of thing. For our store, we might have a customers table, a products table, and an orders table.

Each column in a table has a name and a data type. For example, the customers table might have an id that is an integer, a name that is text, and a created_at that is a timestamp.

Each row represents one specific item, such as one customer.

Customers Table

The structure of the tables, including their columns and types, is called the schema. The database enforces this schema on every write. If a column is defined as an integer, the database will not let you store the word "hello" in it.

This is called schema-on-write: data is checked before it is stored. It creates some work when the schema changes, but it prevents different versions of the application from slowly filling the database with inconsistent records.

One more detail: rows in a table do not have a guaranteed order. Unless a query uses ORDER BY, the database can return rows in any order.

But once data is split across tables, we need a way to connect them.

Primary Keys

Every table needs a way to identify each row uniquely. That is the job of the primary key.

In the customers table, the primary key might be a customer id. No two customers can have the same id, and the id can never be empty (NULL).

Some tables use a natural key, like an email address or a country code. But many use a surrogate key, which is an id generated by the database with no business meaning. Surrogate keys are popular because they never need to change, even if the customer changes their email.

Surrogate keys come in a few common forms:

Foreign Keys

Now let's connect the tables. The orders table has a column called customer_id. This column stores the primary key of the customer who placed the order. This is called a foreign key.

The database can enforce that every customer_id in the orders table points to a customer that actually exists. If we try to create an order for customer 900, and that customer does not exist, the database rejects it.

This is called referential integrity. It prevents orphaned data, like orders that belong to nobody.

Foreign keys also let you define what should happen when a referenced row is deleted or changed:

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

Using keys, we can model three kinds of relationships.

One-to-many. One customer can place many orders, but each order belongs to a single customer. We model this by putting customer_id as a foreign key in the orders table.

One-to-one. For example, each user might have one profile with extra settings. We can model this with a foreign key that is also unique, so each user can appear only once.

Many-to-many. An order can contain many products, and a product can appear in many orders. We cannot model this with a single foreign key.

Instead, we create a separate table, often called a junction table. Here, it might be called order_items, and each row has an order_id, a product_id, and the quantity. So one order with three products becomes three rows in this table.

Junction tables often carry their own fields about the relationship itself, like quantity here, or unit_price to record the price at the time of purchase.

With just these three patterns, we can model most real-world data.

Now let's return to the spreadsheet problems. The process of organizing tables to remove this kind of duplication is called normalization.

The main idea is that each fact should be stored in only one place. A customer's address belongs in the customers table, once. Orders refer to the customer by id instead of copying their details.

This removes three kinds of problems, often called anomalies:

Normalization is described through a series of rules called normal forms:

In practice, most application schemas aim for about third normal form.

Relational databases are queried using SQL, or Structured Query Language.

SQL is declarative. This means you describe what data you want, not how to find it.

For example, this query asks for each customer's name and total order amount from last month, by joining the customers and orders tables, grouping by customer, and sorting by total spending:

Notice that the query does not say which index to use, which table to read first, or how to group the rows. The database figures that out.

This is a major strength of relational databases, because the same data can answer questions nobody planned for when the schema was designed. Remember the spreadsheet: this is the question that needed custom code before.

SQL also supports more advanced patterns. Window functions, for example, compute values across related rows without collapsing them into one row per group:

A join combines rows from related tables when reading.

Inner Join

The most common type is the inner join. It returns only rows that have a match in both tables.

If we join customers with orders, we get one row for each order, along with the customer who placed it. Customers with no orders do not appear.

Left Join

A left join keeps every row from the first table, even when there is no match.

If we left join customers with orders, customers with no orders still appear, with empty (NULL) values for the order columns. This is useful for questions like "which customers have never placed an order":

Using the customers and orders tables from earlier, plus Carol, who has never ordered:

Joins are what make normalization practical. We store each fact once, and bring related facts back together at query time.

But joins also have a cost. Joining several large tables can be expensive if the database cannot use indexes to find matching rows quickly.

So what happens inside the database when we send a SQL query?

Most databases let you see the chosen plan with a command called EXPLAIN. In many databases, EXPLAIN ANALYZE also runs the query and shows real timings.

When a query is slow, EXPLAIN is usually the first place to look. It shows whether the database is using an index, scanning a whole table, or joining in an unexpected order.

Without an index, finding rows means scanning the entire table. On a table with millions of rows, that is slow.

An index is a separate data structure that keeps the values of one or more columns sorted, along with pointers to the matching rows. Most relational databases use B-Trees for this.

With an index on customer email, finding one customer takes a few page reads instead of a full scan.

The primary key is indexed automatically, and indexes on foreign keys and filter columns are also common.

But indexes are not free:

So the goal is to index the columns your queries actually use, not every column.

Relational databases are also known for protecting data correctness.

Transactions

The first way is through transactions. A transaction groups several changes so they succeed or fail together.

When a customer places an order, we might insert the order, insert the order items, and reduce product stock. With a transaction, either all of those changes are saved or none of them are.

If any statement fails, the whole transaction is rolled back. The store never ends up with an order whose stock was not reduced.

Relational databases provide ACID guarantees:

Isolation comes in levels. Stronger levels prevent more edge cases, but can make transactions wait or retry more often:

Durability is usually achieved with a write-ahead log (WAL). The database records each change in a crash-safe log before treating the transaction as committed. If the server crashes before the data files are updated, recovery replays the log.

Constraints

The second way is through constraints:

Here is how those rules look in a schema (PostgreSQL syntax):

Note the CHECK (stock >= 0) on products. If the transaction above tried to sell an item that is out of stock, this constraint would fail and the whole order would roll back.

These rules live inside the database. So even if a bug in the application tries to write bad data, the database rejects it.

Normalization reduces duplication, but it also means more joins. For read-heavy systems, those joins can become expensive.

Suppose a product listing page needs product details, the average rating, and the number of reviews. Calculating the average rating across millions of reviews on every page load would be slow.

So we might store the average rating and review count directly in the products table, and update them whenever a new review is added.

This is called denormalization. We deliberately store some data more than once to make reads faster.

The trade-off is that writes become more complex. The application, or the database through triggers, must keep the copies in sync.

A common approach is to start normalized and denormalize only the specific paths that are proven to be slow.

A traditional relational database runs on a single primary server that handles all writes. As traffic grows, there are several ways to scale it.

1. Vertical Scaling

Move to a bigger machine with more CPU, memory, and faster storage.

This is simple, and it often works for longer than teams expect. Transactions, joins, indexes, and constraints keep working exactly as before. The limits are a hardware ceiling, rising cost, and the fact that one primary is still a single write bottleneck.

2. Read Replicas

The primary copies its changes to one or more replicas, which handle read queries. This works well when reads far outnumber writes.

Replicas do not remove the write bottleneck. And because copying takes time, a replica may briefly lag behind the primary. After a user changes data, their next read may need to go to the primary so they see their own change.

3. Caching

Frequently read data can be stored in a cache like Redis, so fewer queries reach the database. The cost is keeping the cache fresh when the underlying data changes.

4. Partitioning

Partitioning splits one large table into smaller pieces, often by date, on the same server. For example, an orders table might have one partition per month. Queries for last month only touch that partition, and old partitions can be dropped or archived cheaply.

5. Sharding

Sharding spreads data across multiple independent database servers. Each shard owns part of the data.

Sharding increases write capacity, but it makes joins and transactions across shards much harder. Unique constraints and foreign keys are also hard to enforce across shard boundaries. A good shard key spreads load evenly and keeps data that is queried together on the same shard.

To address this, distributed SQL databases like Google Spanner, CockroachDB, and YugabyteDB provide a relational model and SQL while spreading data across many machines.

A relational database is a strong choice when:

It is less suited to a few workloads:

But for most applications, a relational database is a sensible default starting point. Often the architecture is not "relational or specialized" but both: a relational database holds the official copy of the data, and caches or search indexes are built from it.

Cloud providers also offer managed versions, such as Amazon RDS and Aurora, Google Cloud SQL, and Azure SQL Database. They handle backups, patching, replicas, and failover for you.