SQL or NoSQL for a New Project

A new project gets a database choice before its hardest query has been written. SQL or NoSQL should be decided from the shape of the data, the consistency promise, and what the team can run reliably.

Describe the Data Shape Before Choosing SQL or NoSQL

List the main entities, relationships, and expected growth. If orders, customers, inventory, and payments must stay connected by keys, a relational model gives useful constraints and joins. If records have highly variable structure and are mostly retrieved as whole documents, a document model can fit. Do not choose SQL or NoSQL from marketing labels before examining actual access patterns.

I sketch a few real questions. Which customer orders are unpaid? Which events belong to one device? Which fields must be unique? The queries expose whether the data wants relationships, nested documents, or both. A single project can use more than one store, but that adds operational cost.

State the Consistency Contract

Decide which actions must be atomic. A payment, inventory reservation, and order state transition can require a clear transaction boundary. Some systems support transactions across multiple records, while others make single-record operations the natural unit. Product capabilities vary, so test the exact database and version rather than relying on a category name.

I ask what the user sees if one step succeeds and another fails. That is the consistency requirement. A vague promise of eventual consistency does not explain who resolves a missing order. Which recovery or compensation action is acceptable? Write it before choosing the store.

Test the Query Patterns on SQL and NoSQL Candidates

List the most important reads and writes with expected filters, joins, and sort order. A relational database can express changing questions well, but a poor schema or index can still perform badly. A document database can serve a known access path efficiently, but a new cross-document report can be awkward. Prototype the hardest query, not the easiest insert.

This T-SQL example shows a relationship query a SQL design handles naturally. The point is the join and filter, not a promise that this query is always faster than another system. Test with representative data.

SELECT o.OrderId, c.CustomerName, o.OrderDate
FROM dbo.Orders AS o
JOIN dbo.Customer AS c ON c.CustomerId = o.CustomerId
WHERE o.OrderStatus = N'Open'
ORDER BY o.OrderDate, o.OrderId;

Consider Schema Change and Validation

A flexible document does not mean the application can accept any shape forever. Required properties, versions, and validation still need ownership. A relational schema makes many rules explicit with types, keys, and constraints. Both approaches need a migration plan when the business changes. Compare how each choice handles old and new application versions together.

I test an invalid record in the prototype. Can it be rejected with a useful error? Can old data be read after a field changes? A database choice should make important mistakes visible. Hiding schema in application code can delay the discussion, not remove it.

Count Operational Work

Backups, restores, access control, monitoring, capacity, upgrades, and incident response belong in the decision. A store can be elegant in development and difficult for a small team to operate at night. Consider hosting model and vendor support, but keep the recovery requirement explicit. A managed service still needs tested exports or restores for the data it owns.

I ask who will be on call and which failure they can diagnose. Team skill is not an embarrassing footnote. It determines whether the chosen system can meet its service promise. A new database technology needs time for training and a recovery drill.

Use a Small Defensible Prototype

Build the most important write transaction and two difficult reads in each serious candidate. Add representative data distribution and a schema change. Measure resource use and recovery steps in your test environment. Avoid a benchmark that times only inserts into an empty collection or table. Record the assumptions and the result so the choice can be reviewed.

The second T-SQL example shows a uniqueness rule that the relational model can enforce. The equivalent requirement must be proved in any alternative design, even if the mechanism differs.

CREATE UNIQUE INDEX UX_Customer_AccountCode
ON dbo.Customer(AccountCode);

Choose SQL or NoSQL and Record the Tradeoff

Write a short decision record: data shape, consistency needs, top queries, expected growth, operations, team skills, and rejected alternatives. State what would cause a revisit. Do not claim one category is universally modern or obsolete. SQL and NoSQL are broad families with different products and features.

I prefer a choice that the team can explain after the first failure. Which assumption is most likely to change? Put a monitoring or review point around it. A defendable decision is one with visible evidence and an operational plan, not a slogan on an architecture slide.

A defendable choice starts with the data’s invariants. List the entities, relationships, required queries and the business rules that must hold during concurrent writes. If a payment and balance update must commit together, describe that guarantee explicitly. If the workload is dominated by independent documents with changing shapes, describe the access patterns and consistency tolerance. I avoid picking a database from an architecture diagram before seeing these cases.

Prototype one representative read and one representative write in each serious candidate. Include indexing, validation, failure handling and operational tasks, not only a happy-path query. A team fluent in one platform can deliver more reliably than a team learning an unfamiliar system under a deadline, so skills and support capacity belong in the decision.

Record the tradeoff. Explain what the chosen system handles well, what it makes harder and which future change would trigger reevaluation. SQL and NoSQL are broad labels; the decision is about a particular product, model and workload.

Do not make the choice a referendum on fashion. A relational database can store flexible documents, and a document database can support structured queries, but their operational tradeoffs still differ. I ask the team to price the cost of migrations, backups, monitoring and hiring as well as query speed. The best choice is the one the team can operate and explain under failure.

Related reading on this blog: SQL Terms vs MongoDB Terms and Learning MongoDB is Easy.

A database category is not an architecture decision, it is a candidate that must meet a written contract.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.