Relational databases

Tables, rows, and columns

A table has named columns and a collection of rows. Each column describes a field, such as a product’s name or price. Each row is one record with values for those fields. Adding another product adds a row; adding a weight column changes the table’s definition.

The table definitions belong to the schema. This describes the columns, their data types, and rules about the values allowed. A price column can use a numeric type that supports adding prices and comparing amounts; a product name needs a text type. Choosing those types gives the database information about what the values mean and what operations make sense for them.

We usually give each row an identifier and declare it as the table’s primary key. Its job is to give every row a unique, non-missing identity. A product ID identifies a product even if its name changes; it is not a row number or a position on disk. A key can also use a combination of columns when that is what identifies a record.

These tables describe the organisation we work with. The database engine—the software that stores and retrieves the records—chooses their physical arrangement in memory and on disk. Different engines can present the same kind of tables while storing them differently.

Further reading: Designing a relational schema (draft) · How records are stored and retrieved (planned)

Relationships between tables

A stable product ID becomes useful beyond the products table. An order needs to record which products were bought, but copying every product’s name and description into every order would give us many copies to keep up to date. Customers can also place many orders; copying their contact details would create the same problem.

Instead, an order can store the ID of its customer. An order line records a product and quantity within that order, referring to both the order and product by their IDs. Those references let us keep a customer record in one place and still find the orders that belong to it.

Illustration

Separate records, connected by IDs

Customers tableOrders tableAdaC4O12 · C4C4O12P7Products tableOrder linesP7Blue mugO12 · P7Quantity: 2

The benefit becomes clearer when information changes. Correcting a customer’s contact details needn’t mean searching through every order to update a copied customer record. We can also ask new questions across the same facts: what a customer bought, which orders contain a product, or how many units sold last month.

Some facts should be preserved separately. The price paid for a product belongs to the purchase, even if the current catalogue price later changes. Good table design distinguishes a shared fact we want to keep current from a historical fact we need to remember. An order line might therefore store €18 as the price paid for a mug whose catalogue price is now €20. Looking up the product tells us what it is; the order line tells us what that purchase cost.

Further reading: Designing a relational schema (draft) · Tables and JSON (draft)

Queries and joins

Keeping these facts in separate tables leaves us with a question: how do we put them together when someone wants to see an order? We send the database a query, a request for information. Most relational databases use a language called SQL to express it.

For order O12, the order line contains product ID P7 and a quantity of two. The product record with ID P7 supplies its current name, “Blue mug.” A query that matches those IDs can return an answer containing the product name and quantity together: Blue mug, 2. Combining records by matching values this way is a join. We haven’t had to store another copy of the product name on the order line to produce that answer.

SELECT products.name, order_lines.quantity
FROM order_lines
JOIN products
  ON products.product_id = order_lines.product_id
WHERE order_lines.order_id = 'O12';

We could ask a different question of the same records, such as which orders contain P7. SQL lets us describe the matches and fields we need without writing a search procedure for each question. The engine’s query planner chooses an execution plan: the operations it will use to produce the answer. As the tables grow, how it finds the matching rows becomes important.

Further reading: Queries and joins (draft) · From SQL to an execution plan (planned) · How joins work (planned)

Indexes

Suppose we want all the orders for customer C4. The engine could read the orders table and check the customer ID on every row. That gives the right answer, but most of the work may be spent examining other customers’ orders.

An index on the orders table’s customer ID provides another route. A common kind of index keeps the customer IDs in sorted order, with a way to locate the corresponding order records. That ordering lets the engine narrow its search to C4 instead of checking every customer ID. Orders for C4 are grouped together in the index, even if their records are scattered through the table. This is an index of orders by customer, separate from the customer table’s own primary key. The answer is the same; the work needed to find it changes.

That extra route has to stay current. When a new order arrives, the database must add it to the table and update the index so a later lookup can find it. Adding indexes therefore uses more storage and adds work to changes. An index arranged by customer also may not help a report that needs every order from yesterday. Its value depends on the questions the application asks; sometimes reading the table directly is the cheaper option.

Further reading: How indexes work (planned) · Choosing indexes for your queries (planned) · Investigating a slow query (planned)

Constraints

Finding records efficiently is only useful if the records mean what we expect. Our join relies on P7 identifying a product. If an order line could refer to a product that doesn’t exist, there would be no product name to return. A foreign key lets us declare that a reference must match an existing record.

