Relational vs. Document Databases: A Practical Guide

# The document approach: everything together, naturally
blog_post_document = {
    "_id": "post_123",
    "title": "Understanding Database Trade-offs",
    "author": "Alice",
    "content": "Let's explore when to use each database type...",
    "published_at": "2024-01-15",
    "comments": [
        {
            "author": "Bob",
            "text": "Great explanation!",
            "posted_at": "2024-01-15T10:30:00Z",
            "likes": 5
        },
        {
            "author": "Carol",
            "text": "This clarified a lot for me.",
            "posted_at": "2024-01-15T11:45:00Z",
            "likes": 3
        }
    ],
    "tags": ["databases", "architecture", "nosql"]
}
 
# One query, one document, everything you need
def get_post_with_comments(db, post_id):
    return db.posts.find_one({"_id": post_id})
    # Done! No joins, no assembly required.

# The relational approach: data split across tables
def get_post_with_comments_relational(db, post_id):
    cursor = db.cursor()
    
    # First, get the post
    cursor.execute("SELECT * FROM posts WHERE id = ?", (post_id,))
    post = cursor.fetchone()
    
    # Then, get the comments
    cursor.execute("SELECT * FROM comments WHERE post_id = ?", (post_id,))
    comments = cursor.fetchall()
    
    # Then, get the tags
    cursor.execute("""
        SELECT t.name FROM tags t
        JOIN post_tags pt ON t.id = pt.tag_id
        WHERE pt.post_id = ?
    """, (post_id,))
    tags = cursor.fetchall()
    
    # Now assemble them in application code
    return {
        "post": post,
        "comments": comments,
        "tags": [t[0] for t in tags]
    }

# The pain of many-to-many in documents
def get_user_feed_document_style(db, user_id):
    """
    Get posts from all users that this user follows.
    In a document database, this is painful.
    """
    # Step 1: Get the user to find who they follow
    user = db.users.find_one({"_id": user_id})
    followed_ids = user.get("following", [])
    
    # Step 2: Get each followed user's posts (N queries!)
    all_posts = []
    for followed_id in followed_ids:
        followed_user = db.users.find_one({"_id": followed_id})
        all_posts.extend(followed_user.get("posts", []))
    
    # Step 3: For each post, get the author's current name and photo
    # (in case they changed since the post was created)
    enriched_posts = []
    for post in all_posts:
        author = db.users.find_one({"_id": post["author_id"]})
        post["author_name"] = author["name"]
        post["author_photo"] = author["photo"]
        enriched_posts.append(post)
    
    # This is slow, complex, and error-prone!
    return sorted(enriched_posts, key=lambda p: p["timestamp"], reverse=True)

# The elegance of many-to-many in SQL
def get_user_feed_relational(db, user_id):
    """
    Get posts from all users that this user follows.
    One query, efficiently executed by the database.
    """
    cursor = db.cursor()
    cursor.execute("""
        SELECT 
            p.id,
            p.content,
            p.timestamp,
            u.name AS author_name,
            u.photo AS author_photo
        FROM posts p
        JOIN users u ON p.author_id = u.id
        JOIN follows f ON f.followed_id = u.id
        WHERE f.follower_id = ?
        ORDER BY p.timestamp DESC
        LIMIT 50
    """, (user_id,))
    
    return cursor.fetchall()
    # One query. The database handles the complexity.

# Schema-on-read: handling evolving data gracefully
def process_user_profile(user_doc):
    """
    Handle different versions of user documents.
    Old documents have 'name', new ones have 'first_name' and 'last_name'.
    """
    if "first_name" in user_doc:
        # New format
        full_name = f"{user_doc['first_name']} {user_doc['last_name']}"
    elif "name" in user_doc:
        # Old format - split the name
        parts = user_doc["name"].split(" ", 1)
        full_name = user_doc["name"]
        # Optionally migrate on read
        user_doc["first_name"] = parts[0]
        user_doc["last_name"] = parts[1] if len(parts) > 1 else ""
    else:
        full_name = "Unknown"
    
    return full_name
 
 
# Example: old and new documents coexist peacefully
old_user = {"_id": "u1", "name": "Alice Johnson", "email": "alice@example.com"}
new_user = {"_id": "u2", "first_name": "Bob", "last_name": "Smith", "email": "bob@example.com"}
 
print(process_user_profile(old_user))  # "Alice Johnson"
print(process_user_profile(new_user))  # "Bob Smith"

# The danger of no schema: silent corruption
def save_user_buggy(db, user_data):
    """
    A bug in the code might save bad data without anyone noticing.
    """
    # Oops! Someone typo'd 'email' as 'emial'
    db.users.insert_one({
        "name": user_data["name"],
        "emial": user_data["email"],  # Typo! No schema to catch it.
    })
    # The insert succeeds. The bug hides until someone queries 'email'.
 
 
# With a relational schema, this would fail immediately
# CREATE TABLE users (
#     id SERIAL PRIMARY KEY,
#     name TEXT NOT NULL,
#     email TEXT NOT NULL  -- Typo'd 'emial' would cause an error!
# );

# Common schema changes in SQL
schema_changes = """
-- Adding a column is usually instant (just metadata change)
ALTER TABLE users ADD COLUMN first_name TEXT;
 
-- Backfilling data might be slow on huge tables
UPDATE users SET first_name = split_part(name, ' ', 1);
 
-- But you can do lazy migration instead (just like documents!)
-- Leave first_name NULL and fill it when reading:
"""
 
