MySQL Schema Design

-- Products table
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    sku VARCHAR(100) UNIQUE,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Categories table
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE,
    parent_category_id INT NULL,
    FOREIGN KEY (parent_category_id) REFERENCES categories(id)
);

-- Product attributes (for variations)
CREATE TABLE product_attributes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    attribute_name VARCHAR(100),
    attribute_value VARCHAR(255),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Product images
CREATE TABLE product_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    image_url VARCHAR(500),
    is_primary BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id)
);
                

MySQL uses multiple related tables with foreign key constraints.

MongoDB Document Design

// Single product document in MongoDB
{
    "_id": ObjectId("65a1b2c3d4e5f67890123456"),
    "name": "Wireless Bluetooth Headphones",
    "description": "Noise-cancelling wireless headphones",
    "price": 129.99,
    "category": {
        "name": "Electronics",
        "slug": "electronics",
        "parentCategory": "Audio"
    },
    "sku": "WH-2026-BT",
    "stock": 45,
    "attributes": {
        "color": ["Black", "White", "Blue"],
        "connectivity": "Bluetooth 5.3",
        "batteryLife": "30 hours",
        "weight": "250g"
    },
    "images": [
        {
            "url": "/images/headphones-main.jpg",
            "alt": "Wireless Headphones Front View",
            "isPrimary": true
        },
        {
            "url": "/images/headphones-side.jpg",
            "alt": "Side View",
            "isPrimary": false
        }
    ],
    "variants": [
        {
            "name": "Premium Edition",
            "price": 159.99,
            "features": ["Extra Bass", "Carrying Case"]
        }
    ],
    "reviews": [
        {
            "userId": "user123",
            "rating": 5,
            "comment": "Excellent sound quality!",
            "date": ISODate("2025-12-15T10:30:00Z")
        }
    ],
    "createdAt": ISODate("2025-11-20T14:30:00Z"),
    "updatedAt": ISODate("2026-01-05T09:15:00Z")
}
                

MongoDB stores all related data in a single, flexible document.

MySQL Relational Design

-- Users table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Addresses table (one-to-many with users)
CREATE TABLE addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    street_address VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    zip_code VARCHAR(20),
    is_default BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Orders table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE,
    total_amount DECIMAL(10, 2),
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Order items (many-to-many relationship)
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);
                

MongoDB Embedded Design

// User document with embedded addresses
{
    "_id": ObjectId("65a1b2c3d4e5f67890123457"),
    "email": "john.doe@example.com",
    "passwordHash": "$2b$10$...",
    "name": {
        "first": "John",
        "last": "Doe"
    },
    "addresses": [
        {
            "type": "home",
            "street": "123 Main St",
            "city": "New York",
            "state": "NY",
            "zipCode": "10001",
            "isDefault": true
        },
        {
            "type": "work",
            "street": "456 Office Ave",
            "city": "New York",
            "state": "NY",
            "zipCode": "10002",
            "isDefault": false
        }
    ],
    "orders": [
        {
            "orderId": "ORD-2026-001",
            "date": ISODate("2026-01-05T14:30:00Z"),
            "status": "delivered",
            "items": [
                {
                    "productId": ObjectId("65a1b2c3d4e5f67890123456"),
                    "name": "Wireless Bluetooth Headphones",
                    "quantity": 1,
                    "price": 129.99
                },
                {
                    "productId": ObjectId("65a1b2c3d4e5f67890123458"),
                    "name": "USB-C Charging Cable",
                    "quantity": 2,
                    "price": 19.99
                }
            ],
            "shippingAddress": {
                "street": "123 Main St",
                "city": "New York",
                "state": "NY",
                "zipCode": "10001"
            },
            "total": 169.97
        }
    ],
    "preferences": {
        "newsletter": true,
        "theme": "dark",
        "currency": "USD"
    },
    "createdAt": ISODate("2025-06-10T09:15:00Z")
}
                

MySQL: Joins and Aggregation

-- Get user orders with product details
SELECT 
    u.email,
    o.order_number,
    o.created_at,
    oi.quantity,
    p.name as product_name,
    p.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE u.email = 'john@example.com'
ORDER BY o.created_at DESC;

-- Get total sales by category
SELECT 
    c.name as category,
    COUNT(DISTINCT o.id) as order_count,
    SUM(oi.quantity * oi.unit_price) as total_revenue
FROM categories c
JOIN products p ON c.id = p.category_id
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'delivered'
GROUP BY c.id
ORDER BY total_revenue DESC;

-- Find users with multiple addresses
SELECT 
    u.email,
    COUNT(a.id) as address_count
FROM users u
LEFT JOIN addresses a ON u.id = a.user_id
GROUP BY u.id
HAVING address_count > 1;
                

MongoDB: Aggregation Framework

// Get user orders with embedded approach
db.users.aggregate([
    {
        $match: {
            "email": "john@example.com"
        }
    },
    {
        $project: {
            email: 1,
            orders: 1
        }
    },
    {
        $unwind: "$orders"
    },
    {
        $sort: {
            "orders.date": -1
        }
    }
]);

