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