Definition
- SQL (relational) databases – Store structured data in tables with a fixed schema, enforce relationships and constraints with foreign keys, and provide ACID transactions and expressive joins.
- NoSQL databases – A family of non-relational stores (document, key-value, wide-column, graph) that relax fixed schemas and often strong consistency in exchange for horizontal scalability and flexible data models.
- Decision criteria – The choice is driven by the data's structure, the consistency requirement, the dominant access pattern, and the scale need, not by fashion or trend.
- Polyglot persistence – Using SQL for the transactional core and NoSQL for parts that need flexibility or scale is a common and recommended architecture.
A SQL database is like the National Bank of India's formal double-entry ledger: rigid rules, every entry balances, and you can cross-reference accounts, transactions, and customers reliably. A NoSQL database is like a flexible filing system where each customer folder can hold whatever papers that customer needs, no two folders must match, and you can spread the folders across many rooms to serve millions of people quickly. You use the strict ledger for money that must balance and the flexible folders for activity feeds, sessions, or product catalogs where flexibility and scale matter more than joins.
Understanding the Concepts
What is a SQL (relational) database?
A SQL database stores data in tables with a fixed schema defined up front: columns, types, and constraints. The database rejects data that does not fit the schema, which enforces data integrity and makes the structure self-documenting. Relationships between tables are modeled with foreign keys, and you reassemble related data using joins. SQL databases provide ACID transactions, so a transfer that debits one account and credits another either fully happens or fully rolls back.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
public class AccountService {
private final DataSource dataSource;
public AccountService(DataSource dataSource) {
this.dataSource = dataSource;
}
public BigDecimal getBalance(String accountId) throws SQLException {
String sql = "SELECT balance FROM accounts WHERE account_id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, accountId);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getBigDecimal("balance");
}
throw new IllegalArgumentException("Account not found: " + accountId);
}
}
}
}
The code above shows a standard JDBC query against a SQL database. The schema guarantees that balance is always a numeric column and account_id is always present.
What is a NoSQL database?
NoSQL databases are non-relational stores that relax the fixed schema. A document store like MongoDB lets each document have different fields, so you can evolve the shape without a migration. Data is typically denormalized: you store it the way you read it, often embedding related data in one document so a single read returns everything.
package com.bank.service;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;
import static com.mongodb.client.model.Filters.eq;
public class CustomerProfileService {
private final MongoCollection<Document> profiles;
public CustomerProfileService(MongoDatabase db) {
this.profiles = db.getCollection("customer_profiles");
}
public Document getProfile(String customerId) {
Document profile = profiles.find(eq("customerId", customerId)).first();
if (profile == null) {
throw new IllegalArgumentException("Profile not found: " + customerId);
}
// Profile may contain nested addresses, preferences, activity log
// Each customer's document can have different fields
return profile;
}
}
The MongoDB document can hold nested addresses, preferences, and an activity log all in one read, without joins. Different customers can have different fields.
How do SQL and NoSQL differ?
The two families differ along several clear axes.
| Axis | SQL (Relational) | NoSQL |
|------|-------------------|-------|
| Schema | Fixed, enforced at write time | Flexible, schema-on-read |
| Data model | Normalized tables, joins | Denormalized documents, embeddings |
| Consistency | ACID transactions | Often eventual consistency (tunable) |
| Scaling | Vertical, read replicas | Native horizontal sharding |
| Query language | Rich SQL with joins, aggregations | Varies by type, optimized for known patterns |
| Schema changes | Requires migration | Add fields by writing them |
package com.bank.model;
import java.math.BigDecimal;
import java.time.LocalDateTime;
// SQL model: normalized, separate tables for account and transaction
public class Transaction {
private long transactionId; // PK
private String accountId; // FK to accounts table
private BigDecimal amount;
private String type; // DEBIT or CREDIT
private LocalDateTime createdAt;
// In SQL: SELECT t.*, a.holder_name
// FROM transactions t
// JOIN accounts a ON t.account_id = a.account_id
// WHERE t.account_id = 'SAV2001'
}
In SQL, a transaction references its account via a foreign key, and you join to get the holder name. In NoSQL (document), you embed the holder name inside the transaction document so one read returns everything.
What are the types of NoSQL databases?
NoSQL is not one thing. There are four families, each suited to a different access pattern.
- Key-value stores (Redis, DynamoDB) are the simplest and fastest for lookups by key. They are ideal for caching, sessions, and hot balance lookups.
- Document stores (MongoDB) hold JSON-like documents, great for flexible, hierarchical entities like customer profiles, product catalogs, or activity feeds.
- Wide-column stores (Cassandra) handle massive write volumes and time-series data at scale, with a column-family model optimized for append-heavy workloads.
- Graph databases (Neo4j) excel at relationship-heavy queries like fraud-ring detection, social connections, or recommendation engines.
package com.bank.cache;
import redis.clients.jedis.Jedis;
import java.math.BigDecimal;
public class BalanceCacheService {
private final Jedis jedis;
public BalanceCacheService(Jedis jedis) {
this.jedis = jedis;
}
public void cacheBalance(String accountId, BigDecimal balance) {
// Key-value: simple set/get by account ID
jedis.setex("balance:" + accountId, 60, balance.toPlainString());
}
public BigDecimal getCachedBalance(String accountId) {
String cached = jedis.get("balance:" + accountId);
return cached != null ? new BigDecimal(cached) : null;
}
}
Redis stores and retrieves the balance by key in microseconds. This is a classic key-value pattern for hot banking data.
When should you use SQL?
Use SQL when the data is highly relational and needs multi-entity transactions with strong consistency. At the National Bank of India, the core money ledger must be on a relational database because transfers must debit and credit atomically, balances must always reconcile, and reporting needs joins across accounts, customers, and transactions.
-- Atomic transfer: both succeed or both roll back
BEGIN;
UPDATE accounts SET balance = balance - 50000.00 WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 50000.00 WHERE account_id = 'SAV2002';
COMMIT;
If the second UPDATE fails, the entire transaction rolls back and no money disappears. This ACID guarantee is non-negotiable for financial data.
When should you use NoSQL?
Use NoSQL when the schema is fluid, the data is semi-structured, or the workload demands native horizontal scaling. At the National Bank of India, customer profiles vary per customer and change shape every release, the mobile activity feed is read-heavy and does not need joins, and session data needs sub-millisecond lookups at massive scale.
package com.bank.service;
import com.mongodb.client.MongoCollection;
import org.bson.Document;
import java.util.Arrays;
public class ActivityFeedService {
private final MongoCollection<Document> feeds;
public ActivityFeedService(MongoCollection<Document> feeds) {
this.feeds = feeds;
}
public void logActivity(String customerId, String action, String detail) {
Document entry = new Document("customerId", customerId)
.append("action", action)
.append("detail", detail)
.append("timestamp", System.currentTimeMillis())
.append("tags", Arrays.asList("mobile", "banking"));
feeds.insertOne(entry);
// No migration needed if we add a new field next sprint
}
}
Adding a new field like tags requires no migration. Each document can have its own shape.
What about ACID vs BASE and the CAP theorem?
SQL databases follow the ACID model: Atomicity, Consistency, Isolation, Durability. Every transaction is all-or-nothing. Many NoSQL systems follow the BASE model: Basically Available, Soft state, Eventually consistent. They prioritize availability and partition tolerance over immediate consistency.
The CAP theorem states that a distributed system can guarantee at most two of three properties: Consistency, Availability, and Partition tolerance. Since network partitions are inevitable, the real choice is between consistency and availability during a partition. SQL databases (single-node or synchronous replication) choose consistency. Many NoSQL systems choose availability, accepting that different nodes may briefly disagree.
package com.bank.service;
import java.math.BigDecimal;
// ACID: Transfer must be atomic
public class TransferService {
// SQL: both updates in one transaction
// If credit fails, debit is rolled back
public void transfer(String fromAccount, String toAccount, BigDecimal amount) {
// BEGIN TRANSACTION
// UPDATE accounts SET balance = balance - amount WHERE account_id = fromAccount
// UPDATE accounts SET balance = balance + amount WHERE account_id = toAccount
// COMMIT
// If any step fails: ROLLBACK
}
}
// BASE: Activity feed can tolerate brief inconsistency
// Replica in Mumbai may show a feed entry a few milliseconds
// after the replica in Delhi, and that is acceptable
What are common mistakes when choosing?
The most common mistake is choosing NoSQL because it is trendy and then fighting its limitations when the data is actually relational.
// WRONG: Using MongoDB for a banking ledger
// You lose ACID transactions, foreign keys, and joins
// A transfer across two documents is NOT atomic by default
// WRONG: Using MySQL for a high-throughput activity feed
// You fight schema migrations every sprint
// Horizontal scaling requires painful application-level sharding
// CORRECT: Polyglot persistence
// MySQL/PostgreSQL for the core ledger (ACID, joins, constraints)
// MongoDB for customer profiles and feeds (flexible, scalable)
// Redis for sessions and hot balances (sub-millisecond lookups)
Another mistake is over-normalizing in NoSQL. If you split a MongoDB document into many collections and join them in the application, you have recreated a relational model without the relational guarantees.
How do you implement polyglot persistence in Java?
Pankaj, the architect at the National Bank of India, designed the system with SQL for the transactional core and NoSQL for flexible, read-heavy data. Rajesh implements it with separate service classes.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mongodb.client.MongoDatabase;
import redis.clients.jedis.Jedis;
public class BankingFacade {
private final DataSource sqlDataSource; // PostgreSQL for ledger
private final MongoDatabase mongoDb; // MongoDB for profiles
private final Jedis redis; // Redis for cache
public BankingFacade(DataSource sqlDataSource, MongoDatabase mongoDb, Jedis redis) {
this.sqlDataSource = sqlDataSource;
this.mongoDb = mongoDb;
this.redis = redis;
}
public void transfer(String from, String to, BigDecimal amount) throws SQLException {
// ACID transaction on PostgreSQL
try (Connection conn = sqlDataSource.getConnection()) {
conn.setAutoCommit(false);
try {
debit(conn, from, amount);
credit(conn, to, amount);
conn.commit();
// Invalidate cached balances
redis.del("balance:" + from, "balance:" + to);
} catch (SQLException e) {
conn.rollback();
throw e;
}
}
}
private void debit(Connection conn, String accountId, BigDecimal amount)
throws SQLException {
String sql = "UPDATE accounts SET balance = balance - ? WHERE account_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setBigDecimal(1, amount);
ps.setString(2, accountId);
ps.executeUpdate();
}
}
private void credit(Connection conn, String accountId, BigDecimal amount)
throws SQLException {
String sql = "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setBigDecimal(1, amount);
ps.setString(2, accountId);
ps.executeUpdate();
}
}
}
PostgreSQL handles the atomic transfer, Redis caches are invalidated after commit, and MongoDB (not shown in the transfer) serves profiles and feeds.
What should a senior engineer know?
A senior engineer should know that the decision is not binary. Most production systems use polyglot persistence. The key considerations are:
- Data model fit – If data is relational with many foreign key relationships, SQL is natural. If data is hierarchical or varies per record, a document store fits.
- Consistency requirement – Money requires ACID. Activity feeds tolerate eventual consistency.
- Access pattern – If queries are ad hoc and analytical, SQL's join engine is essential. If queries are known and narrow, NoSQL's optimized paths are faster.
- Scale – If write throughput must exceed a single node from day one, a natively distributed NoSQL store may fit. If reads are the bottleneck, SQL with replicas and a cache may suffice.
- Operational maturity – SQL databases have decades of tooling, monitoring, and expertise. NoSQL systems are newer and require distributed-systems knowledge.
Decision checklist: 1. Is the data highly relational with foreign keys? → SQL 2. Must transactions span multiple entities atomically? → SQL 3. Does the schema change every sprint? → NoSQL (document) 4. Is the access pattern simple key lookups at scale? → NoSQL (key-value) 5. Are relationship traversals the dominant query? → NoSQL (graph) 6. Is write throughput beyond a single node? → NoSQL (wide-column) 7. Do you need rich ad hoc analytics with joins? → SQL 8. Can different parts use different stores? → Polyglot
Visual Overview
┌─────────────────────────────────────────────────────────────┐ │ SQL vs NoSQL Decision │ ├─────────────────────────────────────────────────────────────┤ │ │ │ ┌─────────────────────┐ ┌─────────────────────────┐ │ │ │ SQL (Relational) │ │ NoSQL (Flexible) │ │ │ ├─────────────────────┤ ├─────────────────────────┤ │ │ │ Fixed Schema │ │ Schema-less / Flexible │ │ │ │ Tables + Rows │ │ Documents / KV / Graph │ │ │ │ ACID Transactions │ │ BASE / Eventual │ │ │ │ Joins + FK │ │ Embedded / Denormalized │ │ │ │ Vertical Scale │ │ Horizontal Scale │ │ │ │ Rich SQL Queries │ │ Pattern-optimized │ │ │ └────────┬────────────┘ └────────┬────────────────┘ │ │ │ │ │ │ ▼ ▼ │ │ ┌─────────────────┐ ┌──────────────────────────┐ │ │ │ Use For: │ │ Use For: │ │ │ │ - Ledger │ │ - Profiles (Document) │ │ │ │ - Transfers │ │ - Sessions (Key-Value) │ │ │ │ - Accounts │ │ - Events (Wide-Column) │ │ │ │ - Reports │ │ - Fraud Ring (Graph) │ │ │ └─────────────────┘ └──────────────────────────┘ │ │ │ │ ┌───────────────────────────┐ │ │ │ Polyglot Persistence │ │ │ │ SQL for core ledger │ │ │ │ + NoSQL for flexible │ │ │ │ read-heavy data │ │ │ └───────────────────────────┘ │ └─────────────────────────────────────────────────────────────┘
Key Takeaway
Deciding between SQL and NoSQL comes down to four questions: how relational is the data, how strong must the consistency be, what is the dominant access pattern, and how far must the system scale. SQL relational databases store structured data in tables with a fixed schema, enforce relationships with foreign keys, and provide ACID transactions with expressive joins. This makes them the right choice when data is highly relational and strong consistency is essential, which is exactly the case for a banking ledger where transfers must be atomic and balances must always reconcile.
NoSQL databases relax the fixed schema and often strong consistency in exchange for horizontal scalability, flexible data models, and very high throughput for specific access patterns. The four families each solve a different problem: document stores like MongoDB for flexible hierarchical entities, key-value stores like Redis for sub-millisecond lookups, wide-column stores like Cassandra for massive write throughput, and graph databases like Neo4j for relationship-heavy queries like fraud detection.
The differences line up along clear axes. Schema: SQL enforces structure at write time while NoSQL allows schema-on-read flexibility. Data model: SQL normalizes and joins while NoSQL denormalizes and embeds. Consistency: SQL provides ACID while NoSQL often defaults to eventual consistency, framed by the CAP theorem. Scaling: SQL scales vertically with read replicas while NoSQL scales horizontally with native sharding.
At the National Bank of India, Pankaj kept the core money ledger on PostgreSQL because transfers must be atomic and balances must always reconcile. For the mobile activity feed and per-user preferences, which varied per customer and changed shape every release, Rajesh used MongoDB so the team could evolve the document without migrations. Redis handled sessions and hot balance lookups for sub-millisecond response times. This polyglot approach uses each tool where it fits.
Remember one sentence: choose SQL for relational, strongly-consistent, transactional data with joins, and choose NoSQL for flexible schemas, specific high-throughput access patterns, and horizontal scale.
---