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

IRC-Mania Die Seite rund um Sicherheit und Programmierung

Internet, Sicherheit , Internet Relay Chat

Gesponsert

This article offers a comprehensive comparison of SQL and NoSQL databases, focusing on relational systems (MySQL, PostgreSQL) and document-oriented systems (MongoDB), along with practical use cases for each.

In a Nutshell

SQL databases enforce a fixed schema and organize data relationally, while NoSQL databases offer flexible, dynamic schemas. The choice between them depends on your data structure, scalability requirements, and specific application needs.

Technical Overview

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: Custom query languages, aggregation pipelines

Key Differences:

• Data Model: Relational vs document-oriented
• Scalability: Vertical vs horizontal
• Consistency: Strong vs eventual
• Flexibility: Fixed vs dynamic schema

Key Takeaways for Assessment

• SQL: Relational databases with fixed schemas and ACID guarantees
• NoSQL: Document-oriented databases with flexible schemas
• MySQL/PostgreSQL: Popular relational database systems
• MongoDB: Popular NoSQL document database
• Use Cases: SQL for structured data, NoSQL for flexible, evolving data
• Scalability: SQL scales vertically, NoSQL scales horizontally
• Consistency: SQL provides strong consistency, NoSQL offers eventual consistency
• Professional Relevance: Essential knowledge for database design and selection

Core Components

1. SQL Databases: Tables, schemas, JOINS, ACID transactions
2. NoSQL Databases: Documents, collections, flexible schemas
3. Data Model: Relational vs document-oriented
4. Query Languages: SQL vs NoSQL query syntax
5. Scalability: Vertical vs horizontal scaling
6. Consistency Models: ACID vs BASE
7. Use Cases: Structured vs flexible data requirements
8. Performance: Read/write optimization strategies

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 - varied fields are 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 a separate collection
db.bestellungen.insertOne({
    kunden_id: ObjectId("..."), // Reference to customer
    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 guarantees
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
    
    # Query
    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
    
    # Query
    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

# Run performance comparison
print("Performance-Vergleich:")
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}")

IRC-Security.de

Cybersecurity Artikel und Tutorials

Decision Guide: SQL vs NoSQL

When to Use SQL

Structured Data:

• Financial data and accounting
• Customer records with fixed attributes
• Orders with predefined 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 filtering

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 evolving requirements
• MVPs
• Agile development

Strengths and Weaknesses

SQL Databases

Strengths:

• ACID Properties: Strong consistency guarantees
• Standardized: SQL is an established standard
• Tooling: Extensive tools and frameworks available
• Data Integrity: Constraints and referential integrity built in

Weaknesses:

• Schema Rigidity: Schema changes are cumbersome
• Scalability: Primarily vertical scaling
• Performance: Can struggle with very large datasets
• 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 datasets
• Developer-friendly: JSON-like data structures

Disadvantages:

• Consistency: Eventual consistency instead of ACID
• Standardization: No single unified standard
• Tooling: Less mature ecosystem
• Complexity: Transactions and JOINs are more involved

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);
        }
    }
}

Common Exam Questions

1. What’s the main difference between SQL and NoSQL? SQL uses fixed schemas, ACID guarantees, and relational data models. NoSQL offers flexible schemas, BASE semantics, and document-oriented storage.

2. When would you choose NoSQL over SQL? Choose NoSQL for flexible data structures, horizontal scalability requirements, and big data scenarios.

3. Explain ACID versus BASE. ACID ensures Atomicity, Consistency, Isolation, and Durability (strong consistency). BASE provides Basically Available, Soft state, and Eventually consistent data.

4. What are the downsides of NoSQL databases? Fewer established standards, weaker consistency guarantees, and less mature tooling across the ecosystem.

Key Resources

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

FAQ: SQL vs NoSQL

Recommended Reading: Databases

Premium VPS Hosting

Schnelle und sichere VPS Server

Gesponsert