Database Design for Large-Scale Applications: A Step-by-Step Guide from System Design Notes

The system-design-notes repository teaches a pattern-driven approach to database design that prioritizes access patterns over technology hype, emphasizing ACID guarantees for critical transactions and horizontal scaling through sharding for high-traffic workloads.

Database selection and modeling decisions make or break large-scale distributed systems. The liquidslr/system-design-notes repository provides battle-tested methodologies across real-world scenarios—ranging from hotel reservation engines to real-time gaming leaderboards—demonstrating how to match database technologies to specific workload characteristics. This article extracts the core principles, implementation patterns, and concrete code examples from the repository’s analysis of high-traffic system architectures.

Start with Access Patterns, Not Technology

Before selecting a storage engine, the repository insists on mapping read versus write dominance. In 22. Hotel Reservation System/README.md, the authors emphasize: "Before we choose what database to use, let’s consider our access patterns."

The Hotel Reservation System exemplifies this analysis. Because hotel inventory checks are frequent (read-heavy) while actual bookings are relatively rare (write-light), the design favors a relational database with strong consistency guarantees. The repository selects a relational DB specifically for its ACID properties to prevent double-booking and negative inventory scenarios.

Conversely, the Ad Click Event Aggregation system in 21. Ad Click Event Aggregation/README.md faces massive write volumes and analytical queries. Here, the repository recommends OLAP databases like ClickHouse or Druid, noting that "aggregation is typically done in OLAP databases" for high-throughput analytics workloads.

Relational Databases and ACID Guarantees

When consistency bugs are unacceptable—such as over-booking hotel rooms—the repository mandates relational databases with strict transactional integrity.

In 22. Hotel Reservation System/README.md, the authors implement database constraints to enforce business invariants directly in the schema:

CONSTRAINT `check_room_count`
  CHECK ((`total_inventory` - `total_reserved`) >= 0);

This constraint guarantees that total_inventory - total_reserved never drops below zero, preventing race-condition bugs that application-level validation might miss.

For complex transactions, the repository demonstrates optimistic locking using version columns and pessimistic locking via SELECT ... FOR UPDATE. The Hotel Reservation System shows both approaches, with SQL patterns that check inventory availability before committing:

-- Check room inventory before reserving (optimistic locking style)
SELECT date, total_inventory, total_reserved
FROM room_type_inventory
WHERE room_type_id = ${roomTypeId}
  AND hotel_id = ${hotelId}
  AND date BETWEEN ${startDate} AND ${endDate};

-- Abort if reservation exceeds 110% of inventory
IF (total_reserved + ${roomsToReserve}) > 1.10 * total_inventory
   ROLLBACK;
ELSE
   UPDATE room_type_inventory
   SET total_reserved = total_reserved + ${roomsToReserve}
   WHERE room_type_id = ${roomTypeId}
     AND hotel_id = ${hotelId}
     AND date BETWEEN ${startDate} AND ${endDate};
COMMIT;

Horizontal Scaling Through Sharding and Replication

Single-node databases fail under massive load. The repository addresses this through horizontal partitioning strategies.

In the Hotel Reservation System, the authors propose sharding by hotel_id, calculating that "each shard handles 1,875 QPS" when distributing load across multiple database instances. This natural key sharding strategy keeps related data together while spreading write traffic.

For read scalability and high availability, the repository implements read replication across availability zones. As noted in 22. Hotel Reservation System/README.md, "It makes sense to set up read replication (potentially across zones) to enable high availability." This architecture directs write operations to the primary node while read traffic distributes across replicas.

Caching and CDC Strategies

To reduce database load, the repository layers Redis caching for hot data. The Hotel Reservation System specifically caches room inventory in Redis with TTLs to bound staleness:

SET hotelID_roomTypeID_2021-06-01 20

This stores 20 available rooms for a specific date, eliminating repetitive database queries for frequently accessed inventory.

For cache consistency, the repository advocates Change Data Capture (CDC) using tools like Debezium. As implemented in the Hotel Reservation chapter, "Debezium is a popular option for synchronizing database changes with Redis." This pattern propagates database mutations to downstream caches and services without application-level dual-write complexity.

NoSQL and Specialized Storage Patterns

When schema flexibility or extreme write throughput dominates, the repository pivots to NoSQL solutions.

The Real-time Gaming Leaderboard in 25. Real-time Gaming Leaderboard/README.md demonstrates Redis sorted sets for global rankings. The authors explicitly state that "Sorted sets are more performant than relational databases for leaderboards," avoiding expensive SQL ORDER BY operations on large datasets.

For the S3-like Object Storage system in 24. S3-like Object Storage/README.md, the repository employs hybrid storage—using file-based databases like RocksDB or SQLite for low-write, high-read metadata tables while storing blob data in object storage. This tiered approach optimizes cost and performance characteristics for different data types.

Distributed Transaction Coordination

Cross-service operations require coordination protocols. The repository covers two critical patterns:

1. Two-Phase Commit (2PC): Described in 22. Hotel Reservation System/README.md as "a database protocol which guarantees atomic transaction commit across multiple nodes," suitable for short-lived transactions requiring strict atomicity.

2. Saga Pattern: For long-running distributed processes, the repository implements compensating transactions to maintain eventual consistency across microservices.

Summary

• Analyze access patterns first—read-heavy workloads favor relational databases with caching, while write-heavy analytics favor OLAP stores like ClickHouse.
• Enforce constraints at the database level—use CHECK constraints and ACID transactions to prevent consistency bugs that application logic cannot catch.
• Scale horizontally via natural key sharding—distribute data using domain-specific keys like hotel_id to balance load and maintain locality.
• Implement read replication—separate read traffic across zones for high availability while preserving write consistency on the primary.
• Cache strategically with CDC—use Redis for hot data and Debezium streams to maintain cache-database consistency.
• Choose specialized storage—use Redis sorted sets for leaderboards, RocksDB for metadata, and relational databases for transactional integrity.

Frequently Asked Questions

When should I choose a relational database over NoSQL for large-scale applications?

Choose a relational database when your application requires ACID guarantees to prevent consistency bugs like double-booking or negative balances. According to the Hotel Reservation System in 22. Hotel Reservation System/README.md, relational databases excel when you need "ACID guarantees and clear schema" for complex transactions involving multiple entities.

How does the repository recommend handling concurrent reservation attempts?

The repository demonstrates both optimistic locking (using version numbers) and pessimistic locking (using SELECT ... FOR UPDATE) in 22. Hotel Reservation System/README.md. For high-contention scenarios like hotel bookings, the authors recommend checking inventory availability within a transaction and using database constraints to prevent over-commitment.

What sharding strategy does the repository suggest for hotel reservation data?

The repository recommends sharding by hotel_id to distribute load across database instances. As calculated in 22. Hotel Reservation System/README.md, partitioning by this natural key allows "each shard [to handle] 1,875 QPS," ensuring related reservation data for specific hotels resides on the same shard while spreading global traffic.

How should I keep my cache synchronized with the database?

Use Change Data Capture (CDC) with tools like Debezium. The repository emphasizes in 22. Hotel Reservation System/README.md that "Debezium is a popular option for synchronizing database changes with Redis," creating a reliable pipeline that updates caches when underlying database records change without requiring application-level dual-write logic.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}