What is a relational database? How relational databases work

A relational database is a database that organizes data into one or more tables (also called relations) of rows and columns, where each row is a record with a unique key and each column is a field with a defined type. Relationships between tables are expressed by referencing keys, and the dominant language for querying and modifying the data is SQL. The relational model was proposed by Edgar F. Codd in 1970, and most operational databases in use today still follow it.

What is a relational database?

A relational database is a database whose logical schema is based on the relational model introduced by Edgar F. Codd in his 1970 paper "A Relational Model of Data for Large Shared Data Banks". In this model:

• A table (relation) is a set of rows that share the same structure.
• A row (tuple) is one record, a single instance of what the table describes.
• A column (attribute) is a named field with a defined data type (integer, text, date, etc.).

Every row in a relation must be unique, and uniqueness is enforced by a primary key — one or more columns whose values never repeat inside the table. References to rows in other tables are stored as foreign keys, and referential integrity rules keep those references valid.

The language used to define, query, and manipulate these tables is SQL (Structured Query Language). SQL is declarative: a query states what data to retrieve, and the database engine decides how to retrieve it. The key distinction from earlier file systems and from many NoSQL stores is that the relational model enforces a structured schema before data is inserted. Types, constraints, and keys are declared up front, and the database rejects writes that violate them.

How tables and relationships work

Three kinds of relationships can exist between tables:

• One-to-one — each row in table A corresponds to at most one row in table B (for example, a users table and a users_private table that holds sensitive data split off for access control).
• One-to-many — each row in table A can relate to many rows in table B, but each row in B relates to only one row in A. The classic example is a customers table and an orders table: one customer can place many orders, but each order belongs to one customer.
• Many-to-many — rows in A can relate to many rows in B and vice versa. Students enroll in many courses; each course has many students. Many-to-many relationships are implemented with a third junction table whose rows pair primary keys from both sides.

The mechanics look like this:

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT UNIQUE
);

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  amount     NUMERIC(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL
);

-- Insert a customer
INSERT INTO customers (id, name, email) VALUES (1, 'Alice', 'alice@example.com');

-- Insert an order that references that customer
INSERT INTO orders (id, customer_id, amount, created_at)
VALUES (100, 1, 49.99, '2026-09-04 10:00:00');

-- Retrieve each order with the customer's name
SELECT o.id, c.name, o.amount, o.created_at
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  c.email = 'alice@example.com';

The REFERENCES clause declares the foreign key; the database engine refuses to insert an order whose customer_id does not already exist in customers. The JOIN clause combines rows from both tables based on the key match. Tools such as dbt build layered SQL models on top of an RDBMS like postgres, and cloud services like supabase expose a managed postgres instance with auth and APIs layered on top.

Normalization: reducing redundancy

Normalization is the process of restructuring a relational schema to minimize redundancy and avoid certain update anomalies. Edgar F. Codd defined a series of normal forms, each one stricter than the last:

• First Normal Form (1NF) — every column holds an atomic (indivisible) value, there are no repeating groups, and each row is unique. A column that stores a comma-separated list of phone numbers violates 1NF; the fix is a separate phone_numbers table.
• Second Normal Form (2NF) — the table is in 1NF and every non-key column depends on the whole primary key, not just part of it. This matters only for tables with composite primary keys. A (student_id, course_id) → instructor dependency violates 2NF if instructor depends only on course_id.
• Third Normal Form (3NF) — the table is in 2NF and has no transitive dependencies: non-key columns do not depend on other non-key columns. If course_id → department → department_head, then department_head should move to a departments table.
• Boyce–Codd Normal Form (BCNF) — a stricter version of 3NF that handles the rare cases 3NF misses, where a non-key attribute determines part of a candidate key.

