Databases explained: relational vs NoSQL and when each fits

Every app that remembers anything (customers, orders, messages, bookings) stores it in a database. The choice of database shapes how easily you can run reports, how the product behaves when two people act at once, and what it costs to grow. It is also a choice that is expensive to reverse once real data is in place.

Developers often frame it as “SQL or NoSQL”. This guide explains what those labels mean, the trade-offs behind them and how to judge which fits your product, so you can follow the discussion and ask useful questions.

Relational databases

A relational database stores data in tables, like linked spreadsheets with strict rules. A customers table has one row per customer and fixed columns such as name and email. An orders table has one row per order, with a column that points to the customer who placed it. You query and change the data with SQL (Structured Query Language), and the database joins tables together to answer questions such as “total spend by customer last quarter”.

Microsoft’s architecture guide on relational versus NoSQL data describes relational databases as having “a fixed schema”, using SQL, and supporting “ACID guarantees”. Well-known examples include PostgreSQL, MySQL, SQL Server and Oracle. PostgreSQL’s about page gives a sense of the maturity: its origins go back to 1986 at the University of California at Berkeley, and it has been ACID-compliant since 2001.

What ACID means

ACID is a set of promises about transactions, groups of changes that should succeed or fail together. As summarised in the Wikipedia entry on ACID:

• Atomicity: a transaction is all or nothing. If a payment is recorded but stock can’t be reduced, neither change is kept.
• Consistency: a transaction can only move the data from one valid state to another, respecting the rules you set.
• Isolation: transactions running at the same time don’t see each other’s half-finished work.
• Durability: once a change is committed, it survives a crash or power cut.

These promises matter most where money, stock or bookings are involved. Two customers trying to buy the last ticket at the same moment should not both succeed.

NoSQL databases

NoSQL is a loose label for databases that don’t use the relational table model. AWS’s overview of NoSQL databases calls them “purpose-built databases” designed “for specific data models”, with flexible schemas that can change as the application changes. The main types are:

AWS also lists in-memory stores, built for microsecond responses, and search databases, built to find content quickly.

The Microsoft guide explains the trade. NoSQL databases “scale out horizontally, often across commodity servers”, which suits very high volumes. In exchange, they “typically don’t provide ACID guarantees beyond the scope of a single database partition”. Relational databases are “typically provisioned to a single server and scale vertically” by adding more power to that machine, although read copies and partitioning can extend them.

When each fits

The Microsoft guide offers a practical comparison, summarised here:

For most early-stage products, the left-hand column describes reality. Customers, orders, subscriptions and invoices are structured and linked, finance and operations teams want reports, and volumes are modest. A relational database is a sensible default.

The line has also blurred. PostgreSQL supports JSON documents and key-value data alongside tables, and AWS notes that combining both types of database in one application is common. Many teams start relational and add a specialised store later for a specific job, such as caching or search.

A worked example

An example (illustration only). A start-up is building a marketplace where tutors list lessons and students book and pay.

• Users, tutors, lessons, bookings and payments are tightly linked, and a booking must never be confirmed without a payment. That calls for transactions with ACID guarantees, so the core data goes in a relational database.
• Each tutor’s profile has optional, varied details: qualifications, videos, languages, free-text sections. These can sit in a JSON column in the same relational database, keeping things simple.
• Search across thousands of lessons by subject, price and availability might later need a dedicated search engine fed from the main database.
• Logged-in sessions can live in a key-value store if speed becomes an issue.

The result is one main relational database with room to add specialised stores when a real need appears, rather than four databases on day one.

Questions to ask your developers

1. What data model are we using, and why does it fit our data?
2. Which actions need to happen as a single transaction, such as payment plus booking?
3. How will finance and operations get reports, and will that slow the live app?
4. How is the database backed up, and how quickly can it be restored?
5. Is it a managed service, or will someone need to patch and maintain servers?
6. How hard would it be to move to a different database later?

Common mistakes

• Choosing NoSQL because it sounds scalable. Many products never reach the volumes where that matters, and they lose easy reporting and transactions along the way.
• Using a “flexible schema” as a reason not to plan. Data without agreed rules becomes inconsistent, and someone has to clean it up later.
• Running reports on the live database. Heavy queries can slow the app for customers. A read copy or a separate reporting store avoids this.
• Self-managing databases without the skills. A managed cloud database handles patching, backups and failover for a fee, so you don’t need those skills in-house.
• Splitting data across many databases too early, which makes every feature harder to build.

Relational databases remain the safe starting point for most business software. Choose a NoSQL store when you have a specific workload, such as massive event volumes, highly connected data or ultra-fast lookups, that a relational database handles badly, and be clear about what you give up in exchange.