Our inventory management system will manage:

• Product details and product codes
• Product categories
• Supplier information
• Purchase prices and selling prices
• Available stock quantities
• Stock purchases and sales
• Low-stock reports
• Inventory valuation reports

Database name: inventoryDB

Collections:

• products
• suppliers
• stock_transactions

Open MongoDB Shell:

mongosh

Select the database:

use inventoryDB

Create the collections:

db.createCollection("products")
db.createCollection("suppliers")
db.createCollection("stock_transactions")

MongoDB creates a database when data is first stored in it. Collections can also be created automatically when documents are inserted.

If a collection already exists, continue using it rather than running its creation command again.

Example product document:

{
    product_id: 1001,
    product_name: "Wireless Keyboard",
    category: "Computer Accessories",
    brand: "TechPro",
    purchase_price: 600,
    selling_price: 850,
    stock_quantity: 25,
    reorder_level: 5,
    unit: "piece",
    active: true
}

The product_id is an application-level identifier. MongoDB also generates an automatic _id field when one is not provided.

The stock_quantity field stores the available quantity, while reorder_level specifies the minimum quantity at which a product should be considered low in stock.

Insert one product using insertOne():

db.products.insertOne({
    product_id: 1001,
    product_name: "Wireless Keyboard",
    category: "Computer Accessories",
    brand: "TechPro",
    purchase_price: 600,
    selling_price: 850,
    stock_quantity: 25,
    reorder_level: 5,
    unit: "piece",
    active: true
})

Insert multiple products:

db.products.insertMany([
    {
        product_id: 1002,
        product_name: "Wireless Mouse",
        category: "Computer Accessories",
        brand: "TechPro",
        purchase_price: 250,
        selling_price: 400,
        stock_quantity: 40,
        reorder_level: 10,
        unit: "piece",
        active: true
    },
    {
        product_id: 1003,
        product_name: "USB Cable",
        category: "Cables",
        brand: "ConnectPlus",
        purchase_price: 80,
        selling_price: 150,
        stock_quantity: 50,
        reorder_level: 10,
        unit: "piece",
        active: true
    },
    {
        product_id: 1004,
        product_name: "SSD 512GB",
        category: "Storage",
        brand: "FastStore",
        purchase_price: 2500,
        selling_price: 3200,
        stock_quantity: 8,
        reorder_level: 3,
        unit: "piece",
        active: true
    }
])

Run these sample insertions once. Repeating them unchanged will create additional records unless a unique index prevents duplicate product IDs.

Display all products:

db.products.find()

Display products in a readable format:

db.products.find().pretty()

Find a product by its ID:

db.products.findOne({
    product_id: 1001
})

Find products in a particular category:

db.products.find({
    category: "Computer Accessories"
})

Find products with selling prices below ₹500:

db.products.find({
    selling_price: { $lt: 500 }
})

Find products that are active:

db.products.find({
    active: true
})

Use updateOne() and $set to update selected product fields.

db.products.updateOne(
    { product_id: 1001 },
    {
        $set: {
            selling_price: 900,
            reorder_level: 8
        }
    }
)

The update changes the selling price and reorder level without replacing the entire document.

Display the updated product:

db.products.findOne({
    product_id: 1001
})

When new stock arrives, the available quantity increases. Use $inc to add the purchased quantity.

db.products.updateOne(
    { product_id: 1001 },
    {
        $inc: {
            stock_quantity: 10
        }
    }
)

If the previous quantity was 25, the new quantity becomes 35.

For a complete inventory system, also record the purchase in the stock transaction collection so the change can be audited later.

When a product is sold, the available stock must decrease. Use a filter to ensure that enough stock is available before updating the quantity.

db.products.updateOne(
    {
        product_id: 1001,
        stock_quantity: { $gte: 2 }
    },
    {
        $inc: {
            stock_quantity: -2
        }
    }
)

This example sells two units. The condition prevents the update from reducing stock below zero when fewer than two units are available.

Check matchedCount and modifiedCount in the result to confirm that the update succeeded.

