Databases in System Design

Choosing the right database is one of the most critical decisions in system design. The choice impacts performance, scalability, data integrity, and the overall complexity of the system. A database model that excels for one use case can be a poor fit for another.

This section introduces the two major families of databases—SQL and NoSQL —and explores the fundamental trade-offs between them.


The Fundamental Trade-Off: SQL vs. NoSQL

The primary difference between SQL and NoSQL lies in how they model, store, and retrieve data, which in turn dictates their strengths in consistency, scalability, and flexibility.

graph TD
    subgraph "SQL (Relational)"
        direction LR
        Table1[Users Table] --> Table2[Orders Table]
    end
    subgraph "NoSQL (Document)"
        Doc["User Document {<br>  name: 'Alice',<br>  orders: [ ... ]<br>}"]
    end

Description: SQL databases use a structured, tabular format with relationships between tables. NoSQL databases often use flexible formats like JSON documents where related data can be embedded.

SQL (Relational) Databases

Relational databases , which use Structured Query Language (SQL), have been the standard for decades. They organize data into tables with rows and columns, and enforce a predefined schema.

  • Data Model: Structured and tabular.
  • Schema: Fixed and defined before data is inserted. Enforces data integrity.
  • Key Strength: Guarantees ACID transactions (Atomicity, Consistency, Isolation, Durability), making them extremely reliable for financial and mission-critical data.
  • Querying: SQL is an extremely powerful language for performing complex queries, aggregations, and JOINs across multiple tables.
  • Scalability: Traditionally scale vertically (by increasing the power of a single server). While modern RDBMS have better horizontal scaling, it is generally more complex than in NoSQL systems.
  • Examples: MySQL, PostgreSQL, Microsoft SQL Server, Oracle.
  • Learn more: Relational Databases (RDBMS)

NoSQL (Non-Relational) Databases

NoSQL databases emerged to meet the demands of large-scale, distributed systems where the rigid structure of SQL was a bottleneck. They encompass a wide variety of data models.

  • Data Model: Highly flexible, including Key-Value, Document, Column-family, and Graph models.
  • Schema: Dynamic or schema-less. You can add new fields without redefining the entire database structure.
  • Key Strength: Designed for horizontal scaling (scaling out by adding more servers). They often prioritize performance and availability over strong consistency, aligning with the BASE model (Basically Available, Soft state, Eventually consistent).
  • Querying: Query languages vary by database and are often less powerful than SQL for complex joins.
  • Trade-offs: The flexibility and scalability often come at the cost of weaker consistency guarantees, as explained in the CAP Theorem.
  • Examples: MongoDB (Document), Redis (Key-Value), Cassandra (Wide-column), Neo4j (Graph).
  • Learn more: NoSQL Databases

Further Reading


At a Glance: SQL vs. NoSQL

FeatureSQL (RDBMS)NoSQL
Data ModelStructured (Tables with rows/columns)Flexible (Documents, Key-Value, etc.)
SchemaRigid, predefinedDynamic
ScalabilityVertical (Scale-up)Horizontal (Scale-out)
ConsistencyStrong (ACID)Varies (Often BASE / Eventual)
QueryingPowerful SQL with complex JOINsVaries; often simpler, no complex JOINs
Best for…Financial systems, e-commerce, applications requiring high data integrity.Big Data, social media, IoT, real-time web apps.

Articles

  1. SQL vs. NoSQL: What’s the difference? - IBM An article from IBM that provides a high-level overview of the key differences in data structure, scalability, and use cases for SQL and NoSQL databases .

  2. SQL vs NoSQL: 5 Critical Differences | Integrate.io This article details five critical differences between SQL and NoSQL, focusing on architecture, schemas, and scaling to help choose the right database.

Videos

  1. SQL vs. NoSQL: What’s the difference? A video from IBM explaining the top five differences between SQL and NoSQL to help in making an informed decision.

  2. SQL vs. NoSQL Explained (in 4 Minutes) A concise video that clarifies the distinctions between SQL and NoSQL, covering concepts like structured data, ACID compliance, and horizontal scaling.