The Core Conflict: SQL vs NoSQL
In a system design interview, choosing the right database is half the battle. This choice determines your consistency guarantees, scaling limits, and development speed.
1. SQL (Relational Databases)
Examples: PostgreSQL, MySQL, Oracle.
- Data Model: Rigid tables with rows and columns. Uses schemas.
- ACID: Atomic, Consistent, Isolated, Durable. High focus on data integrity.
- Scaling: Optimized for Vertical Scaling. Horizontal scaling (sharding) is complex.
- Best For: Financial systems, complex joins, structured data.
2. NoSQL (Non-Relational)
Examples: Cassandra (Columnar), MongoDB (Document), Redis (Key-Value), Neo4j (Graph).
- Data Model: Flexible. JSON-like documents, key-value pairs, or wide-column rows.
- BASE: Basically Available, Soft-state, Eventually consistent.
- Scaling: Built for Horizontal Scaling from day one.
- Best For: Real-time analytics, social media feeds, massive unstructured data.
3. Decision Matrix: Which one to pick?
| Requirement | Choose SQL | Choose NoSQL |
|---|---|---|
| Joins | Yes, optimized | No (use denormalization) |
| Schema | Pre-defined, rigid | Dynamic, flexible |
| Consistency | Strong (ACID) | Eventual (BASE) |
| Scalability | Vertical | Horizontal |
4. Real-World Analogy: The Library
- SQL is a strictly organized library where every book is in a specific category, shelf, and position. It's easy to find relationships but hard to move the library.
- NoSQL is a pile of boxes where you just throw books in. It's incredibly fast to add more boxes, but finding specific relationships between books in different boxes is harder.
Final Takeaway
Don't pick NoSQL just because it's "cool." Pick it because you need massive scale or a flexible schema. Pick SQL because you need consistency and complex queries.