Choosing the Right Database

The Database Selection Challenge

Choosing the right database is one of the most critical decisions in system design. The wrong choice can lead to performance problems, scaling issues, and development headaches.

Simple Analogy

Choosing a database is like choosing a vehicle:

• SQL: Like a sedan—versatile, reliable, good for most trips
• Document DB: Like a pickup truck—flexible, can carry different loads
• Key-Value: Like a motorcycle—fast and simple, but limited
• Graph DB: Like a GPS system—optimized for navigating relationships

Choose based on your journey (use case)!

---

Decision Framework

Step 1: Analyze Your Data Structure

Questions to Ask:

• Is your data structured (tables) or unstructured (documents)?
• Do you have fixed relationships or flexible structures?
• Do you need to store nested/hierarchical data?

---

Step 2: Analyze Query Patterns

Questions to Ask:

• Do you need complex JOINs or simple lookups?
• Are queries mostly by key or by complex conditions?
• Do you need to traverse relationships?

---

Step 3: Analyze Scale Requirements

Questions to Ask:

• How many records do you expect?
• What’s your expected QPS (queries per second)?
• Do you need horizontal scaling?

---

Step 4: Analyze Consistency Requirements

Questions to Ask:

• Do you need ACID transactions?
• Can you tolerate eventual consistency?
• Is data accuracy critical or is speed more important?

---

Decision Matrix

Use Case	Data Structure	Query Pattern	Scale	Consistency	Recommended
E-commerce	Structured	Complex JOINs	Medium	Strong	SQL (PostgreSQL)
Social Media	Semi-structured	Simple lookups	Large	Eventual	Document DB (MongoDB)
Caching	Simple	Key lookup	Large	Eventual	Key-Value (Redis)
Social Network	Relationships	Graph queries	Large	Eventual	Graph DB (Neo4j)
Time-Series	Structured	Column queries	Large	Eventual	Column-Family (Cassandra)
Financial	Structured	Complex queries	Medium	Strong	SQL (PostgreSQL)
Content Management	Semi-structured	Document queries	Medium	Eventual	Document DB (MongoDB)
Session Storage	Simple	Key lookup	Large	Eventual	Key-Value (Redis)

---

Real-World Examples

Example 1: E-Commerce Platform

Why SQL?

• Structured product/order/user data
• Need complex queries (reports, analytics)
• ACID transactions for checkout
• Relationships between entities

---

Example 2: Social Media Feed

Why Document DB?

• Flexible post structure (text, images, videos)
• Simple queries (get user’s posts)
• Need to scale horizontally
• Fast reads more important than complex queries

---

Example 3: Recommendation Engine

Why Graph DB?

• Complex relationships (users, products, purchases)
• Need to traverse relationships
• “Find similar users” queries
• Relationship queries are primary use case

---

Polyglot Persistence

Polyglot persistence means using multiple database types in the same system. Different parts use different databases optimized for their needs.

Example: E-commerce platform

• PostgreSQL: Orders, payments, inventory (structured, ACID)
• MongoDB: Product catalogs, reviews (flexible schema)
• Redis: Shopping cart, sessions, cache (fast lookups)

---

LLD ↔ HLD Connection

How database choice affects your class design:

Mapping Domain Models to Storage

Python

Database Abstraction

from abc import ABC, abstractmethod
from typing import Optional

class UserRepository(ABC):
    """Abstract repository - database agnostic"""
    @abstractmethod
    def find_by_id(self, user_id: int) -> Optional['User']:
        pass

    @abstractmethod
    def save(self, user: 'User') -> 'User':
        pass

class SQLUserRepository(UserRepository):
    """SQL implementation"""
    def __init__(self, db_connection):
        self.db = db_connection

    def find_by_id(self, user_id: int) -> Optional['User']:
        # SQL query
        cursor = self.db.execute("SELECT * FROM users WHERE id = ?", (user_id,))
        row = cursor.fetchone()
        return User.from_row(row) if row else None

    def save(self, user: 'User') -> 'User':
        # SQL insert/update
        self.db.execute(
            "INSERT INTO users (id, name, email) VALUES (?, ?, ?)",
            (user.id, user.name, user.email)
        )
        return user

class MongoDBUserRepository(UserRepository):
    """MongoDB implementation"""
    def __init__(self, mongo_collection):
        self.collection = mongo_collection

    def find_by_id(self, user_id: int) -> Optional['User']:
        # MongoDB query
        doc = self.collection.find_one({"_id": user_id})
        return User.from_document(doc) if doc else None

    def save(self, user: 'User') -> 'User':
        # MongoDB insert/update
        self.collection.replace_one(
            {"_id": user.id},
            user.to_document(),
            upsert=True
        )
        return user

Java

Database Abstraction

import java.util.Optional;

public interface UserRepository {
    // Abstract repository - database agnostic
    Optional<User> findById(Integer userId);
    User save(User user);
}

public class SQLUserRepository implements UserRepository {
    // SQL implementation
    private Connection connection;

    public Optional<User> findById(Integer userId) {
        try (PreparedStatement stmt = connection.prepareStatement(
            "SELECT * FROM users WHERE id = ?"
        )) {
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return Optional.of(mapToUser(rs));
            }
        }
        return Optional.empty();
    }

    public User save(User user) {
        // SQL insert/update
        // ...
        return user;
    }
}

public class MongoDBUserRepository implements UserRepository {
    // MongoDB implementation
    private MongoCollection<Document> collection;

    public Optional<User> findById(Integer userId) {
        Document doc = collection.find(eq("_id", userId)).first();
        return doc != null ? Optional.of(mapToUser(doc)) : Optional.empty();
    }

    public User save(User user) {
        // MongoDB insert/update
        collection.replaceOne(eq("_id", user.getId()), user.toDocument(),
                              new ReplaceOptions().upsert(true));
        return user;
    }
}

---

Key Takeaways

Remember

• Decision Framework: Analyze data structure, query patterns, scale, consistency
• SQL: Structured data, complex queries, ACID transactions, relationships
• NoSQL: Unstructured data, simple queries, horizontal scaling, flexibility
• Polyglot Persistence: Use multiple databases for different needs
• Map Domain Models: Design classes that match database structure
• Repository Pattern: Abstract database choice from business logic
• Choose wisely: Wrong database = performance and scaling problems

---

What’s Next?

Now that you understand database selection, let’s dive deep into indexing strategies to optimize database performance:

Next up: Database Indexing Strategies — Learn about B-trees, LSM trees, and inverted indexes for designing searchable entities.

Practice Exercise

Design a database architecture for a video streaming platform. What databases would you use? Where? Why? How would you structure the data?