Skip to main content

How Relational Databases Really Work

Relational databases are the silent engines behind most of the world’s business software. They do not merely store data — they guarantee its integrity, answer complex questions with a single statement, and protect against chaos when hundreds of users access the same information simultaneously. This article explains the principles that make all of that possible.

You will learn how a relational database is structured, why it works the way it does, and what happens inside the engine when you issue a query. This is not a syntax tutorial; it is a tour of the relational model from the inside out, designed to give you the mental framework that makes schema design, performance tuning, and architectural decisions far easier.

What Is a Relational Database?​

A relational database is a system that organises data into tables (formally called relations) composed of rows (tuples) and columns (attributes). The structure is not arbitrary — it follows a mathematical model proposed by Edgar F. Codd in 1970, which defines how data can be stored, queried, and kept consistent through declarative rules.

Common relational database management systems (RDBMS) include:

  • PostgreSQL — advanced open‑source database with a rich extension ecosystem.
  • MySQL — widely used in web applications, known for speed and ease of use.
  • Oracle Database — enterprise‑grade system with extensive management features.
  • Microsoft SQL Server — deeply integrated with the Microsoft development stack.

While these products differ in implementation, they all share the same foundational principles: tables with typed columns, relationships enforced by keys, and a declarative language (SQL) for querying.

Why Relational Databases Were Invented​

Before the relational model, data was stored in hierarchical or network databases, or simply in flat files. Each approach had severe limitations:

  • File‑based storage — no query language; searching required scanning entire files. Concurrency and consistency were entirely the application’s responsibility.
  • Hierarchical databases — data was organised as trees. Accessing data along a different path than the hierarchy’s design required painful workarounds or duplication. Relationships like many‑to‑many were nearly impossible to model.
  • Network databases — more flexible than hierarchical, but the developer still had to navigate the physical storage structure explicitly. Adding a new relationship meant rewriting large parts of the application.

Codd’s relational model solved these problems with three powerful ideas:

  1. Data independence — the logical structure (what data exists) is separated from the physical storage (how it is laid out on disk). You can add an index without rewriting every query.
  2. Declarative queries — you describe what you want, not the navigational path to get there. The database engine itself decides the most efficient way to retrieve the data.
  3. Integrity constraints — rules about what values are allowed are part of the schema, enforced by the database, not left to every application that accesses it.

These innovations made it possible to build complex business systems that could be maintained, evolved, and reasoned about independently of the hardware.

The Relational Model​

Understanding the formal terms gives you a precise vocabulary and reveals the simplicity of the design. The core concepts are:

Relational Model TermCommon Database EquivalentMeaning
RelationTableA set of rows with the same structure.
TupleRowA single record in a relation.
AttributeColumnA named field within a tuple, drawn from a specific domain.
DomainData typeThe set of allowed values for an attribute (e.g., integer, text, date).
Primary KeyPrimary keyA unique identifier for each tuple; a relation cannot contain duplicate tuples.
Foreign KeyForeign keyA reference from one relation to the primary key of another, establishing a link.

The power of this model lies in its mathematical foundation: all operations (joins, projections, selections) are defined in terms of set theory, which guarantees predictable results. When you join two tables, you are performing a relational operation whose outcome is well‑defined, regardless of the database product.

Tables, Rows, and Columns​

Consider a simple e‑commerce system. The core entities might be represented as these three relations:

Customer
-----------
id (PK)
name
email

Order
-----------
id (PK)
order_date
customer_id (FK -> Customer.id)

Product
-----------
id (PK)
name
price

OrderItem
-----------
order_id (FK -> Order.id)
product_id (FK -> Product.id)
quantity

In this design:

  • Customer and Order have a one‑to‑many relationship: a customer can place many orders.
  • Order and Product have a many‑to‑many relationship, implemented via the OrderItem junction table.

Every table represents a business concept, not a screen or a report. This separation is deliberate. If the business later decides to add subscription services, the Customer table remains stable; new tables are added without disrupting existing ones. This is a direct consequence of modelling the business domain, not the application UI.

Keys in Relational Databases​

Keys are the mechanism that gives each row an identity and links tables together.

Primary Key​

Every table must have a primary key — a column (or combination of columns) that uniquely identifies each row. The database enforces this uniqueness, rejecting any attempt to insert a duplicate. Common choices include:

  • Auto‑increment integer — simple, sequential, and index‑friendly.
  • UUID — globally unique, useful in distributed systems, but can fragment indexes.
  • ULID — sortable, time‑based unique identifier that combines uniqueness with index efficiency.

The primary key is the default target of foreign keys and the basis for the table’s main index.

Candidate Key and Alternate Key​

