Why database choice impacts architecture in HLD - Design It to Understand It

Design: Database Impact on System Architecture

In scope: database types and their architectural impact, data flow changes, scaling considerations. Out of scope: detailed database internals or vendor-specific features.

Functional Requirements

FR1: Explain how different database types affect system design decisions

FR2: Show how database choice influences scalability, latency, and data consistency

FR3: Demonstrate impact on data modeling and query patterns

FR4: Highlight effects on deployment and maintenance

Non-Functional Requirements

NFR1: Focus on common database types: relational, document, key-value, graph

NFR2: Consider typical scale: up to 100K concurrent users

NFR3: Latency target: p99 API response under 200ms

NFR4: Availability target: 99.9% uptime

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

Database type (SQL, NoSQL variants)

Caching layers

Data access APIs

Replication and sharding mechanisms

Backup and recovery

Design Patterns

CQRS (Command Query Responsibility Segregation)

Event sourcing

Cache aside pattern

Sharding and partitioning

Polyglot persistence

Reference Architecture

Client
  |
  v
API Layer
  |
  v
+---------------------+
|  Database Layer      |
|  +----------------+ |
|  | Relational DB  | |
|  | Document DB    | |
|  | Key-Value DB   | |
|  | Graph DB       | |
|  +----------------+ |
+---------------------+
  |
  v
Cache Layer (optional)

Components

API Layer

REST/gRPC

Handles client requests and routes to appropriate database or cache

Relational Database

PostgreSQL/MySQL

Structured data with strong consistency and complex joins

Document Database

MongoDB/Couchbase

Flexible schema for semi-structured data, fast reads/writes

Key-Value Store

Redis/DynamoDB

Ultra-fast lookups for simple key-based data

Graph Database

Neo4j/JanusGraph

Efficient handling of complex relationships and traversals

Cache Layer

Redis/Memcached

Reduce database load and improve read latency

Request Flow

1. Client sends request to API Layer

2. API Layer determines data type and query complexity

3. For simple key lookups, API checks Cache Layer first

4. If cache miss, API queries appropriate database type

5. Database returns data to API Layer

6. API Layer returns response to client

7. Writes go directly to database with optional cache invalidation

Database Schema

Entities depend on database type: - Relational DB: Tables with normalized relations (1:N, N:N) - Document DB: Collections with nested documents - Key-Value DB: Simple key and value pairs - Graph DB: Nodes and edges representing entities and relationships Relationships and data models must align with chosen database capabilities

Scaling Discussion

Bottlenecks

Relational DB can become slow with complex joins at scale

Document DB may face consistency challenges with distributed writes

Key-Value stores limited by memory size and network latency

Graph DBs can struggle with very large or deeply connected data

Cache invalidation complexity increases with write volume

Solutions

Use read replicas and sharding for relational DB scaling

Implement eventual consistency and conflict resolution for document DB

Scale key-value stores horizontally and optimize network

Partition graph data and limit traversal depth

Use cache aside pattern and TTLs to manage cache freshness

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying data needs, 20 minutes discussing database types and architectural impact, 10 minutes on scaling and trade-offs, 5 minutes summarizing.

Explain how data shape and access patterns influence database choice

Discuss consistency vs availability trade-offs

Highlight how database choice affects latency and scalability

Mention caching and replication strategies

Show awareness of polyglot persistence for complex systems

Practice

1. Which of the following is a primary reason why the choice of database impacts system architecture?

easy

A. Database choice only matters for small systems

B. All databases use the same query language

C. Databases do not affect system performance

D. Different databases handle data consistency and scaling differently

Solution

1. Step 1: Understand database roles in architecture

   Databases store and manage data, affecting how data is accessed and maintained.

2. Step 2: Recognize differences in consistency and scaling

   Relational and NoSQL databases differ in consistency guarantees and how they scale horizontally or vertically.

3. Final Answer:

   Different databases handle data consistency and scaling differently -> Option D