The practical trade-off is between write-time integrity and read-time performance. A fully normalized schema removes duplication, so updating a value (a customer's address, say) happens in exactly one place. But heavy normalization means more joins at query time, which can be slow on large datasets. Many production systems therefore denormalize selectively — for example, storing a cached total_orders count on a customer row to avoid a COUNT(*) join on every page load — while keeping the rest of the schema normalized. Analytics warehouses built on platforms like snowflake typically denormalize further, using wide star or snowflake schemas for query speed.

ACID transactions

A relational database is almost always paired with ACID transactions: the atomicity, consistency, isolation, and durability guarantees covered in detail in our transactions guide. In practice this means:

• A set of related writes either all happen or none of them do.
• The database never leaves constraints in a broken state.
• Concurrent transactions don't see each other's uncommitted changes.
• Committed changes survive crashes and power loss.

Consider a bank transfer:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- debit
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;   -- credit
COMMIT;

If the second UPDATE fails (network error, constraint violation, system crash), the BEGIN/COMMIT pair is not completed and the database engine rolls the whole transaction back. Account 1 is not debited. This is the property that makes relational databases the default choice for finance, e-commerce inventory, and any system where partial writes are unacceptable.

SQL: the query language

SQL evolved from SEQUEL, a language developed by Donald D. Chamberlin and Raymond F. Boyce at IBM in the early 1970s for their System R prototype. The first commercially available RDBMS was Oracle V2, released in 1979. SQL was standardized by ANSI in 1986 and by ISO in 1987, and the standard has been revised several times since — most recently as SQL:2023.

Conceptually SQL is grounded in relational algebra, which defines a small set of operators on relations: selection (filter rows), projection (pick columns), join (combine tables by key), union (merge compatible rows), and so on. Real SQL also includes grouping, aggregation, and subqueries.

The four core data-manipulation statements are:

• SELECT — retrieve rows from one or more tables.
• INSERT — add new rows.
• UPDATE — modify existing rows.
• DELETE — remove rows.

Schema definition uses CREATE TABLE, ALTER TABLE, and DROP TABLE, plus constraint clauses such as PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK. SQL is the common surface across virtually all RDBMSs, including postgres, MySQL, SQLite, SQL Server, and Oracle.

Popular relational database systems

A few systems dominate the landscape:

• PostgreSQL — open-source RDBMS widely considered the most feature-complete. Advanced types, indexing, JSON support, extensions, and strict standards conformance. Often run on its own, on top of a managed provider like supabase, or as the engine behind a cloud warehouse product.
• MySQL — the most widely deployed open-source RDBMS, especially in web stacks. Owned by Oracle; the MariaDB fork is also popular.
• SQLite — a serverless, embedded library. Not a separate process — the engine is linked into the application. The most widely deployed database engine in the world by install count, found in phones, browsers, and countless desktop apps.
• Microsoft SQL Server — a commercial RDBMS with deep Windows / Azure integration and a long history in enterprise IT.
• Oracle Database — the original commercial RDBMS lineage (from Oracle V2 in 1979); still dominant in large enterprise deployments.

Most modern analytics and transformation tooling targets these engines. dbt compiles SQL into version-controlled models that run on any of them; snowflake is a cloud-native SQL data warehouse that separates storage from compute.

When to use a relational database

A relational database is the right choice when:

• The data has a clear, stable structure with relationships between entities (users, orders, products, invoices, and the joins between them).
• You need ACID transactions — money movement, inventory, anything where partial writes are unacceptable.
• The workload needs complex joins across multiple tables.
• Data integrity, defined constraints, and a single source of truth matter more than schema flexibility.

NoSQL alternatives (document, key-value, graph, columnar) win when the data is unstructured, the workload is write-heavy at massive scale and can tolerate eventual consistency, or the access patterns don't fit a tabular model. In practice most production systems use both: a relational database for the system of record, plus specialized stores on the side.

FAQ

What is a relational database?

A relational database is a database that stores data in tables of rows and columns, enforces a predefined schema with typed columns, and uses primary and foreign keys to express relationships between tables. The standard language for working with these databases is SQL.

What is normalization in a relational database?

Normalization is the process of restructuring tables so that each piece of data lives in exactly one place, which avoids update, insertion, and deletion anomalies. It is graded into normal forms — 1NF, 2NF, 3NF, BCNF — each with stricter rules about keys and dependencies.

What is the difference between SQL and NoSQL?

SQL databases (postgres, MySQL, Oracle, SQL Server, SQLite) use a structured tabular schema and SQL to query it, and typically provide ACID transactions. NoSQL databases (document, key-value, wide-column, graph) use flexible schemas, scale horizontally, and usually trade strict consistency for availability under the BASE model.

When should you use a relational database?

Use a relational database when the data has a clear structure with relationships between entities, when ACID transactions are required, when you need joins across multiple tables, and when data integrity matters more than schema flexibility. For unstructured or rapidly changing data at very large scale, a NoSQL store is often a better fit.