05_Database_Types__Sql_Vs._Nosql_

Database Types (SQL vs. NoSQL)

Difficulty: Foundational
Generated on: 2025-07-13 02:51:00
Category: System Design Cheatsheet

---

Database Types: SQL vs. NoSQL - System Design Cheatsheet (Foundational)

1. Core Concept

What is it? Choosing the right database is crucial for application performance, scalability, and data integrity. The primary choice is between SQL (Relational) and NoSQL (Non-Relational) databases.

Why is it important? The wrong database choice can lead to performance bottlenecks, data inconsistencies, and increased development complexity. Choosing the right database aligns with the application’s data model, access patterns, and scalability requirements.

2. Key Principles

Feature	SQL (Relational)	NoSQL (Non-Relational)
Data Model	Tables with rows and columns; Schema is defined.	Documents, Key-Value Pairs, Graphs, Column Families; Flexible schema.
Schema	Strict Schema; enforced at write time.	Schema-less or Schema-on-Read; more flexible.
ACID	ACID properties (Atomicity, Consistency, Isolation, Durability)	BASE properties (Basically Available, Soft state, Eventually consistent)
Querying	SQL (Structured Query Language)	Varies depending on the NoSQL database type.
Scalability	Vertical scaling (scaling up a single server); Horizontal scaling (sharding) can be complex.	Horizontal scaling (adding more machines) is generally easier.
Relationships	Joins to relate data across tables.	Denormalization, Embedding, or application-level joins.

3. Diagrams

SQL (Relational)

graph LR
    A[Users Table] --> B(Orders Table);
    B --> C(Products Table);
    style A fill:#f9f,stroke:#333,stroke-width:2px
    style B fill:#f9f,stroke:#333,stroke-width:2px
    style C fill:#f9f,stroke:#333,stroke-width:2px
    subgraph SQL Database
        A
        B
        C
    end

NoSQL (Document Database)

graph LR
    A[User Document] --> B(Orders Array in User Document);
    style A fill:#ccf,stroke:#333,stroke-width:2px
    style B fill:#ccf,stroke:#333,stroke-width:2px
    subgraph NoSQL Database (MongoDB)
        A
    end

NoSQL (Key-Value Store)

graph LR
    A[Key: UserID, Value: User Object]
    style A fill:#ccf,stroke:#333,stroke-width:2px
    subgraph NoSQL Database (Redis)
        A
    end

4. Use Cases

Use Case	SQL Suitability	NoSQL Suitability
Financial Transactions	Excellent. ACID properties are crucial for data integrity.	Not ideal unless consistency is less critical.
E-commerce Product Catalog	Good. Can be modeled with relational tables.	Good. Document databases can store flexible product attributes.
Social Media Feed	Less ideal. Relationships can be complex to query.	Good. Graph databases excel at relationship management.
Session Management (Redis)	Not ideal. Overkill for simple key-value pairs.	Excellent. Key-value stores provide fast access.
Logging and Analytics	Less ideal. High volume, unstructured data.	Excellent. Column-family databases handle large datasets well.
Content Management Systems (CMS)	Good. Well-defined structure and relationships.	Good. Document databases offer flexibility for content types.
Gaming Leaderboards (Redis)	Not Ideal. Overkill for simple sorted sets.	Excellent. Key-value stores provide fast access and sorted sets for ranking.

5. Trade-offs

Factor	SQL	NoSQL
Complexity	More complex to scale horizontally; Schema changes can be difficult.	Easier to scale horizontally; Schema evolution is more flexible.
Consistency	Strong consistency (ACID)	Eventual consistency (BASE)
Flexibility	Less flexible schema.	More flexible schema.
Querying	Standardized SQL language.	Varies by database type.
Development Speed	Can be slower upfront due to schema design.	Can be faster upfront due to schema-less nature.

6. Scalability & Performance

SQL:

• Vertical Scaling: Adding more resources (CPU, RAM, storage) to a single server. Has limits.
• Horizontal Scaling (Sharding): Dividing the database across multiple servers. Complex to implement and manage. Requires careful shard key selection.
• Read Replicas: Creating read-only copies of the database to handle read traffic.

NoSQL:

• Horizontal Scaling: Easier to add more servers to the cluster. Data is automatically distributed.
• Performance: Often optimized for specific use cases (e.g., fast reads in key-value stores).
• Consistency Trade-offs: Eventual consistency can lead to temporary data inconsistencies.

Example: Scaling an E-commerce Platform

• SQL (Orders, Users): Sharding the Orders table by user_id to distribute the load across multiple database servers. Using read replicas to handle read traffic from the product catalog.
• NoSQL (Product Catalog): Using a document database like MongoDB to store product data. Horizontally scaling the MongoDB cluster to handle increasing product catalog size and traffic.

7. Real-world Examples

• Facebook: Uses MySQL (SQL) for core data like user profiles, but also uses Cassandra (NoSQL) for high-volume data like messages and activity logs. They also use Memcached (Key-Value) for caching.
• Google: Uses Spanner (SQL) for globally distributed, strongly consistent data. Uses Bigtable (NoSQL) for large-scale data processing and analytics.
• Amazon: Uses DynamoDB (NoSQL) for its e-commerce platform, especially during peak traffic periods like Black Friday. Uses RDS (SQL) for many other services.
• Netflix: Uses Cassandra (NoSQL) for storing and processing viewing history and recommendations.
• Twitter: Uses MySQL (SQL) for user data and relationships, but uses Cassandra (NoSQL) for timelines and tweets.

8. Interview Questions

• “Explain the difference between SQL and NoSQL databases.”
• “What are the ACID and BASE properties? Why are they important?”
• “When would you choose a SQL database over a NoSQL database?”
• “When would you choose a NoSQL database over a SQL database?”
• “How does sharding work in a SQL database?”
• “How does horizontal scaling work in a NoSQL database?”
• “Describe a scenario where you would use a key-value store like Redis.”
• “Describe a scenario where you would use a graph database like Neo4j.”
• “What are the trade-offs between strong consistency and eventual consistency?”
• “How does denormalization help with performance in NoSQL databases?”
• “How would you design a system to store user sessions? What database would you use and why?”
• “How would you design a system to store and analyze clickstream data? What database would you use and why?”