Why database choice impacts architecture in HLD - Quick Recap

Recall & Review

beginner

What is one main reason the choice of database affects system architecture?

Different databases have unique ways of storing and retrieving data, which influences how the system is designed to handle data flow and performance.

intermediate

How does a relational database differ from a NoSQL database in terms of architecture impact?

Relational databases use structured tables and enforce relationships, requiring strict schema design, while NoSQL databases offer flexible schemas and scale horizontally, affecting how data models and scaling strategies are planned.

intermediate

What is CAP theorem and why is it important when choosing a database?

CAP theorem states a system can only guarantee two of three: Consistency, Availability, and Partition tolerance. This affects architecture decisions based on which properties are prioritized.

advanced

Why does database scalability influence system architecture?

If a database scales well horizontally, the architecture can distribute load across many servers, improving performance and fault tolerance. Poor scalability may require more complex caching or sharding strategies.

advanced

How can database consistency models impact application design?

Strong consistency requires waiting for all nodes to agree on data, which can slow responses but ensures accuracy. Eventual consistency allows faster responses but needs the application to handle temporary data differences.

Which database type typically requires a fixed schema and enforces relationships?

A Graph database

B NoSQL document store

C Key-value store

D Relational database

Relational databases use tables with fixed schemas and enforce relationships through keys.

What does the CAP theorem say a distributed system cannot guarantee all at once?

A Concurrency, Accessibility, Persistence

B Capacity, Accuracy, Performance

C Consistency, Availability, Partition tolerance

D Cache, Analytics, Privacy

CAP theorem states a system can only guarantee two of Consistency, Availability, and Partition tolerance.

Which database feature most directly affects how easily a system can handle more users?

A Scalability

B Encryption

C Backup frequency

D Data type support

Scalability determines how well a database can grow to support more users or data.

Eventual consistency means:

A Data is always immediately consistent

B Data updates will be visible everywhere after some time

C Data is never consistent

D Data is consistent only on weekends

Eventual consistency means data changes propagate and become consistent after a delay.

Choosing a NoSQL database often means:

A Flexible schema and easier horizontal scaling

B Strict schema and vertical scaling only

C No support for distributed systems

D No support for large data volumes

NoSQL databases usually allow flexible schemas and scale horizontally well.

Explain how the choice between relational and NoSQL databases impacts system architecture.

Think about how data is organized and how the system grows.

Describe the importance of CAP theorem in designing distributed database systems.

Consider what happens when parts of the system can't communicate.

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