def get_user_with_lazy_migration(db, user_id):
    """
    Lazy migration: same pattern works in relational DBs!
    """
    cursor = db.cursor()
    cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
    user = cursor.fetchone()
    
    if user["first_name"] is None and user["name"]:
        # Migrate on read, just like a document database
        first_name = user["name"].split(" ")[0]
        cursor.execute(
            "UPDATE users SET first_name = ? WHERE id = ?",
            (first_name, user_id)
        )
        user["first_name"] = first_name
    
    return user

# The locality trade-off illustrated
class DocumentLocalityExample:
    """
    Demonstrates when document locality helps and hurts.
    """
    
    def locality_helps(self, db, post_id):
        """
        Loading a complete blog post for display.
        One read, everything included, fast!
        """
        post = db.posts.find_one({"_id": post_id})
        # post contains title, content, author, comments, tags, etc.
        return render_blog_post(post)
    
    def locality_hurts_on_read(self, db):
        """
        Just need post titles for a listing page.
        But we load entire documents including all comments!
        """
        # This loads way more data than we need
        posts = db.posts.find({}, limit=20)
        return [p["title"] for p in posts]
        # Wasteful: loaded 20 full posts just for titles
    
    def locality_hurts_on_write(self, db, post_id, new_comment):
        """
        Adding one comment might rewrite the entire document.
        """
        db.posts.update_one(
            {"_id": post_id},
            {"$push": {"comments": new_comment}}
        )
        # Internally, the database might:
        # 1. Read the entire document
        # 2. Add the comment
        # 3. Write the entire document to a new location
        # 4. Update the index to point to the new location

# PostgreSQL with JSON: the hybrid approach
hybrid_schema = """
-- Mostly relational, with a JSON column for flexibility
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category_id INTEGER REFERENCES categories(id),
    
    -- Flexible attributes that vary by product type
    attributes JSONB NOT NULL DEFAULT '{}'
);
 
-- You can index inside the JSON!
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- And query it naturally
SELECT name, price, attributes->>'color' as color
FROM products
WHERE attributes->>'size' = 'large'
  AND category_id = 5;
"""
 
def add_product_hybrid(db, name, price, category_id, **attributes):
    """
    Best of both worlds: structured core fields, flexible attributes.
    """
    import json
    cursor = db.cursor()
    cursor.execute("""
        INSERT INTO products (name, price, category_id, attributes)
        VALUES (?, ?, ?, ?)
    """, (name, price, category_id, json.dumps(attributes)))
    db.commit()
 
 
# Usage: different products have different attributes
add_product_hybrid(db, "T-Shirt", 29.99, 1, 
                   color="blue", size="large", material="cotton")
add_product_hybrid(db, "Laptop", 999.99, 2,
                   ram="16GB", storage="512GB SSD", screen_size="15.6 inch")
# The schema doesn't need to know about colors or RAM!

# MongoDB's $lookup: joins in a document database
mongodb_aggregation = """
// Find all orders with customer details embedded
db.orders.aggregate([
    {
        $lookup: {
            from: "customers",
            localField: "customer_id",
            foreignField: "_id",
            as: "customer"
        }
    },
    {
        $unwind: "$customer"
    },
    {
        $project: {
            order_id: 1,
            total: 1,
            customer_name: "$customer.name",
            customer_email: "$customer.email"
        }
    }
])
"""
 
# This isn't as elegant as SQL, but it works!

def choose_database_model(requirements: dict) -> str:
    """
    A decision helper for choosing your data model.
    """
    score_document = 0
    score_relational = 0
    
    # Question 1: Data structure
    if requirements.get("hierarchical_data"):
        score_document += 2
        print("✓ Hierarchical data favors documents")
    
    if requirements.get("many_to_many_relationships"):
        score_relational += 2
        print("✓ Many-to-many relationships favor relational")
    
    # Question 2: Query patterns
    if requirements.get("load_entire_entity_at_once"):
        score_document += 1
        print("✓ Loading complete entities favors documents")
    
    if requirements.get("complex_queries_across_entities"):
        score_relational += 2
        print("✓ Complex cross-entity queries favor relational")
    
    # Question 3: Schema stability
    if requirements.get("schema_changes_frequently"):
        score_document += 1
        print("✓ Frequent schema changes favor documents")
    
    if requirements.get("data_integrity_critical"):
        score_relational += 1
        print("✓ Data integrity requirements favor relational")
    
    # Question 4: Scale and performance
    if requirements.get("horizontal_scaling_priority"):
        score_document += 1
        print("✓ Horizontal scaling slightly favors documents")
    
    if requirements.get("complex_transactions_needed"):
        score_relational += 1
        print("✓ Complex transactions favor relational")
    
    # Decision
    print(f"\nDocument score: {score_document}")
    print(f"Relational score: {score_relational}")
    
    if score_document > score_relational + 2:
        return "Document database recommended"
    elif score_relational > score_document + 2:
        return "Relational database recommended"
    else:
        return "Consider a hybrid approach or test both"
 
 
# Example: E-commerce product catalog
ecommerce_requirements = {
    "hierarchical_data": True,  # Products with nested attributes
    "many_to_many_relationships": True,  # Products ↔ Categories, Orders ↔ Products
    "load_entire_entity_at_once": True,  # Product pages load full product
    "complex_queries_across_entities": True,  # Analytics, reporting
    "schema_changes_frequently": True,  # New product types, new attributes
    "data_integrity_critical": True,  # Inventory, pricing
}
 
result = choose_database_model(ecommerce_requirements)
print(f"\nRecommendation: {result}")
# This would likely suggest a hybrid approach - 
# and indeed, many e-commerce platforms use PostgreSQL with JSONB!