SQL vs NoSQL

---

Definition

---

NoSQL Types

---

Comparison

---

When to Use

---

Data Modeling

// SQL: Normalized, relationships via foreign keys

// Users table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

// Orders table (references users)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total DECIMAL(10,2),
    created_at TIMESTAMP
);

// Query with JOIN
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 123;

// NoSQL (MongoDB): Denormalized, embedded documents

// Embedded approach (one document)
{
    "_id": "user123",
    "name": "John Doe",
    "email": "[email protected]",
    "orders": [
        { "id": "order1", "total": 99.99, "date": "2024-01-15" },
        { "id": "order2", "total": 149.99, "date": "2024-01-20" }
    ]
}

// Query (no join needed)
db.users.findOne({ _id: "user123" })

// Referenced approach (separate collections)
// Users collection
{ "_id": "user123", "name": "John Doe" }

// Orders collection
{ "_id": "order1", "userId": "user123", "total": 99.99 }

// Query (application-level join)
user = db.users.findOne({ _id: "user123" })
orders = db.orders.find({ userId: "user123" })

---

Migration Patterns

// MIGRATING SQL TO NOSQL

// 1. Identify access patterns
// What queries do you run most frequently?
// Design NoSQL schema around those patterns

// 2. Denormalize
// SQL: Normalize to avoid redundancy
// NoSQL: Denormalize for read performance

// Before (SQL normalized):
// users: id, name
// addresses: id, user_id, street, city
// orders: id, user_id, total

// After (NoSQL denormalized):
{
    "_id": "user123",
    "name": "John",
    "addresses": [
        { "street": "123 Main", "city": "NYC" }
    ],
    "orders": [
        { "id": "o1", "total": 99.99 }
    ]
}

// 3. Handle relationships
// Option A: Embed (for 1:few, frequently accessed together)
// Option B: Reference (for 1:many, independent access)

// 4. Plan for eventual consistency
// May need to update multiple documents
// Consider Saga pattern for transactions

// DUAL-WRITE MIGRATION
// 1. Write to both databases
// 2. Read from old, verify against new
// 3. Switch reads to new
// 4. Remove writes to old

class MigrationService {
    void saveUser(User user) {
        sqlRepository.save(user);      // Old
        mongoRepository.save(user);    // New
        verify(user);                   // Compare
    }
}

---

Tips & Tricks

---