A candidate key is any column or set of columns that could serve as a primary key. For example, both email and username in a User table are candidates because they are unique. After you choose one as the primary key, the remaining candidates become alternate keys — still unique, but not the primary identifier.

Composite Key​

A primary key that consists of two or more columns is a composite key. In the OrderItem table above, (order_id, product_id) together form a composite primary key: the combination is unique, but neither column alone is sufficient. Composite keys are essential for junction tables representing many‑to‑many relationships.

Foreign Key​

A foreign key is a column (or set of columns) that references the primary key of another table. It enforces referential integrity — you cannot create an Order with a customer_id that does not exist. If you try to delete a Customer that still has orders, the database will either block the deletion, cascade it, or set the foreign key to null, depending on the defined constraint rule.

Referential integrity is a cornerstone of data quality. It eliminates a whole class of bugs — orphaned records, dangling references, and inconsistent states — that are nearly impossible to catch reliably in application code.

Relationships Between Tables​

The relational model supports three fundamental relationship types, all implemented through keys.

One‑to‑One​

Each row in table A relates to at most one row in table B. Implemented by placing a foreign key with a UNIQUE constraint in either table. Example: a User and a UserProfile table, where each user has exactly one profile.

One‑to‑Many​

A single row in table A relates to many rows in table B. The “many” side holds a foreign key pointing to the “one” side. This is the most common relationship. Example: a Customer can have many Orders; the Order table has a customer_id foreign key.

Many‑to‑Many​

Rows in table A can relate to many rows in table B, and vice versa. Requires an intermediate junction table that contains foreign keys to both tables. In the earlier schema, OrderItem serves as the junction between Order and Product. Each row in OrderItem represents one product within one order.

These relationships are not just documentation; they are enforced at the database level. The schema itself becomes a contract that guarantees data coherence.

Constraints​

Constraints are rules that the database engine enforces on every data change. They are the first line of defense against bad data.

ConstraintPurposeExample
PRIMARY KEYUniquely identifies each row.id column in Customer.
FOREIGN KEYEnsures a value exists in the referenced table.customer_id in Order.
UNIQUEGuarantees no duplicate values in a column or group of columns.email column in User.
CHECKValidates a condition on each row.price > 0 on a Product.
NOT NULLPrevents a column from being empty.order_date in Order must always be present.

Constraints improve data quality (no impossible values), reliability (the database itself blocks errors), and maintainability (the rules are documented in the schema, not scattered across application code). They are cheap to define and extraordinarily expensive to retro‑fit after bad data has accumulated.

SQL: The Language of Relational Databases​

SQL (Structured Query Language) is the standard interface to relational databases. It is a declarative language: you describe the result set you want, not the procedure to obtain it.

SQL is divided into several categories of statements:

  • DDL (Data Definition Language) — CREATE TABLE, ALTER TABLE, DROP TABLE. Defines the schema.
  • DML (Data Manipulation Language) — INSERT, UPDATE, DELETE. Modifies data.
  • DQL (Data Query Language) — SELECT. Retrieves data, often with joins, filters, and aggregations.
  • DCL (Data Control Language) — GRANT, REVOKE. Manages permissions.
  • TCL (Transaction Control Language) — COMMIT, ROLLBACK. Controls transactions.

A typical SELECT statement reads like a sentence describing the desired output, not the execution steps:

SELECT customer.name, order.order_date, product.name
FROM customer
JOIN "order" ON customer.id = "order".customer_id
JOIN order_item ON "order".id = order_item.order_id
JOIN product ON order_item.product_id = product.id
WHERE order.order_date >= '2025-01-01';

The database engine parses this, explores multiple execution strategies, picks the one with the lowest estimated cost, and returns the result. The application never knows — or needs to know — whether the engine used an index scan, a hash join, or a nested loop.

How Queries Work​

When a query arrives, it undergoes several stages before returning data. Understanding this pipeline demystifies performance and explains why seemingly small query changes can have huge effects.

Application
      |
   SQL Query
      |
   Parser
      |
   Rewriter (optional)
      |
   Planner / Optimizer
      |
   Executor
      |
   Storage Engine
      |
   Data files & Indexes
  • Parser — checks syntax and builds a parse tree representing the query structure.
  • Rewriter — applies rules like view expansion or row‑level security policies (not always present).
  • Optimizer — generates multiple possible execution plans (different join orders, index usage, scan methods), estimates the cost of each based on table statistics, and selects the cheapest plan.
  • Executor — runs the chosen plan, fetching data from the storage engine.
  • Storage Engine — manages data pages, buffer pool, and disk I/O.