4. Quick Check:

   Database choice affects consistency and scaling [OK]

Hint: Focus on how data handling varies by database type [OK]

Common Mistakes:

• Assuming all databases behave the same
• Ignoring scaling differences
• Thinking database choice doesn't affect performance

2. Which database type is best described by this syntax for creating a table: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));?

easy

A. Document-based NoSQL database

B. Key-value store

C. Relational SQL database

D. Graph database

Solution

1. Step 1: Identify the syntax style

   The CREATE TABLE statement with columns and types is standard SQL syntax.

2. Step 2: Match syntax to database type

   Relational databases use SQL and tables with defined schemas.

3. Final Answer:

   Relational SQL database -> Option C

4. Quick Check:

   SQL table creation = Relational database [OK]

Hint: Look for SQL keywords like CREATE TABLE [OK]

Common Mistakes:

• Confusing NoSQL with SQL syntax
• Assuming all databases use SQL
• Ignoring schema definitions

3. Consider a system using a NoSQL document database storing user profiles as JSON. Which architectural impact is most likely when scaling this system?

medium

A. Easier horizontal scaling with eventual consistency

B. Strong ACID transactions across multiple documents

C. Rigid schema enforcement for all data

D. Limited ability to shard data across servers

Solution

1. Step 1: Understand NoSQL document database traits

   NoSQL document stores often allow flexible schemas and scale horizontally.

2. Step 2: Identify scaling and consistency trade-offs

   They usually provide eventual consistency and support sharding for horizontal scaling.

3. Final Answer:

   Easier horizontal scaling with eventual consistency -> Option A

4. Quick Check:

   NoSQL scaling = horizontal + eventual consistency [OK]

Hint: NoSQL means flexible schema and horizontal scaling [OK]

Common Mistakes:

• Expecting strong ACID transactions in NoSQL
• Assuming rigid schemas
• Thinking sharding is limited

4. A system architect chose a relational database but faces performance issues with high write loads. What is a likely architectural mistake?

medium

A. Using eventual consistency instead of strong consistency

B. Ignoring NoSQL options that scale writes better

C. Choosing a key-value store for complex joins

D. Not using SQL queries for data retrieval

Solution

1. Step 1: Analyze the problem context

   Relational databases can struggle with very high write loads due to ACID constraints.

2. Step 2: Identify architectural mistake

   Ignoring NoSQL databases that handle high write throughput with eventual consistency is a common error.

3. Final Answer:

   Ignoring NoSQL options that scale writes better -> Option B

4. Quick Check:

   High write load needs scalable DB choice [OK]

Hint: High writes? Consider NoSQL for better scaling [OK]

Common Mistakes:

• Assuming relational DB always best
• Confusing consistency models
• Misusing key-value stores for complex queries

5. You are designing a global social media platform requiring fast reads, flexible user data, and eventual consistency. Which database choice best fits this architecture and why?

hard

A. NoSQL document database for flexible schema and horizontal scaling

B. Graph database for user relationships but no horizontal scaling

C. In-memory key-value store only for all data storage

D. Relational database for strong consistency and complex joins

Solution

1. Step 1: Analyze requirements

   The system needs fast reads, flexible data, and can tolerate eventual consistency.

2. Step 2: Match database features to needs

   NoSQL document databases provide flexible schemas, scale horizontally worldwide, and support eventual consistency.

3. Step 3: Evaluate other options

   Relational DBs have strong consistency but less flexible scaling; in-memory stores lack persistence; graph DBs may not scale globally easily.

4. Final Answer:

   NoSQL document database for flexible schema and horizontal scaling -> Option A

5. Quick Check:

   Global scale + flexible data = NoSQL document DB [OK]

Hint: Flexible data + global scale? Pick NoSQL document DB [OK]

Common Mistakes:

• Choosing relational DB despite scaling limits
• Using in-memory store without persistence
• Ignoring horizontal scaling needs