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

> Master database design for large-scale applications with this guide. Learn a pattern-driven approach prioritizing access patterns, ACID guarantees, and horizontal scaling through sharding.

- Repository: [Gaurav Kumar/system-design-notes](https://github.com/liquidslr/system-design-notes)
- Tags: how-to-guide
- Published: 2026-09-11

---

**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:

```sql
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:

```sql
-- 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:

```redis
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.