RDBMS definition
A relational database is a database that organizes data into tables of rows and columns, links tables through keys, and is queried with SQL. A relational database management system (RDBMS), such as PostgreSQL, MySQL, SQL Server or Oracle, enforces schemas, constraints and ACID transactions, keeping structured business data accurate and consistent.
How does a relational database work?
Data is stored in tables, each describing one kind of entity, such as customers, orders or products. Every row has a primary key that uniquely identifies it, and foreign keys link related rows across tables, for example each order referencing the customer who placed it. SQL queries combine tables with joins, filter and aggregate data, and the database's query planner decides how to retrieve results efficiently using indexes and statistics about the data.
The schema defines column types and constraints, such as not null, unique and check rules, which the database enforces on every write. This keeps invalid data out regardless of which application or user is writing, a major reason relational databases remain the default system of record for business software.
What are ACID transactions?
Relational databases group changes into transactions with four guarantees, known as ACID. Together they ensure that operations such as transferring money between accounts or booking the last seat on a flight either complete fully or not at all, even when many users act at once or a server crashes in the middle of an operation.
- Atomicity: all changes in a transaction succeed or none do.
- Consistency: every transaction leaves the data valid under all rules.
- Isolation: concurrent transactions do not interfere with each other.
- Durability: committed changes survive crashes and power loss.
Normalization and data modeling
Relational design usually follows normalization: storing each fact once and referencing it elsewhere, rather than duplicating it. A customer's address lives in one row, not copied into every order. This prevents inconsistencies when data changes and keeps storage efficient. Analytical databases sometimes denormalize deliberately for faster queries, but operational systems benefit from normalized designs, sensible indexes and well-chosen keys that match how the application actually queries data.
Indexes deserve particular care. They make reads fast by letting the database find rows without scanning whole tables, but each index slows writes and uses storage. Reviewing slow query logs and execution plans with tools such as EXPLAIN shows which indexes help, which are unused, and where a query should be rewritten instead.
Popular relational databases
PostgreSQL is a feature-rich open-source database with strong standards support and extensions such as PostGIS and pgvector. MySQL and its fork MariaDB power a large share of web applications. Microsoft SQL Server and Oracle Database dominate many enterprises, and SQLite is embedded in mobile apps and devices. Managed cloud services, including Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, and Azure SQL, handle backups, patching and replication automatically. Choice often follows ecosystem and hosting.
Relational vs NoSQL databases
Relational databases excel at structured, interrelated data, complex queries and strict consistency. NoSQL databases excel at flexible schemas, massive horizontal scale and specific access patterns such as key lookups or graph traversal. Most applications are well served by a relational database as the system of record, adding NoSQL stores for caching or specialized workloads. Nexzem defaults to PostgreSQL for new client applications for this reason.