SQL vs NoSQL: Relational vs Document-Oriented Databases & Use Cases

This article is a comprehensive comparison of SQL and NoSQL databases – with a focus on relational (MySQL, PostgreSQL) and document-oriented (MongoDB) databases and their use cases.

In a Nutshell

SQL databases are relational with fixed schemas, NoSQL databases are flexible with dynamic schemas. The choice depends on data structure, scalability, and use cases.

Compact Technical Description

SQL (Structured Query Language) and NoSQL (Not Only SQL) represent two fundamentally different approaches to data storage.

SQL Databases (Relational)

• Schema: Fixed, predefined schema
• Structure: Tables with rows and columns
• ACID: Atomicity, Consistency, Isolation, Durability
• Examples: MySQL, PostgreSQL, Oracle, SQL Server
• Queries: SQL with JOINS, aggregations, subqueries

NoSQL Databases (Document-Oriented)

• Schema: Flexible, dynamic schema
• Structure: JSON/BSON documents
• BASE: Basically Available, Soft state, Eventually consistent
• Examples: MongoDB, CouchDB, DynamoDB
• Queries: Query Language, Aggregation Pipelines

Main Differences:

• Data Model: Relational vs Document-Oriented
• Scalability: Vertical vs Horizontal
• Consistency: Strong vs Eventual
• Flexibility: Fixed vs Dynamic Schema

Exam-Relevant Key Points

• SQL: Relational databases with fixed schema, ACID properties
• NoSQL: Document-oriented databases with flexible schema
• MySQL/PostgreSQL: Popular relational databases
• MongoDB: Popular NoSQL document database
• Use Cases: SQL for structured data, NoSQL for flexible data
• Scalability: SQL vertical, NoSQL horizontal
• Consistency: SQL strong, NoSQL eventual
• IHK-relevant: Important for database design and selection

Core Components

1. SQL Databases: Tables, schema, JOINS, ACID
2. NoSQL Databases: Documents, collections, flexible schema
3. Data Model: Relational vs Document-Oriented
4. Query Languages: SQL vs NoSQL Query Language
5. Scalability: Vertical vs Horizontal
6. Consistency Models: ACID vs BASE
7. Use Cases: Structured vs Flexible Data
8. Performance: Read/write optimization

Practical Examples

1. SQL Database (MySQL/PostgreSQL) Example

-- Schema Definition
CREATE TABLE kunden (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    geburtsdatum DATE,
    adresse_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (adresse_id) REFERENCES adressen(id)
);

CREATE TABLE adressen (
    id INT PRIMARY KEY AUTO_INCREMENT,
    strasse VARCHAR(255) NOT NULL,
    stadt VARCHAR(100) NOT NULL,
    plz VARCHAR(10) NOT NULL,
    land VARCHAR(50) DEFAULT 'Deutschland'
);

CREATE TABLE bestellungen (
    id INT PRIMARY KEY AUTO_INCREMENT,
    kunden_id INT NOT NULL,
    bestelldatum DATE NOT NULL,
    gesamtbetrag DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered') DEFAULT 'pending',
    FOREIGN KEY (kunden_id) REFERENCES kunden(id) ON DELETE CASCADE
);

-- Data Manipulation
INSERT INTO kunden (name, email, geburtsdatum, adresse_id) 
VALUES ('Max Mustermann', 'max@example.com', '1990-05-15', 1);

INSERT INTO adressen (strasse, stadt, plz) 
VALUES ('Hauptstraße 1', 'Berlin', '10115');

-- Complex Query with JOINS
SELECT 
    k.name,
    k.email,
    k.geburtsdatum,
    a.strasse,
    a.stadt,
    COUNT(b.id) as anzahl_bestellungen,
    SUM(b.gesamtbetrag) as umsatz
FROM kunden k
LEFT JOIN adressen a ON k.adresse_id = a.id
LEFT JOIN bestellungen b ON k.id = b.kunden_id
WHERE k.geburtsdatum BETWEEN '1980-01-01' AND '1995-12-31'
    AND a.stadt = 'Berlin'
GROUP BY k.id, k.name, k.email, k.geburtsdatum, a.strasse, a.stadt
HAVING COUNT(b.id) > 0
ORDER BY umsatz DESC
LIMIT 10;

