Choosing the database engine is one of the architectural decisions with the greatest long-term impact on an enterprise system. It shapes the data model, the scaling strategy, operating costs and even how fast the team can ship new features. The debate between SQL and NoSQL is not settled by picking a side: it is settled by understanding what guarantees each family offers and what the workload actually demands. In this guide we walk through relational modelling, normalisation, consistency patterns and the practical criteria for getting it right in production.
The relational model: the SQL standard and ACID guarantees
Relational databases organise information into tables (relations) made up of rows and columns with defined types. Their language, SQL, is standardised in ISO/IEC 9075, which guarantees a declarative syntax that is portable across engines such as PostgreSQL, MySQL/MariaDB, Oracle Database or SQL Server. The defining strength of the relational model is the ACID properties: Atomicity (a transaction is applied in full or not at all), Consistency (integrity constraints are always met), Isolation (concurrent transactions do not interfere with one another) and Durability (committed data survives a failure).
Isolation is realised through levels defined by the standard: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Each level is a trade-off between concurrency and the anomalies it tolerates (dirty, non-repeatable or phantom reads). PostgreSQL, for example, uses multiversion concurrency control (MVCC) so that reads do not block writes. Understanding these levels is what separates a system that scales from one that degrades under load with locks and deadlocks.
Normalisation: from Codd's theory to practice
Normalisation, formalised by Edgar F. Codd, aims to eliminate redundancy and update anomalies by decomposing relations into successive normal forms:
- 1NF (First Normal Form): each cell holds an atomic value; no lists inside a single field.
- 2NF: building on 1NF, every non-key attribute depends on the entire primary key (relevant with composite keys).
- 3NF: transitive dependencies are removed; a non-key attribute must not depend on another non-key attribute.
- BCNF (Boyce-Codd): a stricter version of 3NF for cases with several overlapping candidate keys.
In practice, most transactional (OLTP) schemas are designed in 3NF or BCNF to guarantee integrity. In analytics (OLAP) and reporting, however, controlled denormalisation is applied — star or snowflake schemas — because bulk reads are penalised by repeated JOINs. The operating rule: normalise to write, denormalise with judgement to read, and always measure with real data before optimising.
NoSQL: when the relational model gets in the way
NoSQL is not a single technology but a family of engines that give up part of the relational model to gain schema flexibility or horizontal scaling. They fall into four main categories:
- Document stores (MongoDB, Couchbase): store nested JSON/BSON documents. Ideal when the data structure varies between records or evolves quickly.
- Key-value (Redis, DynamoDB): access by key with microsecond latency; perfect for caching, sessions and counters.
- Wide-column (Apache Cassandra, HBase): distributed bulk writes and time series at large scale.
- Graph (Neo4j, Amazon Neptune): relationships as first-class citizens; recommendation, fraud detection and social networks.
The theoretical foundation that justifies these trade-offs is the CAP theorem (Eric Brewer): in the face of a network partition (P), a distributed system must choose between Consistency (C) and Availability (A). Many NoSQL systems adopt the BASE model (Basically Available, Soft state, Eventually consistent), prioritising availability and accepting eventual consistency. This is not "worse" than ACID: it is a deliberate decision for workloads where a replica that is a few milliseconds out of date is tolerable, but a service outage is not.
Scalability: vertical, horizontal and sharding
Vertical scaling (adding CPU, RAM or disk to one server) is simple but hits a physical and cost ceiling. Horizontal scaling (spreading the load across nodes) is NoSQL's natural path and, increasingly, SQL's too through partitioning. The key techniques:
- Read replicas: copies that absorb read-only queries, offloading the write primary.
- Sharding: horizontal partitioning of the data by a key (for example,
customer_id % Nor by geographical range). Each shard lives on a different node. - NewSQL distributed databases (CockroachDB, Google Spanner, YugabyteDB): combine horizontal scaling with ACID guarantees and standard SQL, closing the historic gap.
The most expensive mistake in sharding is choosing the wrong partition key: a key with low cardinality or skew creates "hot spots" that saturate one node while the rest sit idle. A good key spreads the load evenly and groups together the data that is queried together, avoiding queries that have to fan out across every node in the cluster.
Indexing and query optimisation
An index is the structure that avoids scanning the whole table to find a row. The most common is the B+ tree (B-tree), efficient for equalities and ranges; hash indexes only serve exact equalities; PostgreSQL's GIN/GiST indexes cover JSON, full text and geospatial data; and inverted indexes underpin full-text search. Each index speeds up certain reads but has a cost: it slows down writes (it has to be kept up to date) and consumes memory and disk. The discipline is to index the columns that appear in frequent WHERE, JOIN and ORDER BY clauses, and to drop the indexes nobody uses.
The fundamental diagnostic tool is the execution plan, obtained with EXPLAIN ANALYZE in PostgreSQL or its equivalents in other engines. It reveals whether the planner does a costly sequential scan or uses an index, whether the JOIN order is optimal and where the time is concentrated. Anti-patterns to avoid: applying functions to the indexed column in the WHERE clause (it disables the index), using SELECT * when only three columns are needed, and the classic N+1 problem, in which an application fires one query per row of a result instead of a single query with a JOIN. Measuring before optimising and measuring again afterwards is not optional: intuitions about database performance are usually wrong.
Comparison table: SQL versus NoSQL
| Criterion | SQL (relational) | NoSQL |
|---|---|---|
| Schema | Rigid and predefined | Flexible or schema-less |
| Consistency | Strong (ACID) | Eventual / tunable (BASE) |
| Scaling | Vertical and partitioning | Natively horizontal |
| Complex queries | Powerful JOINs and aggregations | Limited; logic in the application |
| Ideal use case | Transactions, ERP, finance | Catalogues, IoT, caching, graphs |
Implementation: steps for getting it right
- Characterise the workload: read/write ratio, volume, target latency and access patterns.
- Define the non-negotiable guarantees: do you need multi-table transactions? Can you tolerate eventual consistency?
- Model the queries first, not the tables in NoSQL: access dictates the design.
- Index with intent: each index speeds up reads but penalises writes and takes up memory.
- Plan for backups and recovery: define RPO and RTO, and test restores, not just backups.
- Protect personal data: encryption at rest and in transit, and minimisation in line with the GDPR.
Common mistakes that come back to bite
- Using NoSQL "because it is modern" for highly relational data: you end up reimplementing JOINs in the application.
- Denormalising prematurely in SQL without measuring, generating update anomalies.
- Ignoring the isolation level and suffering race conditions in production.
- Defining no TTL or retention policy and letting tables grow unchecked.
- Treating the database as a black box and not monitoring slow queries with
EXPLAIN.
Polyglot persistence: the best of both worlds
Modern architecture is rarely monolithic in its data layer. Polyglot persistence means using the right engine for each subdomain: PostgreSQL for orders and invoicing, Redis for sessions and caching, Elasticsearch for full-text search and Cassandra for telemetry. The challenge then shifts to coherence across stores, which is solved with patterns such as event sourcing, CDC (Change Data Capture) or the Outbox pattern to propagate changes reliably.
Security, compliance and data governance
An enterprise database is, almost always, the repository of the organisation's most sensitive assets, and therefore the priority target of any attack. Protection is built in layers. With encryption, a distinction is drawn between data at rest (Transparent Data Encryption at the file or tablespace level) and data in transit (TLS between the application and the engine). With access control, the principle of least privilege is realised through roles, granular permissions by table or column and, in advanced engines, row-level security (RLS) that filters which records each user sees. Auditing records who accessed what and when, a common requirement in regulated sectors.
From the GDPR perspective, the database is where principles such as minimisation (not storing more personal data than necessary), storage limitation (retention and automatic deletion policies) and data subject rights such as erasure take concrete shape — erasure requiring the ability to reliably locate and delete every record relating to a person. Pseudonymisation and anonymisation are recommended techniques for reducing risk, especially in development and analytics environments that should not operate with real personal data. Data governance — catalogue, lineage, classification by sensitivity and a responsible owner — stops being a luxury and becomes a requirement of both compliance and quality.
Frequently asked questions
Does NoSQL replace relational databases?
No. They are complementary tools. Relational databases remain irreplaceable for financial transactions, ERP and any domain where referential integrity is non-negotiable. NoSQL shines in schema flexibility and horizontal scaling.
What does it mean for a system to be "eventually consistent"?
It means that after a write, the replicas converge to the same value within a short window, but during that interval a read may return slightly stale data. This is acceptable in catalogues or social networks, not in bank balances.
When is it worth denormalising a SQL schema?
When read queries dominate, repeated JOINs are the measured bottleneck and the write frequency is low. Always with real performance data on the table, never on intuition.
What is NewSQL?
A generation of engines (CockroachDB, Spanner, YugabyteDB) that offers standard SQL and ACID guarantees with distributed horizontal scaling, eliminating the classic trade-off between strong consistency and scale.
Conclusion
There is no universally superior database; there is the database that is right for a specific workload. The correct question is not "SQL or NoSQL?" but "what consistency guarantees, what scaling pattern and what access model does this domain demand?". A billing system calls for ACID and a schema normalised to 3NF; a fast-changing product catalogue welcomes a document engine; telemetry of millions of events per minute calls for a distributed wide-column store. At Summum Systems we design each persistence layer starting from the real access patterns and the integrity and compliance requirements, avoiding both over-engineering and decisions driven by fashion. Getting this right is not noticeable in the first month: it is noticeable when the system scales and the architecture holds up without rewrites.