Foreign keys are one kind of constraint. We can also require a value to be present, prevent duplicate identifiers, or reject a negative quantity. If every order must belong to a customer, a required customer ID and an enforced foreign key together reject an order without a valid customer. The rule applies whether the write comes from the checkout application, an import script, or another program. It has to be enforced, though: in SQLite, for example, foreign-key checks must be enabled.

An order can name a real customer and still have no order lines. The rules above allow that, even though we have only recorded part of a purchase.

Further reading: Constraints (draft)

Transactions

To record that purchase, the application needs to create an order, add its lines, and reduce the available stock. Each change could satisfy its own constraints while the purchase as a whole remains incomplete. If adding a line fails after the order has been created, we need a way to undo the unfinished work.

A transaction groups those operations into one unit. If the application commits, their changes are accepted together. If it rolls back, they are discarded: the new order and any lines already added are removed, and any stock reduction made in that transaction is undone. This all-or-nothing property is called atomicity. Only the operations included in the transaction receive this protection.

A committed purchase also needs to survive a restart. Engines commonly record changes in a persistent log, which they can use to recover committed work after a crash. Keeping committed changes is called durability. The protection depends on the engine, settings, and storage; if the stored data itself is lost, recovery needs another copy, such as a backup.

The transaction still has a boundary. Rolling back database changes won’t undo a charge already made through a separate payment service or retrieve an email already sent. The application must account for those outside effects separately.

Further reading: What belongs in one transaction? (draft) · What happens when a write commits? (planned)

Concurrent access

Keeping one transaction’s changes together is only part of the problem. Two requests may read the same record before either updates it. If both see one item in stock, both may decide it is available. Each request can look sensible on its own while their combined result breaks the application’s rule.

Concurrency control governs how these overlapping operations interact. One tool is a lock, which temporarily restricts conflicting access. For example, with PostgreSQL’s default transaction behaviour, each buyer’s request could lock the stock row before checking it, and hold that lock until its transaction ends. The second request waits for the first to finish. If the first commits a purchase of the last item, the second then gets the updated row, sees zero stock, and declines the purchase.

The important part is protecting the check as well as the change. Making the second write wait would not fix a decision it had already made from an earlier stock value. Coordination also has a cost: a slow transaction holding the lock can hold up other buyers.

A report has a different need: it should be able to read records while purchases continue. Many engines keep older versions of records for this purpose. If a price changes during a query, a read using an earlier view can still see the old price. The transaction’s isolation level determines which changes a read can see and which kinds of interference are prevented. For an ordinary read without a row lock, PostgreSQL’s default mode uses a view of data committed before the query began. A later read in the same transaction can see newer changes.

Reading an earlier version does not reserve an item. Two buyers could still see the same stock value and both decide to buy.

Waiting is one possible outcome of a conflict. Another is that the database rejects a transaction and the application has to try the transaction again. The statements and isolation behaviour we choose determine which conflicts can occur and how they are handled.

Further reading: When two requests change the same data (draft) · Isolation and snapshots (draft) · Handling waits, deadlocks, and retries (draft)

When to use a relational database

Relational databases are useful when an application has shared, related information, needs several ways to query it, and must keep updates correct. Orders and inventory, bookings, accounts, and business records commonly have those needs. The combination of queries, rules, and transactions is what makes this approach versatile.

The costs follow from the same capabilities. Supporting many kinds of lookup may mean maintaining several indexes on every change. Coordinating purchases is useful, but requests competing for the same stock record can spend time waiting. A report reading much of the database may compete with small everyday requests for memory, storage access, and processor time. Those pressures help explain why an application might eventually use a different layout or a separate system for some work.

Before adding another system, check what the existing engine can do. A relational database may also store nested product details or search text, sometimes through extensions. Keeping the work together avoids another service and another copy of the data to maintain; separating it may give a demanding workload the resources or specialised behaviour it needs.

PostgreSQL, MySQL, and Microsoft SQL Server are major examples. SQLite brings relational tables and SQL into an engine that runs inside the application process. Their storage, concurrency, deployment, and operating requirements differ; choosing the relational approach is a starting point for choosing a product.

Using the database over time brings further responsibilities: deciding who may change records, evolving the schema while applications use it, and testing that backups can restore the service. The collection below gives these their own articles, alongside the mechanisms introduced here.

Further reading: PostgreSQL, MySQL, and SQL Server (planned) · When an embedded database fits (planned) · When should you add another system? (planned)