-- Transaction with ACID Properties
BEGIN TRANSACTION;

UPDATE bestellungen 
SET status = 'shipped', 
    bestelldatum = CURRENT_DATE 
WHERE id = 123 AND status = 'pending';

INSERT INTO versand (bestellung_id, tracking_nummer, versanddatum)
VALUES (123, 'DE123456789', CURRENT_DATE);

COMMIT;

2. NoSQL Database (MongoDB) Example

// MongoDB JavaScript Shell

// Collections and documents (no schema required)
db.kunden.insertOne({
    name: "Max Mustermann",
    email: "max@example.com",
    geburtsdatum: new Date("1990-05-15"),
    adresse: {
        strasse: "Hauptstraße 1",
        stadt: "Berlin",
        plz: "10115",
        land: "Deutschland"
    },
    interessen: ["programmieren", "lesen", "reisen"],
    premium: true,
    created: new Date()
});

// Flexible data structure - different fields possible
db.kunden.insertMany([
    {
        name: "Alice Schmidt",
        email: "alice@example.com",
        alter: 28,
        adresse: {
            strasse: "Musterstraße 5",
            stadt: "Hamburg",
            plz: "20095"
        },
        interessen: ["design", "fotografie"],
        social_media: {
            twitter: "@alice_design",
            instagram: "alice.photos"
        }
    },
    {
        name: "Bob Weber",
        email: "bob@example.com",
        alter: 35,
        adresse: {
            strasse: "Bahnhofstraße 10",
            stadt: "München",
            plz: "80331",
            land: "Deutschland"
        },
        firma: {
            name: "TechCorp",
            position: "Senior Developer",
            seit: new Date("2018-03-01")
        },
        interessen: ["programmierung", "klettern", "kochen"]
    }
]);

// Orders as separate collection
db.bestellungen.insertOne({
    kunden_id: ObjectId("..."), // Reference to customers
    positionen: [
        {
            produkt: "Laptop",
            anzahl: 1,
            preis: 999.99
        },
        {
            produkt: "Maus",
            anzahl: 2,
            preis: 29.99
        }
    ],
    gesamtbetrag: 1059.97,
    status: "pending",
    bestelldatum: new Date(),
    zahlung: {
        methode: "credit_card",
        status: "paid",
        transaktions_id: "txn_123456789"
    }
});

// Complex Aggregation Pipeline
db.kunden.aggregate([
    {
        $match: {
            "adresse.stadt": "Berlin",
            premium: true
        }
    },
    {
        $lookup: {
            from: "bestellungen",
            localField: "_id",
            foreignField: "kunden_id",
            as: "bestellungen"
        }
    },
    {
        $addFields: {
            anzahl_bestellungen: { $size: "$bestellungen" },
            umsatz: {
                $sum: "$bestellungen.gesamtbetrag"
            }
        }
    },
    {
        $project: {
            name: 1,
            email: 1,
            "adresse.stadt": 1,
            interessen: 1,
            anzahl_bestellungen: 1,
            umsatz: 1,
            avg_bestellwert: {
                $divide: ["$umsatz", "$anzahl_bestellungen"]
            }
        }
    },
    {
        $sort: {
            umsatz: -1
        }
    },
    {
        $limit: 10
    }
]);

// Flexible queries with dynamic fields
db.kunden.find({
    $or: [
        { "interessen": "programmieren" },
        { "firma.name": { $exists: true } },
        { alter: { $gte: 30, $lte: 40 } }
    ],
    "adresse.land": "Deutschland"
}).sort({ "name": 1 });

// Text search with index
db.kunden.createIndex({ name: "text", "interessen": "text" });

db.kunden.find({
    $text: { $search: "programmieren reisen" }
});

3. Use Case Comparison: E-Commerce Platform

-- SQL approach for structured data
-- Products with fixed attributes
CREATE TABLE produkte (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    beschreibung TEXT,
    preis DECIMAL(10,2) NOT NULL,
    kategorie_id INT,
    lagerbestand INT DEFAULT 0,
    gewicht DECIMAL(5,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kategorie_id) REFERENCES kategorien(id)
);

-- Categories with hierarchy
CREATE TABLE kategorien (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INT,
    ebene INT NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES kategorien(id)
);

