Databases in System Design

# CHAPTER 4

Databases in System Design #

1. Introduction #

2. Learning Objectives #

• Compare and contrast SQL (Relational) and NoSQL (Non-Relational) databases.

• Define Database Normalization and strict schemas.

• Understand the CAP Theorem implications for choosing a database.

• Engineer Database Replication (Master-Slave) for high availability and read-scaling.

• Architect Database Sharding (Partitioning) for massive horizontal scale.

3. SQL (Relational Databases) #

• Examples: MySQL, PostgreSQL, Oracle.

• The Structure: Data is stored in strict, highly structured Tables (Rows and Columns), exactly like Excel spreadsheets.

• Relationships: Tables are linked together using "Foreign Keys" (e.g., A User table is mathematically linked to an Orders table).

• ACID Compliance: SQL databases guarantee absolute data integrity. If a bank transfer fails halfway through, the database guarantees the entire transaction is rolled back. There is zero chance of money disappearing.

• The Weakness: Because of strict relational mathematics, SQL databases are incredibly difficult to scale horizontally across multiple servers. They prefer to be scaled Vertically (one massive server).

4. NoSQL (Non-Relational Databases) #

• Examples: MongoDB, Cassandra, DynamoDB.

• The Structure: Data is not stored in rigid tables. It is often stored as flexible JSON-like documents. A single document can contain a user's profile, their friends list, and their settings all in one nested blob.

• The Strength (Flexibility): You do not need to define a strict schema beforehand. You can add new fields on the fly.

• The Superpower (Scale): NoSQL databases were built from day one to scale horizontally across thousands of cheap servers. If you need to store billions of tweets, NoSQL is the answer.

5. Choosing Between SQL and NoSQL #

• Choose SQL when: You are building a financial system, a billing engine, or an inventory tracker. You need strict data integrity, complex queries, and absolute consistency.

• Choose NoSQL when: You are building a social media feed, IoT sensor logs, or an analytics engine. You have massive amounts of rapidly changing, unstructured data, and you prioritize speed and horizontal scalability over strict relational consistency.

6. Database Replication (Master-Slave) #

• The Bottleneck: If 100,000 users are trying to read a blog post from a single database simultaneously, it will crash.

• The Solution: Replication.

• The Architecture:

• Master DB: You have one primary database. It is the ONLY database allowed to perform Writes (Inserts, Updates, Deletes).

• Slave (Replica) DBs: You create 5 identical copies of the Master. They constantly sync data from the Master. They are ONLY allowed to perform Reads.

• The Result: When 100,000 users request the blog post, a load balancer splits those "Read" requests evenly across the 5 Replicas.

7. Database Sharding (Partitioning) #

• The Concept: Sharding is horizontally scaling a database. You take one massive database of 100 million users and split it into 5 smaller databases of 20 million users each, hosted on 5 different physical servers.

• The Routing Logic (The Shard Key): You must define a rule. For example: "Users with IDs 1-20M go to Server A. Users with IDs 20M-40M go to Server B."

• *Warning:* Sharding introduces immense engineering complexity. Querying data across multiple shards (e.g., finding the top 10 highest-paying users globally) is extremely slow and difficult.

8. Diagrams/Visual Suggestions #

[ Application Servers ]
       |   (Write Requests)
       v
[ MASTER Database ] --(Data Sync)--> [ REPLICA 1 ] (Read-only)
                    --(Data Sync)--> [ REPLICA 2 ] (Read-only)
                    --(Data Sync)--> [ REPLICA 3 ] (Read-only)

[ Application Servers ] --(Read Requests)--> [ REPLICAS ]

9. Best Practices #

• Polyglot Persistence: Modern complex systems do not use just one database type. A massive architecture (like Amazon) will use SQL (PostgreSQL) for billing and user accounts, NoSQL (MongoDB) for the product catalog, and an In-Memory store (Redis) for shopping carts. Use the right tool for the specific job.

10. Common Mistakes #

• Sharding Too Early: Sharding is the absolute "last resort" of database scaling. Once you shard a database, it is nearly impossible to un-shard it. *The Fix:* Exhaust all other options first. Upgrade the hardware (Vertical scale), add Read Replicas, optimize your SQL queries, and implement aggressive Caching before you ever consider sharding.

11. Mini Project: Architect an E-Commerce DB #

1. 1. The Users & Orders (SQL): We need strict integrity so nobody gets charged twice. We choose PostgreSQL. We set up 1 Master DB for writing orders, and 3 Replicas for users checking their order history.

1. 2. The Product Catalog (NoSQL): Products have wildly different attributes (a TV has 'screen size'; a shirt has 'fabric type'). Strict SQL tables would be messy. We choose MongoDB to store flexible JSON documents for fast catalog searching.

1. 3. *Result:* A resilient, purpose-built, polyglot data architecture.

12. Practice Exercises #

1. 1. Compare SQL and NoSQL databases. Provide one real-world scenario where SQL is mandatory, and one scenario where NoSQL is heavily preferred.

1. 2. Explain the architecture of "Master-Slave Replication." How does this specific pattern solve the bottleneck of massive "Read" traffic?

13. MCQs with Answers #

14. Interview Questions #

• Q: A system is experiencing immense lag because 50,000 users are simultaneously trying to view a live leaderboard. Walk me through how you would implement Database Replication to resolve this read-heavy bottleneck.

• Q: What is Database Sharding? Explain the concept of a "Shard Key" and discuss the immense operational complexities introduced when you shard a relational database.

• Q: You are building the architecture for an Uber-like ride-sharing app. Would you use SQL or NoSQL to store the constantly updating GPS coordinates of the drivers? Defend your choice.

15. Summary #

16. Next Chapter Recommendation #