// Get total sales by category (using embedded data)
db.users.aggregate([
    {
        $unwind: "$orders"
    },
    {
        $match: {
            "orders.status": "delivered"
        }
    },
    {
        $unwind: "$orders.items"
    },
    {
        $group: {
            _id: "$orders.items.category",
            orderCount: { $sum: 1 },
            totalRevenue: { 
                $sum: { 
                    $multiply: [
                        "$orders.items.quantity", 
                        "$orders.items.price"
                    ]
                }
            }
        }
    },
    {
        $sort: {
            totalRevenue: -1
        }
    }
]);

// Using $lookup for referenced data
db.orders.aggregate([
    {
        $lookup: {
            from: "users",
            localField: "userId",
            foreignField: "_id",
            as: "userDetails"
        }
    },
    {
        $unwind: "$userDetails"
    },
    {
        $project: {
            orderNumber: 1,
            total: 1,
            customerEmail: "$userDetails.email",
            customerName: {
                $concat: [
                    "$userDetails.name.first",
                    " ",
                    "$userDetails.name.last"
                ]
            }
        }
    }
]);
                

MySQL Scaling

• Vertical Scaling: Add more CPU, RAM, SSD to single server
• Read Replicas: Scale reads with multiple read-only copies
• Sharding: Horizontal partitioning (complex to implement)
• Connection Pooling: Manage database connections efficiently
• Query Optimization: Indexes, query rewriting, caching

-- Read replica setup
CHANGE MASTER TO
    MASTER_HOST='primary_host',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=107;

START SLAVE;

-- Partitioning example
CREATE TABLE orders_2026 (
    CHECK (YEAR(created_at) = 2026)
) INHERITS (orders);

-- Query using partition
SELECT * FROM orders WHERE created_at >= '2026-01-01';
                

MongoDB Scaling

• Horizontal Scaling: Add more servers to cluster
• Sharding: Built-in automatic data distribution
• Replica Sets: Automatic failover and data redundancy
• Chunk Balancing: Automatic data rebalancing
• Read/Write Concerns: Tunable consistency levels

// Sharded cluster configuration
sh.addShard("shard1/mongo1:27017,mongo2:27017,mongo3:27017");
sh.addShard("shard2/mongo4:27017,mongo5:27017,mongo6:27017");

// Enable sharding on database
sh.enableSharding("ecommerce");

// Shard collection by user location
sh.shardCollection("ecommerce.orders", { "shardKey": 1 });

// Create index on shard key
db.orders.createIndex({ "userRegion": 1, "_id": 1 });

// Read preference configuration
db.orders.find().readPref("secondary");
                

Step 1: Analysis and Planning

// Analyze MySQL schema
const analysis = {
    tablesToMigrate: [
        {
            table: 'products',
            strategy: 'denormalize',
            targetCollection: 'products',
            transformations: [
                'Combine product_attributes',
                'Embed product_images',
                'Include category data'
            ]
        },
        {
            table: 'users',
            strategy: 'embed_related',
            targetCollection: 'users',
            transformations: [
                'Embed addresses',
                'Include preferences',
                'Add activity history'
            ]
        }
    ],
    
    relationships: [
        {
            type: 'one-to-many',
            from: 'users',
            to: 'orders',
            strategy: 'embed_subset'
        },
        {
            type: 'many-to-many',
            from: 'orders',
            to: 'products',
            strategy: 'embed_references'
        }
    ],
    
    dataConsistency: {
        dualWritePeriod: '2_weeks',
        validationQueries: [
            'SELECT COUNT(*) FROM mysql_table',
            'db.mongodb_collection.countDocuments()'
        ]
    }
};
                

Step 2: Migration Script

// Migration script example
async function migrateProducts() {
    const mysqlPool = await createMySQLConnection();
    const mongoClient = await createMongoConnection();
    
    try {
        // 1. Get products with related data
        const [products] = await mysqlPool.execute(`
            SELECT p.*, 
                   c.name as category_name,
                   c.slug as category_slug,
                   GROUP_CONCAT(pi.image_url) as images,
                   JSON_OBJECTAGG(pa.attribute_name, 
                                 pa.attribute_value) as attributes
            FROM products p
            LEFT JOIN categories c ON p.category_id = c.id
            LEFT JOIN product_images pi ON p.id = pi.product_id
            LEFT JOIN product_attributes pa ON p.id = pa.product_id
            GROUP BY p.id
            LIMIT 1000
        `);
        
        // 2. Transform to MongoDB documents
        const mongoDocs = products.map(product => ({
            _id: new ObjectId(),
            name: product.name,
            description: product.description,
            price: product.price,
            category: {
                name: product.category_name,
                slug: product.category_slug
            },
            images: product.images 
                ? product.images.split(',').map(url => ({
                    url: url.trim(),
                    isPrimary: false
                }))
                : [],
            attributes: JSON.parse(product.attributes || '{}'),
            mysqlId: product.id, // Keep reference during transition
            migratedAt: new Date()
        }));
        
        // 3. Insert into MongoDB
        if (mongoDocs.length > 0) {
            await mongoClient
                .db('ecommerce')
                .collection('products')
                .insertMany(mongoDocs);
            
            console.log(`Migrated ${mongoDocs.length} products`);
        }
        
    } catch (error) {
        console.error('Migration failed:', error);
        throw error;
    } finally {
        await mysqlPool.end();
        await mongoClient.close();
    }
}