SQL vs NoSQL — when to use which in system design

The Real Difference

The SQL vs NoSQL decision isn't about which is better — it's about which trade-offs fit your requirements.

Choose SQL When

• Data is relational with clear foreign key relationships
• You need ACID transactions (financial systems, inventory)
• Your schema is stable and well-defined
• You need complex queries with joins

Choose NoSQL When

• You need horizontal write scaling beyond a single master
• Data structure varies across records (user profiles with different fields)
• You're storing time-series, documents, or graph data
• Read patterns are known and simple (key lookups)

NoSQL Categories

• Key-Value: Redis, DynamoDB — cache, sessions, simple lookups
• Document: MongoDB, Firestore — user profiles, catalogs
• Column-Family: Cassandra, HBase — time-series, write-heavy, IoT
• Graph: Neo4j — social graphs, recommendation engines

// SQL: get user's recent orders with product info
SELECT u.name, o.total, p.name FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
WHERE u.id = 123 ORDER BY o.created_at DESC LIMIT 10;

// NoSQL equivalent: pre-joined document (MongoDB)
db.orders.find({ userId: 123 }).sort({ createdAt: -1 }).limit(10);