A relational database is a database that organizes data into tables of rows and columns, called relations, connects tables through primary and foreign keys, and lets users query and change the data declaratively, almost always with SQL. It is based on the relational model proposed by Edgar F. Codd in 1970 and remains the dominant model in databases for business records, finance and most transactional applications.
Widely used relational database management systems include PostgreSQL, MySQL, MariaDB, Oracle Database, Microsoft SQL Server and SQLite. What they share is a fixed schema, declarative queries that describe what data is needed rather than how to fetch it, and ACID transactions that keep data consistent even when many users write at once or the server crashes.
The relational model behind the database
In the relational model, a relation is a set of tuples that share the same attributes, each attribute drawing values from a defined domain or data type. In practical terms, a relation is a table, a tuple is a row and an attribute is a column. Rows have no inherent order, and each row is identified by its values rather than by its physical position.
| Formal term | Common term | Meaning |
|---|---|---|
| Relation | Table | Set of records with the same structure |
| Tuple | Row or record | One entity or fact |
| Attribute | Column or field | One named property with a data type |
| Domain | Data type with constraints | Allowed values for an attribute |
| Candidate key | Unique identifier | Minimal set of columns that identifies a row |
| Relational schema | Table definition | Name, columns, types and constraints |
Relationships are expressed through values, not pointers. An orders table stores a customer_id column that is a foreign key referencing the primary key of a customers table. A join combines the two at query time, which keeps each fact stored in exactly one place.
How a relational database processes data
When an application sends a SQL statement, the database engine parses it, rewrites it and passes it to a query optimizer. The optimizer estimates the cost of alternative execution plans, for example a full table scan versus an index lookup, or a hash join versus a nested loop join, using statistics about data distribution, and runs the cheapest plan it finds.
Data is stored in fixed-size pages, typically 8 KB in PostgreSQL and 16 KB in MySQL’s InnoDB engine, and accessed through a buffer pool in memory. B-tree indexes provide logarithmic lookups and ordered range scans. Before changes reach data files, they are written to a write-ahead log, which allows the database to recover committed transactions after a crash.
Transactions follow the ACID properties: atomicity, consistency, isolation and durability. Concurrency control, often through multiversion concurrency control (MVCC), lets readers see a consistent snapshot without blocking writers. The SQL standard defines four isolation levels, from read uncommitted to serializable, which trade consistency guarantees against throughput.
Integrity constraints and normalization
A key strength of a relational database is that it enforces data integrity itself, independent of application code. Declared constraints reject invalid data at write time, no matter which program or user attempts the change.
| Constraint | What it guarantees | Example |
|---|---|---|
| Primary key | Each row is unique and identifiable | customer_id is never repeated or null |
| Foreign key | References point to existing rows | An order cannot cite a missing customer |
| Unique | No duplicate values in a column set | One account per email address |
| Not null | A value is always present | Every invoice has a date |
| Check | Values satisfy a condition | Quantity greater than zero |
Normalization organizes tables to remove redundancy and update anomalies. Most operational schemas aim for third normal form, in which every non-key column depends on the key, the whole key and nothing but the key. Analytical systems often denormalize deliberately, for example in star schemas, to reduce joins on large read-heavy queries.
Relational databases compared with other models
Relational databases coexist with NoSQL and specialized models, each optimized for different access patterns. The comparison below shows where the relational approach fits.
| Model | Data shape | Strength | Typical trade-off |
|---|---|---|---|
| Relational | Tables with fixed schema | Joins, constraints, ACID transactions | Schema changes require migrations |
| Document | JSON-like documents | Flexible schema, nested data | Weaker cross-document integrity |
| Key-value | Opaque values by key | Very fast simple lookups | No rich queries |
| Wide-column | Rows with dynamic columns | Massive write throughput at scale | Query patterns fixed in advance |
| Graph | Nodes and edges | Deep relationship traversal | Less suited to aggregate reporting |
The boundaries have blurred. PostgreSQL stores and indexes JSON documents, and distributed SQL systems such as CockroachDB and Google Spanner scale relational transactions horizontally. The relational model therefore remains the default choice for structured, interrelated business data.
Design choices and common mistakes
Most performance and integrity problems in relational databases come from schema and query design rather than the engine. The following mistakes recur across projects.
- Missing indexes on foreign keys and filter columns, which turn lookups into full table scans.
- N+1 query patterns from object-relational mappers that issue one query per row instead of one join.
- Skipping constraints and relying on application code, which lets inconsistent data accumulate.
- Storing lists in a single column, such as comma-separated IDs, which defeats joins and indexing.
- Long-running transactions that hold locks and bloat MVCC versions.
- Over-indexing write-heavy tables, since every index adds cost to each insert and update.
Execution plans, inspected with EXPLAIN, are the main diagnostic tool: they show whether the optimizer uses indexes as expected and where row estimates diverge from reality, which usually points to stale statistics or an unsuitable query shape.
The simple explanation
A relational database is like a set of well-organized spreadsheets that know about each other. One sheet lists customers, another lists orders, and each order carries the customer’s number instead of copying the customer’s details.
Because every fact is stored once, a change in one place is reflected everywhere. The database also follows strict rules. It refuses data that breaks them, such as an order for a customer who does not exist.
How it is used in practice
A regional chain of veterinary clinics runs its appointment and billing system on PostgreSQL. The schema contains tables for owners, pets, appointments, treatments and invoices, linked by foreign keys, and a check constraint prevents appointments that end before they start. Every booking runs in a transaction that reserves the time slot and creates the invoice draft together.
When two receptionists in different clinics try to book the same veterinarian for the same slot, a unique constraint lets the first transaction commit and rejects the second, which then offers the next free slot. Month-end reports join appointments, treatments and invoices with one SQL query that completes in seconds over several years of data. Double bookings and orphaned invoices no longer occur, because the database enforces the rules for every clinic.