When to use SQL vs NoSQL in HLD - Design Approaches Compared

Design: Database Selection Guidance System

Focus on guiding database choice based on application data and workload characteristics. Does not cover actual database implementation or migration.

Functional Requirements

FR1: Support decision making between SQL and NoSQL databases based on application needs

FR2: Handle scenarios involving structured and unstructured data

FR3: Consider scalability and performance requirements

FR4: Support transactional consistency when needed

FR5: Allow flexible schema changes for evolving data models

Non-Functional Requirements

NFR1: Must support up to 10,000 concurrent users making database queries

NFR2: API response latency should be under 200ms for decision queries

NFR3: Availability target of 99.9% uptime

NFR4: Support data volumes from small (GBs) to large (TBs) scale

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

Relational Database Management System (RDBMS) for SQL

Document, Key-Value, Column, or Graph stores for NoSQL

Caching layer for performance

API layer to abstract database choice

Monitoring and metrics for performance and availability

Design Patterns

Polyglot persistence (using multiple database types)

Caching for read-heavy workloads

Eventual consistency vs strong consistency

Schema evolution and migrations

Sharding and replication for scaling

Reference Architecture

Client
  |
  v
API Layer
  |
  +-------------------+
  |                   |
SQL Database      NoSQL Database
(RDBMS)           (Document/Key-Value/Graph)

Components

API Layer

REST/GraphQL

Receives client requests and routes to appropriate database type based on data and workload

SQL Database

PostgreSQL/MySQL/SQL Server

Stores structured data requiring strong consistency and complex queries

NoSQL Database

MongoDB/Cassandra/Redis/Neo4j

Stores unstructured or semi-structured data with flexible schema and high scalability

Cache Layer

Redis/Memcached

Improves read performance for frequently accessed data

Monitoring

Prometheus/Grafana

Tracks system health, latency, and throughput

Request Flow

1. Client sends request to API Layer

2. API Layer analyzes request data type and workload needs

3. If data is structured and requires transactions, route to SQL Database

4. If data is unstructured or needs flexible schema, route to NoSQL Database

5. Cache Layer serves repeated read requests to reduce latency

6. API Layer returns data or decision guidance to client

Database Schema

SQL Database: Tables with defined columns, primary keys, foreign keys for relationships NoSQL Database: Collections or key-value pairs with flexible or dynamic schemas Relationships: SQL uses joins; NoSQL uses embedded documents or references depending on type

Scaling Discussion

Bottlenecks

SQL databases can become slow with very large datasets or high write loads

NoSQL databases may sacrifice consistency for availability

API Layer can become a bottleneck if not horizontally scalable

Cache invalidation complexity as data changes

Monitoring overhead with increasing system size

Solutions

Use sharding and read replicas to scale SQL databases horizontally

Choose NoSQL types based on consistency needs (e.g., Cassandra for availability, MongoDB for consistency)

Deploy API Layer behind load balancers and scale horizontally

Implement cache invalidation strategies like TTL and event-driven updates

Use distributed monitoring systems and aggregate metrics efficiently

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying data types and consistency needs, 20 minutes designing the architecture and explaining database choices, 10 minutes discussing scaling and trade-offs, 5 minutes summarizing.

Explain differences between SQL and NoSQL clearly

Discuss data structure, consistency, and scalability trade-offs

Show understanding of real-world use cases for each database type

Highlight importance of API abstraction and caching

Address scaling challenges and practical solutions

Practice

1. Which type of database is best suited for data with a fixed schema and strong relationships between tables?

easy

A. Flat file storage

B. NoSQL database

C. SQL database

D. In-memory cache

Solution

1. Step 1: Understand data structure needs

   Data with fixed schema and strong relationships fits well with structured tables.

2. Step 2: Match database type to structure

   SQL databases use tables and enforce schema, ideal for such data.

3. Final Answer:

   SQL database -> Option C

4. Quick Check:

   Structured data = SQL database [OK]