-- Orders with ACID guarantee
CREATE TABLE bestellungen (
    id INT PRIMARY KEY AUTO_INCREMENT,
    kunden_id INT NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    gesamtbetrag DECIMAL(10,2) NOT NULL,
    bestelldatum TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kunden_id) REFERENCES kunden(id)
);

BEGIN TRANSACTION;
-- Atomic order processing
INSERT INTO bestellungen (kunden_id, gesamtbetrag, status)
VALUES (123, 299.99, 'paid');

UPDATE produkte 
SET lagerbestand = lagerbestand - 1 
WHERE id = 456;

INSERT INTO bestellpositionen (bestellung_id, produkt_id, menge, preis)
VALUES (LAST_INSERT_ID(), 456, 1, 299.99);

COMMIT;

// NoSQL approach for flexible data
// Products with variable attributes
db.produkte.insertOne({
    name: "Smartphone XYZ",
    beschreibung: "Modernes Smartphone mit vielen Features",
    preis: 599.99,
    kategorie: "Elektronik",
    lagerbestand: 150,
    eigenschaften: {
        marke: "TechBrand",
        modell: "XYZ Pro",
        farbe: ["schwarz", "weiß", "blau"],
        speicher: ["64GB", "128GB", "256GB"],
        anzeige: {
            groesse: "6.1 Zoll",
            aufloesung: "1080x2340",
            technologie: "OLED"
        },
        kamera: {
            hauptkamera: "48MP",
            frontkamera: "12MP",
            features: ["Nachtmodus", "Portrait", "4K Video"]
        },
        konnektivitaet: ["5G", "WiFi 6", "Bluetooth 5.0", "NFC"]
    },
    bewertungen: [
        {
            sterne: 5,
            kommentar: "Tolles Gerät!",
            datum: new Date("2024-01-15")
        },
        {
            sterne: 4,
            kommentar: "Gutes Preis-Leistungs-Verhältnis",
            datum: new Date("2024-01-20")
        }
    ],
    tags: ["smartphone", "5g", "kamera", "premium"]
});

// Flexible orders with various payment methods
db.bestellungen.insertOne({
    kunden_id: ObjectId("..."),
    status: "paid",
    gesamtbetrag: 599.99,
    positionen: [
        {
            produkt_id: ObjectId("..."),
            produkt_name: "Smartphone XYZ",
            variante: {
                farbe: "schwarz",
                speicher: "128GB"
            },
            menge: 1,
            einzelpreis: 599.99,
            gesamtpreis: 599.99
        }
    ],
    zahlung: {
        methode: "credit_card",
        karte: {
            typ: "visa",
            letzte_zahlen: "1234",
            ablaufdatum: "12/25"
        },
        status: "paid",
        transaktions_id: "txn_abc123",
        zahlungsdatum: new Date()
    },
    versand: {
        methode: "standard",
        adresse: {
            name: "Max Mustermann",
            strasse: "Hauptstraße 1",
            stadt: "Berlin",
            plz: "10115",
            land: "Deutschland"
        },
        tracking: {
            nummer: "DE123456789",
            status: "shipped",
        }
    },
    created: new Date()
});

4. Performance Comparison

# Python Performance Comparison

import time
import sqlite3
import pymongo
from pymongo import MongoClient

