Relational database strengths in HLD - System Design Exercise

Design: Relational Database Strengths Overview

Focus on strengths of relational databases in system design. Exclude NoSQL or other database types. Discuss typical use cases and architecture components supporting relational databases.

Functional Requirements

FR1: Store structured data with clear relationships

FR2: Support complex queries with joins and filters

FR3: Ensure data consistency and integrity

FR4: Allow multiple users to access and modify data safely

FR5: Support transactions with rollback and commit

FR6: Provide strong data validation and constraints

Non-Functional Requirements

NFR1: Handle up to 10 million records efficiently

NFR2: Support up to 1000 concurrent users

NFR3: Query response time p99 under 200ms

NFR4: Availability target of 99.9% uptime

NFR5: Data durability and ACID compliance

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

Relational database management system (RDBMS)

SQL query engine

Transaction manager

Connection pool

Backup and recovery tools

Indexing and query optimizer

Design Patterns

Normalization to reduce data redundancy

ACID transactions for consistency

Use of indexes for fast lookups

Foreign key constraints for referential integrity

Sharding and replication for scaling

Reference Architecture

Client Application
     |
     v
Connection Pool
     |
     v
Relational Database Server
  +---------------------+
  | SQL Query Processor |
  | Transaction Manager |
  | Storage Engine      |
  +---------------------+
     |
     v
Disk Storage with Data Files and Indexes

Components

Client Application

Any programming language or tool

Sends SQL queries and receives results

Connection Pool

Middleware or built-in DB feature

Manages database connections efficiently

Relational Database Server

PostgreSQL, MySQL, Oracle, SQL Server

Processes queries, manages transactions, stores data

SQL Query Processor

Part of RDBMS

Parses and optimizes SQL queries

Transaction Manager

Part of RDBMS

Ensures ACID properties for data operations

Storage Engine

Part of RDBMS

Handles data storage, indexing, and retrieval

Disk Storage

HDD or SSD

Persistent storage of data and indexes

Request Flow

1. Client sends SQL query to connection pool

2. Connection pool forwards query to database server

3. SQL Query Processor parses and optimizes query

4. Transaction Manager ensures data consistency during query execution

5. Storage Engine reads/writes data from/to disk storage

6. Results are sent back through connection pool to client

Database Schema

Entities have tables with columns and data types. Relationships are modeled using foreign keys. Constraints enforce data validity (e.g., NOT NULL, UNIQUE). Indexes speed up queries. Example: User table linked to Orders table via user_id foreign key.

Scaling Discussion

Bottlenecks

Single database server CPU and memory limits

Disk I/O bottlenecks for large data volumes

Lock contention during high concurrency

Network latency between clients and database

Backup and recovery time with growing data size

Solutions

Use read replicas to distribute read queries

Implement sharding to split data across servers

Optimize queries and add indexes to reduce load

Use connection pooling to manage concurrent connections

Employ faster storage (SSD) and caching layers

Schedule backups during low traffic periods

Interview Tips

Time: Spend 10 minutes explaining relational database strengths and use cases, 15 minutes on architecture and components, 10 minutes on scaling challenges and solutions, 10 minutes for Q&A.

Relational databases excel at structured data with relationships

ACID transactions ensure strong consistency and reliability

SQL enables powerful and flexible querying

Indexes and constraints improve performance and data integrity

Scaling requires read replicas, sharding, and caching strategies

Trade-offs exist between consistency, availability, and partition tolerance

Practice

1. Which of the following is a key strength of relational databases?

easy

A. They are best suited for storing large binary files.

B. They ensure strong data consistency across multiple users.

C. They store data in unstructured formats like documents.

D. They do not support complex queries.

Solution

1. Step 1: Understand relational database data organization

   Relational databases organize data in tables with relationships, which helps maintain consistency.

2. Step 2: Identify the strength related to multi-user access

   They support multiple users accessing and modifying data while keeping it accurate and consistent.

3. Final Answer:

   They ensure strong data consistency across multiple users. -> Option B

4. Quick Check:

   Data consistency = C [OK]

Hint: Relational = tables + strong consistency [OK]

Common Mistakes:

• Confusing relational with document databases
• Thinking relational databases store unstructured data
• Assuming relational databases don't support complex queries

2. Which SQL keyword is used to combine rows from two tables based on a related column?

easy

A. UPDATE

B. SELECT

C. INSERT

D. JOIN

Solution

1. Step 1: Recall SQL keywords for combining tables

   The JOIN keyword is used to combine rows from two or more tables based on a related column.

2. Step 2: Differentiate from other SQL commands

   SELECT retrieves data, INSERT adds data, UPDATE modifies data, but JOIN combines tables.

3. Final Answer:

   JOIN -> Option D

4. Quick Check:

   Combine tables = JOIN [OK]

Hint: JOIN combines tables on related columns [OK]

Common Mistakes:

• Confusing SELECT with JOIN
• Using INSERT or UPDATE to combine tables
• Not knowing JOIN syntax

3. Given two tables Users and Orders, what will the following SQL query return?

SELECT Users.name, Orders.id FROM Users JOIN Orders ON Users.id = Orders.user_id;

medium

A. All users with their order IDs where user IDs match

B. All users regardless of orders

C. All orders regardless of users

D. An error due to missing WHERE clause

Solution

1. Step 1: Analyze the JOIN condition

   The query joins Users and Orders where Users.id equals Orders.user_id, linking users to their orders.

2. Step 2: Understand the SELECT output

   It selects user names and order IDs only for matching pairs, so users without orders are excluded.

3. Final Answer:

   All users with their order IDs where user IDs match -> Option A

4. Quick Check:

   JOIN with ON = matched user-orders [OK]

Hint: JOIN ON filters matching rows only [OK]

Common Mistakes:

• Thinking JOIN returns all users or all orders
• Assuming WHERE clause is mandatory for JOIN
• Confusing JOIN with UNION

4. Identify the error in this SQL query that tries to fetch all customers with their orders:

SELECT Customers.name, Orders.id FROM Customers JOIN Orders;

medium

A. No error, query is correct

B. Using SELECT instead of INSERT

C. Missing ON clause to specify join condition

D. Orders table does not exist

Solution

1. Step 1: Check JOIN syntax

   JOIN requires an ON clause to specify how tables relate; missing ON causes error or unintended Cartesian product.

2. Step 2: Confirm other parts are correct

   SELECT is correct for fetching data; assuming Orders table exists; so error is missing ON clause.

3. Final Answer:

   Missing ON clause to specify join condition -> Option C

4. Quick Check:

   JOIN needs ON clause [OK]

Hint: JOIN without ON causes errors [OK]

Common Mistakes:

• Assuming JOIN works without ON
• Confusing SELECT with INSERT
• Ignoring table existence

5. A company needs a system that supports multiple users updating customer data simultaneously without losing accuracy. Which relational database feature best supports this requirement?

hard

A. ACID transactions ensuring data consistency

B. Storing data as JSON documents

C. Using key-value pairs for fast access

D. NoSQL horizontal scaling

Solution

1. Step 1: Understand multi-user data update needs

   Multiple users updating data require mechanisms to avoid conflicts and keep data accurate.

2. Step 2: Identify relational database feature for consistency

   ACID transactions ensure atomicity, consistency, isolation, and durability, preventing data loss or corruption.

3. Final Answer:

   ACID transactions ensuring data consistency -> Option A

4. Quick Check:

   Multi-user accuracy = ACID transactions [OK]

Hint: ACID = safe multi-user updates [OK]

Common Mistakes:

• Confusing NoSQL features with relational strengths
• Thinking JSON storage ensures consistency
• Ignoring transaction properties