Hint: Fixed schema and relations? Choose SQL [OK]

Common Mistakes:

• Choosing NoSQL for strictly structured data
• Confusing flat files with databases
• Assuming in-memory cache stores persistent data

2. Which of the following is the correct way to describe NoSQL databases?

easy

A. They always use tables with fixed columns

B. They are designed for flexible, schema-less data storage

C. They require strict ACID transactions for all operations

D. They cannot handle large volumes of data

Solution

1. Step 1: Identify NoSQL characteristics

   NoSQL databases allow flexible, schema-less data models.

2. Step 2: Eliminate incorrect options

   They do not require fixed tables or strict ACID for all operations and can handle large data.

3. Final Answer:

   They are designed for flexible, schema-less data storage -> Option B

4. Quick Check:

   NoSQL = flexible schema [OK]

Hint: No fixed schema? NoSQL fits best [OK]

Common Mistakes:

• Thinking NoSQL always uses tables
• Assuming NoSQL can't scale
• Confusing ACID requirements with SQL only

3. Consider a social media app storing user posts that can have varying fields like text, images, or videos. Which database type will likely handle this data best?

medium

A. SQL database with fixed tables

B. CSV files for storage

C. Relational database with strict schema

D. NoSQL document database

Solution

1. Step 1: Analyze data variability

   User posts have varying fields, so schema flexibility is needed.

2. Step 2: Choose database type matching flexibility

   NoSQL document databases store flexible, nested data well.

3. Final Answer:

   NoSQL document database -> Option D

4. Quick Check:

   Variable fields = NoSQL document DB [OK]

Hint: Varying fields? Use NoSQL document DB [OK]

Common Mistakes:

• Choosing fixed-schema SQL for flexible data
• Using CSV which lacks query power
• Confusing relational DB with NoSQL

4. A developer tries to store rapidly changing sensor data with a fixed schema in a SQL database but faces performance issues. What is the likely cause and best fix?

medium

A. Fixed schema causes overhead; use a NoSQL database for flexibility

B. SQL databases are slow; switch to CSV files

C. Sensor data is small; use in-memory cache only

D. NoSQL databases cannot handle sensor data

Solution

1. Step 1: Identify problem with fixed schema and rapid changes

   Fixed schema in SQL causes overhead with frequent schema updates or inserts.

2. Step 2: Recommend flexible schema database

   NoSQL databases handle rapid, flexible data better, improving performance.

3. Final Answer:

   Fixed schema causes overhead; use a NoSQL database for flexibility -> Option A

4. Quick Check:

   Rapid changes + fixed schema = use NoSQL [OK]

Hint: Rapid, flexible data? NoSQL is better [OK]

Common Mistakes:

• Switching to CSV which lacks performance
• Assuming NoSQL can't handle sensor data
• Ignoring schema flexibility importance

5. You are designing a global e-commerce platform that needs to store user profiles, product catalogs, and handle millions of transactions per day. Which database strategy is best?

hard

A. Use SQL for transactions and NoSQL for product catalogs and user profiles

B. Use only SQL for all data to keep consistency

C. Use only NoSQL for all data to maximize flexibility

D. Use flat files for product catalogs and SQL for transactions

Solution

1. Step 1: Analyze data types and needs

   Transactions require strong consistency (SQL), catalogs and profiles need flexibility and scale (NoSQL).

2. Step 2: Combine database types for best fit

   Using SQL for transactions ensures ACID compliance; NoSQL handles flexible, large-scale data efficiently.

3. Final Answer:

   Use SQL for transactions and NoSQL for product catalogs and user profiles -> Option A

4. Quick Check:

   Mixed data needs = hybrid SQL + NoSQL [OK]

Hint: Mix SQL for transactions, NoSQL for flexible data [OK]

Common Mistakes:

• Using only SQL limits flexibility and scale
• Using only NoSQL risks transaction consistency
• Using flat files lacks query and scale support