Relational (SQL) Databases: Tables, Keys and ACID Transactions

A relational (SQL) database stores data in tables of rows and columns, links tables through keys, enforces a schema before data gets in, and wraps related changes in ACID transactions. That structure is what keeps money, orders and inventory correct when many users act at once and machines fail mid-operation. PostgreSQL, MySQL, SQL Server and Oracle are the classic examples.

The problem without structure

A bank processes a transfer: customer A sends $100 to customer B. That takes two steps: deduct $100 from A, then add $100 to B. If a network error strikes after the deduction but before the addition, A has lost $100, B has gained nothing, and the money has simply vanished.

Plain files can't prevent this, and neither can most simple key-value stores. You need coordinated operations that either fully succeed or fully fail. That guarantee is the foundation of SQL databases.

Why SQL databases exist

Production systems hit three related problems that unstructured storage can't solve on its own:

1. Race conditions corrupt data. Two ticket buyers both read seats_available = 1 at the same moment, both proceed, and the second write overwrites the first: two customers now hold tickets for one seat. Files and simple stores let both writes succeed. See optimistic vs pessimistic locking.
2. Partial failures leave inconsistent state. An e-commerce order decrements inventory, takes payment and creates the order. If payment succeeds but the inventory update fails, the store oversells, and someone spends hours or days reconciling by hand.
3. Unstructured data drifts. One engineer stores phone numbers as integers, another as strings with dashes, a third adds country codes. Queries and reports start breaking. An enforced schema stops this drift.

SQL databases answer all three with structured schemas and transactional guarantees.

How relational databases work

Each table represents one kind of entity (customers, orders, products). Each row is one instance, and each column is an attribute with a fixed data type. The schema enforces rules before data enters the database:

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    Name       varchar(255) NOT NULL,
    Email      varchar(255) UNIQUE,
    Country    varchar(255),
    Balance    decimal(10,2) DEFAULT 0.00
);

CustomerID must be a unique integer, Name can't be null, Email must be unique across all rows, and Balance is stored as a decimal with two digits of scale. Trying to insert "invalid" into CustomerID fails immediately.

Primary keys uniquely identify each row, so no two customers share a CustomerID. The database automatically indexes the primary key for fast lookups (see database indexing); without one, you can't reliably update or delete a specific row.

Tables, relationships and foreign keys

Tables link through foreign keys. An Orders table references Customers through CustomerID:

CREATE TABLE Orders (
    OrderID    int PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount     decimal(10,2),
    OrderDate  timestamp,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

The constraint enforces referential integrity: INSERT INTO Orders (CustomerID) VALUES (999) fails if customer 999 doesn't exist, so the database never holds orders that point at nobody. Relationships also make cross-table questions easy. All orders for one customer:

SELECT o.OrderID, o.Amount, o.OrderDate
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.Email = 'user@example.com';

The database uses indexes to run this join efficiently instead of scanning whole tables. The simplest picture of the idea:

Three kinds of relationships

• One-to-one: each user has exactly one profile (a profiles.user_id that is also unique).
• One-to-many: one user has many orders (orders.user_id references users.user_id).
• Many-to-many: many students enroll in many classes. This needs a junction table that stores both student_id and class_id.

CREATE TABLE enrollments (
    student_id int REFERENCES students(student_id),
    class_id   int REFERENCES classes(class_id),
    PRIMARY KEY (student_id, class_id)          -- a student can enroll in a class once
);

-- Everyone in class 7, with names
SELECT s.name FROM enrollments e JOIN students s ON s.student_id = e.student_id
WHERE e.class_id = 7;

ACID transactions

A transaction groups several operations into one unit with four guarantees. See ACID vs BASE for the contrast with eventually consistent systems.

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100
 WHERE AccountID = 'A' AND Balance >= 100;
UPDATE Accounts SET Balance = Balance + 100
 WHERE AccountID = 'B';
COMMIT;

Isolation comes in levels that trade correctness for performance; see transactions and isolation levels.

Durability through the write-ahead log. Before changing data files, the database appends each change (old and new value) to a sequential log. After a crash it replays the log to restore a consistent state. Sequential appends are fast, so durability doesn't cost much write performance. See write-ahead logging.

Basic SQL operations

Four statements cover everyday work: SELECT reads, INSERT adds rows, UPDATE changes rows and DELETE removes them. Combined with transactions, they build complex workflows that stay consistent.

-- Read: customers in the USA
SELECT Name, Email FROM Customers WHERE Country = 'USA';

-- Create: add a customer
INSERT INTO Customers (Name, Email, Country)
VALUES ('Acme Corp', 'contact@acme.com', 'USA');

-- Update: change contact details
UPDATE Customers SET Email = 'new@acme.com' WHERE CustomerID = 42;

-- Delete: remove a customer
DELETE FROM Customers WHERE CustomerID = 42;

When to use a SQL database

SQL databases shine when relationships and integrity matter more than flexibility:

• Financial systems need atomic multi-account updates: a payment debits one account and credits another, and both must succeed or fail together. See payment systems and ledgers.
• E-commerce manages inventory, orders and customers with strict referential integrity: an order must reference an existing customer and product, inventory decrements must match order creation, and deleting a product with pending orders fails automatically.
• Healthcare stores patient records, appointments and prescriptions; regulations demand audit trails of who accessed which record and when, which SQL databases support with row-level permissions and transaction logs.
• Reporting runs complex analytical queries that the query optimizer handles efficiently. In a key-value store, the same question would need application-level processing.

-- Revenue by product category and region since October 2024
SELECT p.Category, c.Region, SUM(o.Amount) AS Revenue
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products  p ON o.ProductID  = p.ProductID
WHERE o.OrderDate >= '2024-10-01'
GROUP BY p.Category, c.Region;

Popular relational databases

Challenges with SQL databases

Scaling up vs scaling out

One server handles limited throughput. Vertical scaling (more CPU, RAM, faster disks) is simple but gets expensive: RAM is priced roughly linearly at commodity tiers, but at the high end doubling from 256 GB to 512 GB can cost more than 2×, and eventually you hit physical limits. See scalability and scaling databases.

Horizontal scaling spreads data over several servers by sharding on a partition key: users by ID range (IDs 1–1,000,000 on shard A, 1,000,001–2,000,000 on shard B), orders by creation date. Single-user queries stay fast, but queries spanning shards get expensive: cross-shard aggregations query every shard and merge, and joins across servers need network round trips and application-level coordination. Choose partition keys to minimize cross-shard work.

Replication helps with reads: write to a primary, and replicas copy the changes and serve reads. That works when reads vastly outnumber writes; write-heavy workloads still bottleneck on the primary.

Schema changes at scale

Adding a column to a billion-row table can lock it for hours. Production teams use online migration tools that copy data incrementally while still serving traffic; GitHub's gh-ost is a well-known example. Large migrations are usually done in phases: dual-write to old and new structures, backfill, switch reads, then remove the old path.

Microservices

A monolith typically shares one database. Microservices favor a database per service, which improves independence but loses transactions across services; you then need distributed transactions or sagas, which add complexity.