The optimizer is the heart of performance. It uses statistics about table sizes and value distributions to predict how many rows each step will process. If statistics are stale, the chosen plan can be far from optimal — a common source of “why is this query suddenly slow?”. The Performance section covers execution plan analysis in depth.

Why Indexes Matter​

Without indexes, a query that needs a single row from a table of a million rows must scan every single row — a full table scan. With an index, the database can navigate a B‑tree or hash structure to find the relevant rows directly, often reading only a few pages from disk.

An index is a separate data structure that maps values to row locations. When you filter on an indexed column, the engine uses the index to locate matching rows instead of scanning the table. Indexes can also be used for sorting and for covering queries (where all requested columns are in the index, eliminating table access entirely).

Indexes are not free: they consume disk space, and every INSERT, UPDATE, and DELETE must maintain them. Designing the right indexes for the actual query workload is one of the most important database engineering skills. The How Database Indexes Work article explores this trade‑off in detail.

Transactions in Relational Databases​

A transaction bundles multiple operations into a single logical unit. The database guarantees that either all operations succeed (commit) or none of them do (rollback). This is the A (Atomicity) in ACID.

The full set of ACID properties are:

  • Atomicity — all‑or‑nothing execution.
  • Consistency — the database transitions from one valid state to another, respecting all constraints.
  • Isolation — concurrent transactions do not interfere; they produce the same result as if they ran sequentially (the degree of isolation is configurable).
  • Durability — once committed, a transaction survives crashes and power failures.

Transactions are essential for any system where data correctness is non‑negotiable. In a banking application, transferring money from account A to B must debit A and credit B atomically — a partial execution would either lose or create money. In an inventory system, accepting an order must atomically reduce stock to prevent overselling.

The dedicated ACID Transactions article and the Isolation Levels article provide a rigorous examination of these guarantees and the trade‑offs between performance and safety.

Advantages of Relational Databases​

Relational databases remain the default choice for most business systems because of several compelling strengths:

  • Strong consistency — ACID transactions and constraints prevent whole categories of data corruption.
  • Mature ecosystem — decades of development, proven backup and recovery tools, extensive monitoring, and a vast pool of skilled practitioners.
  • Powerful declarative queries — SQL allows complex data retrieval with minimal code, and the optimizer handles performance automatically in most cases.
  • Relationships modelled naturally — foreign keys and joins mirror how business entities relate in the real world.
  • Standardised — SQL is an ANSI/ISO standard; skills transfer across products.
  • Rich indexing — B‑trees, hash indexes, GIN, GiST (in PostgreSQL) support a wide variety of query patterns.
  • Transaction support — battle‑tested isolation levels and locking mechanisms protect data integrity under concurrency.

These advantages explain why financial ledgers, ERP systems, and e‑commerce platforms overwhelmingly run on relational databases.

Limitations of Relational Databases​

No technology is perfect. Engineers must be aware of the boundaries of the relational model:

  • Schema rigidity — changing a table structure (adding a column, altering a type) requires a migration, which on large tables can be slow and disruptive.
  • Horizontal scaling complexity — while read replicas and partitioning exist, distributing write loads across multiple nodes (sharding) is far harder than in many NoSQL systems. It introduces operational overhead and cross‑shard query limitations.
  • Distributed deployment — traditional relational databases were designed for single‑node operation. Distributed SQL databases (CockroachDB, YugabyteDB) are bridging this gap but are relatively new compared to NoSQL alternatives.
  • Not ideal for all workloads — full‑text search, real‑time analytics, and graph traversal are better served by specialised databases. A relational database can often handle them up to a point, but pushing beyond that point leads to fragile workarounds.

These are not weaknesses per se, but engineering trade‑offs. The key is to recognise when a workload has outgrown the relational sweet spot and would benefit from a complementary store.

Relational Databases in Modern Architectures​

Modern applications rarely consist of a single database. The relational database anchors the transactional core, while other specialised stores handle specific workloads. This pattern is called polyglot persistence.

Consider a typical web platform:

Web Application
        |
---------------------------------------------------------
|   PostgreSQL   |   Redis   |   Elasticsearch   |
| (orders,       | (session, | (product search)  |
|  inventory)    |  cache)   |                   |
---------------------------------------------------------
        |
   Vector Database (pgvector / Milvus)
   (semantic recommendations)
  • PostgreSQL holds the source‑of‑truth transactional data: customers, orders, payments.
  • Redis caches session data and popular product pages, offloading repetitive reads from PostgreSQL.
  • Elasticsearch indexes product names and descriptions, providing fast full‑text search with typo tolerance.
  • The vector database stores product embeddings, enabling semantic search and recommendation.

The relational database is still at the centre. It is the system of record. Other stores are secondary indexes or caches, derived from the relational source.

Common Misconceptions​