In a real application, do not record a sale unless the stock update succeeds. Record both the inventory change and sale transaction reliably, using a multi-document transaction where supported or another carefully designed workflow.

Products with stock at or below their reorder level may need replenishment.

db.products.find({
    $expr: {
        $lte: [
            "$stock_quantity",
            "$reorder_level"
        ]
    }
})

The $expr operator allows a query to compare two fields in the same document.

This query selects products whose available quantity is less than or equal to the configured reorder level.

Insert supplier records:

db.suppliers.insertMany([
    {
        supplier_id: 201,
        supplier_name: "ABC Computers",
        mobile: "9876543210",
        city: "Patna",
        active: true
    },
    {
        supplier_id: 202,
        supplier_name: "Digital Wholesale",
        mobile: "9876543211",
        city: "Gaya",
        active: true
    }
])

Find suppliers from Patna:

db.suppliers.find({
    city: "Patna"
})

Supplier information is stored separately so that supplier details can be managed without repeating the full details in every product document.

A stock transaction records a purchase or sale and helps explain why inventory quantities changed.

Example purchase transaction:

db.stock_transactions.insertOne({
    transaction_id: 301,
    product_id: 1001,
    transaction_type: "PURCHASE",
    quantity: 10,
    unit_price: 600,
    transaction_date: new Date(),
    reference: "PUR-001"
})

Example sale transaction:

db.stock_transactions.insertOne({
    transaction_id: 302,
    product_id: 1001,
    transaction_type: "SALE",
    quantity: 2,
    unit_price: 900,
    transaction_date: new Date(),
    reference: "SALE-001"
})

In this design, quantity is stored as a positive number, while transaction_type identifies whether the transaction is a purchase or sale.

In a production system, the application should validate transaction data and ensure that the transaction history agrees with stock changes.

The following aggregation calculates the total inventory value using the purchase price multiplied by the current stock quantity.

db.products.aggregate([
    {
        $project: {
            product_name: 1,
            stock_quantity: 1,
            purchase_price: 1,
            inventory_value: {
                $multiply: [
                    "$purchase_price",
                    "$stock_quantity"
                ]
            }
        }
    }
])

To calculate the total inventory value for all products:

db.products.aggregate([
    {
        $group: {
            _id: null,
            total_inventory_value: {
                $sum: {
                    $multiply: [
                        "$purchase_price",
                        "$stock_quantity"
                    ]
                }
            }
        }
    }
])

This is a simple estimate based on the stored purchase price. Real accounting may require additional rules for taxes, discounts, landed costs, and inventory valuation methods.

Use aggregation to count products and sum stock quantities by category.

db.products.aggregate([
    {
        $group: {
            _id: "$category",
            totalProducts: { $sum: 1 },
            totalStock: {
                $sum: "$stock_quantity"
            }
        }
    },
    {
        $sort: {
            totalProducts: -1
        }
    }
])

This report groups products by category, counts the product documents, and adds their stock quantities.

Unique indexes help prevent duplicate application-level IDs in each collection.

db.products.createIndex(
    { product_id: 1 },
    { unique: true }
)

db.suppliers.createIndex(
    { supplier_id: 1 },
    { unique: true }
)

db.stock_transactions.createIndex(
    { transaction_id: 1 },
    { unique: true }
)

If duplicate identifiers already exist, index creation may fail. Check the existing documents before creating these indexes.

Indexes can also help frequently used queries, but they consume storage and add work to writes. Choose additional indexes based on actual query patterns.

1. Create the inventoryDB database.
2. Create the products, suppliers, and stock_transactions collections.
3. Insert at least five products with different categories.
4. Search for products by name, category, and selling price.
5. Update the selling price of a product.
6. Increase stock after recording a purchase.
7. Reduce stock only when enough quantity is available.
8. Find products that are at or below their reorder level.
9. Insert supplier and stock transaction records.
10. Calculate the total inventory value using aggregation.
11. Generate a category-wise stock report.
12. Create unique indexes on product, supplier, and transaction IDs.