# SQL Performance Test
def sql_performance_test():
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    
    # Create tables
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT,
            email TEXT,
            age INTEGER
        )
    ''')
    
    # Insert data
    start = time.time()
    for i in range(10000):
        cursor.execute(
            'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
            (f'User {i}', f'user{i}@example.com', 20 + i % 50)
        )
    sql_insert_time = time.time() - start
    
    # Queries
    start = time.time()
    cursor.execute('SELECT * FROM users WHERE age BETWEEN 30 AND 40')
    results = cursor.fetchall()
    sql_query_time = time.time() - start
    
    conn.close()
    
    return sql_insert_time, sql_query_time, len(results)

# NoSQL Performance Test
def nosql_performance_test():
    client = MongoClient('localhost', 27017)
    db = client['test_db']
    users = db['users']
    
    # Insert data
    start = time.time()
    documents = []
    for i in range(10000):
        documents.append({
            'name': f'User {i}',
            'email': f'user{i}@example.com',
            'age': 20 + i % 50
        })
    
    users.insert_many(documents)
    nosql_insert_time = time.time() - start
    
    # Queries
    start = time.time()
    results = users.find({'age': {'$gte': 30, '$lte': 40}})
    count = len(list(results))
    nosql_query_time = time.time() - start
    
    client.close()
    
    return nosql_insert_time, nosql_query_time, count

# Perform performance comparison
print("Performance Comparison:")
sql_insert, sql_query, sql_count = sql_performance_test()
nosql_insert, nosql_query, nosql_count = nosql_performance_test()

print(f"SQL - Insert: {sql_insert:.4f}s, Query: {sql_query:.4f}s, Results: {sql_count}")
print(f"NoSQL - Insert: {nosql_insert:.4f}s, Query: {nosql_query:.4f}s, Results: {nosql_count}")

Decision Guide: SQL vs NoSQL

When to use SQL?

Structured data:

• Financial data, accounting
• Customer data with fixed attributes
• Orders with defined fields
• Inventory with standardized properties

ACID requirements:

• Bank transactions
• Accounting systems
• E-commerce order processing
• Reservation systems

Complex queries:

• Reporting with JOINS
• Aggregations across multiple tables
• Data analysis with complex filters

When to use NoSQL?

Flexible data structures:

• Content management systems
• Social media posts
• IoT sensor data
• User profiles with variable attributes

Horizontal scalability:

• Big data applications
• Social networks
• Real-time analytics
• Microservices

Rapid prototyping:

• Startups with rapidly changing requirements
• MVPs (Minimum Viable Products)
• Agile development

Advantages and Disadvantages

SQL Databases

Advantages:

• ACID properties: Strong consistency guarantees
• Standardized: SQL as an established standard
• Tools: Many tools and frameworks available
• Data integrity: Constraints and referential integrity

Disadvantages:

• Schema rigidity: Changes are complex
• Scalability: Primarily vertical scaling
• Performance: With very large data volumes
• Flexibility: Limited for unstructured data

NoSQL Databases

Advantages:

• Flexible Schema: Easy adaptation to new requirements
• Horizontal Scaling: Simple distribution across many servers
• Performance: Optimized for large data volumes
• Developer-Friendly: JSON-like data structures

Disadvantages:

• Consistency: Eventual Consistency instead of ACID
• Standardization: No unified standard
• Tools: Less mature tools
• Complexity: Transactions and JOINS more complex

Migration Strategies

SQL to NoSQL Migration

// Schema-Mapping für Migration
const migrationMapping = {
    users: {
        sql_table: 'users',
        nosql_collection: 'users',
        fields: {
            id: '_id',
            name: 'name',
            email: 'email',
            created_at: 'created'
        },
        // Transformation rules
        transform: (row) => ({
            _id: row.id.toString(),
            name: row.name,
            email: row.email,
            created: new Date(row.created_at),
            profile: {
                age: row.age || null,
                preferences: []
            }
        })
    }
};

// Migration Script
async function migrateToNoSQL(sqlConnection, mongoConnection) {
    for (const [collectionName, mapping] of Object.entries(migrationMapping)) {
        const sqlData = await sqlConnection.query(`SELECT * FROM ${mapping.sql_table}`);
        
        for (const row of sqlData) {
            const transformedData = mapping.transform(row);
            await mongoConnection.collection(mapping.nosql_collection).insertOne(transformedData);
        }
    }
}

Frequently Asked Exam Questions

1. What is the main difference between SQL and NoSQL? SQL: Fixed schema, ACID, relational data. NoSQL: Flexible schema, BASE, document-oriented data.

2. When would you choose NoSQL over SQL? With flexible data structures, horizontal scalability, and Big Data requirements.

3. Explain ACID vs BASE! ACID: Atomicity, Consistency, Isolation, Durability (strong consistency). BASE: Basically Available, Soft state, Eventually consistent.

4. What are the disadvantages of NoSQL databases? Less established standards, weaker consistency guarantees, less mature tools.

Most Important Sources

1. https://www.mongodb.com/compare/sql-nosql/
2. https://www.postgresql.org/about/
3. https://dev.mysql.com/doc/refman/8.0/en/

---

Recommended Reading: Databases