SQL vs NoSQL - Guidelines for Database Selection

Types of Databases

Databases are broadly divided into two types: SQL (relational) and NoSQL (non-relational). Neither is inherently superior; the important thing is choosing the right one for your use case.

SQL (Relational Databases)

Characteristics

• Stores data in tables (rows and columns)
• Schema (data structure) is defined in advance
• Standardized query language with SQL
• Transaction guarantees through ACID properties

-- Table definition
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  total DECIMAL(10, 2),
  status VARCHAR(20)
);

-- Query using relations
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';

ACID Properties

Representative Databases

NoSQL

Types and Characteristics

1. Document Type

Stores data as JSON-like documents.

// MongoDB example
{
  "_id": "user_123",
  "name": "Alice",
  "email": "alice@example.com",
  "orders": [
    { "id": "ord_1", "total": 5000, "items": [...] },
    { "id": "ord_2", "total": 3000, "items": [...] }
  ],
  "preferences": {
    "theme": "dark",
    "language": "ja"
  }
}

Examples: MongoDB, CouchDB, Firestore

2. Key-Value Type

Stores data as simple key-value pairs.

user:123:name → "Alice"
user:123:email → "alice@example.com"
session:abc123 → { "userId": 123, "expires": "..." }

Examples: Redis, Amazon DynamoDB, etcd

3. Column-Oriented Type

Stores data by columns rather than rows. Suitable for aggregating large amounts of data.

flowchart TB
    subgraph RowBased["Row-Based Storage"]
        R1["user_1 | Alice | alice@ex.com"]
        R2["user_2 | Bob | bob@ex.com"]
    end

    subgraph ColumnBased["Column-Based Storage"]
        C1["name → Alice, Bob, ..."]
        C2["email → alice@ex.com, bob@ex.com, ..."]
    end

    RowBased -->|"Stored by column"| ColumnBased

Examples: Apache Cassandra, HBase, ClickHouse

4. Graph Type

Stores data as relationships between nodes and edges.

(Alice)--[FOLLOWS]-->(Bob)
(Alice)--[LIKES]-->(Post1)
(Bob)--[WROTE]-->(Post1)

Examples: Neo4j, Amazon Neptune, ArangoDB

CAP Theorem

A theorem stating that in distributed systems, only two of the following three properties can be satisfied simultaneously.

flowchart TB
    subgraph CAP["CAP Theorem"]
        C["Consistency"]
        A["Availability"]
        P["Partition Tolerance"]

        CP["CP: MongoDB, Redis Cluster"]
        AP["AP: Cassandra, CouchDB"]
        CA["CA: Single-node RDBMS"]

        C --- CP
        P --- CP
        A --- AP
        P --- AP
        C --- CA
        A --- CA
    end

Classification Examples

• CP: MongoDB, Redis Cluster
• AP: Cassandra, CouchDB
• CA: Single-node RDBMS (difficult to achieve in distributed environments)

Comparison Table

Choosing Based on Use Case

Cases Where SQL is Suitable

Cases Where NoSQL is Suitable

Specific Examples

Polyglot Persistence

An approach where multiple databases are used within a single application.

flowchart LR
    App["E-Commerce Application"]
    App --> PG["PostgreSQL<br/>Products, orders, customers"]
    App --> Redis["Redis<br/>Sessions, cache"]
    App --> ES["Elasticsearch<br/>Product search"]
    App --> Mongo["MongoDB<br/>Product reviews, content"]

Summary

Database selection should be based on data characteristics, scalability requirements, and consistency requirements. SQL and NoSQL have trade-offs, and neither is universally superior. By using the right tool for the job and combining multiple databases as needed, you can achieve an optimal architecture.