Relational Databases and ACID
Why this matters: relational databases are the default for a reason, and "we'll use NoSQL for scale" is a weak answer if you cannot articulate what you are giving up. ACID is the thing you are giving up.
Key takeaway
Relational databases enforce a predefined schema before storing data. Data lives in relations (tables) of tuples (rows) and attributes (columns), each tuple has a unique key, and tuples link across tables via foreign keys. SQL manages insertion, deletion, and retrieval.
Why they dominate
Relational databases are dominant due to their simplicity, robustness, flexibility, and scalability. To maintain data integrity they provide ACID properties — a powerful abstraction that automatically handles complex anomalies such as dirty reads, lost updates, and phantom reads, so application logic does not have to.
The four properties
| Property | Guarantee | What it prevents |
|---|---|---|
| Atomicity | Transactions are atomic units — either all statements execute successfully or none do; failed transactions are aborted and rolled back | Half-applied operations, like a debit without its matching credit |
| Consistency | The database is in a consistent state before and after every transaction; rules enforce data validity | Invariant violations — a foreign key pointing at nothing |
| Isolation | Concurrent transactions do not affect each other; the final state matches sequential execution | Dirty reads, lost updates, phantom reads |
| Durability | Once committed, a transaction survives permanently, even through system failure | Losing an acknowledged write to a crash |
Popular relational database management systems include MySQL, Oracle Database, Microsoft SQL Server, IBM DB2, PostgreSQL, and SQLite.
Why relational databases stay the default
Their primary strengths are the abstraction of ACID transactions and the standardization of programming semantics — SQL means the skill transfers between systems. Beyond that:
| Strength | What it means |
|---|---|
| Flexibility | SQL's data definition language (DDL) allows schema modification — adding tables, renaming columns, altering structures — even while the server is running and serving queries |
| Reduced redundancy | Normalization eliminates duplication: an entity's information lives in one table, linked elsewhere by foreign key, so it is updated in exactly one place |
| Concurrency | Transactions manage many simultaneous readers and writers, preventing inconsistencies such as double-booking a hotel room |
| Integration | A shared relational database lets multiple applications aggregate data from one source, with the database handling concurrency control between them |
| Backup and disaster recovery | Guaranteed consistent states simplify export, import, and backup; most cloud offerings provide continuous mirroring for quick restoration |
The drawback: impedance mismatch
Impedance mismatch occurs when the relational model (tables) and in-memory data structures (objects) are not aligned. Application code uses complex, nested structures; databases use flat tables. Bridging them requires a translation layer that maps objects to rows, adding complexity and overhead.
A single aggregated value in the application's view is composed of several rows across several tables. Every read reassembles it; every write decomposes it.
This is the specific pain that document databases were built to remove — and it is the honest reason to reach for one, far more than "NoSQL is faster."
Key takeaway
Relational databases give you ACID and normalization: correctness guarantees that would otherwise live in your application code. You give them up deliberately, for a stated reason — not by default.
Interview signal by level
| Level | What a strong answer sounds like |
|---|---|
| L4 | "Use SQL for structured data — it supports ACID." |
| L5 | Names what ACID buys: "we need atomicity and isolation for payments, so concurrent transfers can't interleave into a wrong balance." |
| Staff+ | Prices the trade: "ACID is an abstraction that saves me writing concurrency control by hand, and normalization means data can't disagree with itself. If I move off it I'm taking those on in application code — so I'd only do that where the access pattern genuinely demands it." |
Next: the four families that relax those guarantees, and what each buys instead.