“SQL Equals Relational Databases”​

SQL is the dominant query language for relational databases, but some NoSQL systems also offer SQL‑like interfaces. Conversely, a few relational databases support non‑SQL query modes. The relational model is about the structure (tables, keys, constraints), not the language syntax.

“Tables Are Just Spreadsheets”​

Spreadsheets lack enforced types, constraints, relationships, and concurrency control. A spreadsheet cell can contain anything; a database column has a specific type and constraints that apply to every row. The difference is the difference between a scratchpad and a safety‑critical control system.

“Foreign Keys Are Optional”​

Skipping foreign keys to “keep things flexible” is one of the costliest shortcuts in database design. Without them, referential integrity must be enforced in application code — code that every new developer must learn, every new service must duplicate, and every bug in it leads to orphaned records and corrupted reporting.

“ORMs Eliminate the Need to Understand Databases”​

Object‑Relational Mappers (ORMs) translate between application objects and database rows, but they do not think for you. Inefficient queries generated by an ORM can silently cause full table scans, N+1 query problems, and transaction mismanagement. Understanding the relational model allows you to use an ORM effectively and recognise when it is producing sub‑optimal SQL.

Best Practices​

These principles, drawn from production experience, will help you build databases that last:

  • Model business entities, not screens or endpoints. A table should represent a real‑world thing (Customer, Order) that is stable across UI changes.
  • Use constraints aggressively. Every NOT NULL, UNIQUE, and FOREIGN KEY you add today is a production bug you will not have to debug tomorrow.
  • Design relationships deliberately. For each relationship, ask: is this one‑to‑one, one‑to‑many, or many‑to‑many? Implement it with the correct foreign key structure.
  • Normalise to reduce redundancy; denormalise only with a measured reason. Start with third normal form, then selectively denormalise if performance requires it, while documenting the trade‑off.
  • Understand transactions. Know which isolation level your application needs and use it explicitly. The default may not provide the guarantees you require.
  • Learn to read execution plans. The optimizer is not magic; its decisions can be debugged and influenced.
  • Avoid premature optimisation. Write correct queries first. When a performance problem arises, measure, identify the bottleneck, and then optimise.

Continue deepening your relational database knowledge with these articles:

Key Takeaways​

  • The relational model organises data into tables (relations) with rows (tuples) and columns (attributes), enforced by a precise mathematical foundation.
  • Keys identify rows and establish relationships; constraints enforce data integrity at the database level, making applications more reliable.
  • SQL is a declarative language that separates what you want from how to get it; the database optimizer handles the execution strategy.
  • Internally, a query goes through parsing, optimization, and execution; understanding this pipeline is essential for performance work.
  • Transactions provide ACID guarantees that protect correctness under concurrency and failure — the bedrock of financial and business systems.
  • Relational databases excel at structured, relationship‑rich data but can be complemented by specialised stores (caches, search engines, vector databases) in a polyglot architecture.

Frequently Asked Questions​

Are relational databases still relevant?​

Absolutely. They remain the default choice for transactional business systems, financial ledgers, ERP, CRM, and any application where data integrity and complex queries are required. Their combination of strong consistency, mature tooling, and standardised query language is unmatched.

Is SQL the same as a relational database?​

No. SQL is the query language, while a relational database is a system that implements the relational model. Most relational databases use SQL, but the concepts of tables, keys, and constraints exist independently of the language.

Why are foreign keys important?​

Foreign keys enforce referential integrity automatically. They prevent orphaned records, ensure that relationships always point to valid rows, and document the connections between tables. Skipping them places the entire burden of correctness on application developers.

Should every table have a primary key?​

Yes. A table without a primary key cannot uniquely identify its rows, making updates and deletions ambiguous. It also cannot serve as the target of foreign keys. Most database designs benefit from a surrogate key (auto‑increment integer or UUID) even when a natural key exists.

Can relational databases scale horizontally?​

Yes, but with more effort than in many NoSQL systems. Horizontal scaling can be achieved through read replicas, partitioning, and sharding. Distributed SQL databases are making this easier, but traditional RDBMS sharding still requires careful planning and ongoing operational attention.

Which relational database should beginners learn first?​

PostgreSQL is an excellent choice. It is standards‑compliant, feature‑rich, and has a large community. MySQL is also a solid option, especially if you are building for the LAMP/LEMP stack. The concepts learned in one transfer to the other and to most other relational databases.

What's Next?​

Now that you have a solid understanding of the relational model and how relational databases function internally, the next step is to dive into the guarantees that make them safe for critical data.

Continue with:

Mastering the relational model is the foundation for every other topic in database engineering: indexing, query optimisation, replication, and distributed architectures all build upon this base.