Section 11: Projects

📖 Bookstore Management System

Complete inventory, sales tracking, and customer management with MongoDB

Home› Section 11: Projects› Bookstore Management

🎯 Project Overview

Build a comprehensive bookstore management system that handles inventory tracking, sales processing, customer management, and business analytics. Perfect for understanding real-world MongoDB applications in retail.

Features:

  • ✅ Book inventory with real-time stock tracking
  • ✅ Sales order processing and history
  • ✅ Customer profiles and purchase history
  • ✅ Revenue and sales analytics
  • ✅ Low stock alerts and reorder system
  • ✅ Author and publisher management

📋 Database Schema Design

Books Collection

{
  _id: ObjectId("..."),
  isbn: "978-0-13-468599-1",
  title: "Clean Code",
  subtitle: "A Handbook of Agile Software Craftsmanship",
  
  authors: [
    {
      name: "Robert C. Martin",
      bio: "Software engineer and author"
    }
  ],
  
  publisher: {
    name: "Prentice Hall",
    location: "Upper Saddle River, NJ"
  },
  
  publication_date: ISODate("2008-08-01"),
  language: "English",
  pages: 464,
  
  categories: ["Programming", "Software Engineering", "Best Practices"],
  tags: ["clean code", "refactoring", "design patterns"],
  
  price: {
    list_price: 54.99,
    sale_price: 44.99,
    currency: "USD"
  },
  
  inventory: {
    stock_quantity: 25,
    warehouse_location: "Shelf A-12",
    reorder_level: 5,
    supplier: "Tech Books Wholesale"
  },
  
  ratings: {
    average: 4.7,
    count: 1245,
    distribution: {
      5: 892,
      4: 256,
      3: 67,
      2: 18,
      1: 12
    }
  },
  
  format: "Paperback",
  dimensions: {
    height: 9.0,
    width: 7.3,
    thickness: 1.1,
    unit: "inches"
  },
  
  status: "active",
  created_at: ISODate("2024-01-15"),
  updated_at: ISODate("2024-12-20")
}

Orders Collection

{
  _id: ObjectId("..."),
  order_number: "ORD-2024-001234",
  order_date: ISODate("2024-12-20T14:30:00Z"),
  
  customer: {
    customer_id: ObjectId("..."),
    name: "Alice Johnson",
    email: "alice@example.com",
    phone: "+1-555-0123"
  },
  
  items: [
    {
      book_id: ObjectId("..."),
      isbn: "978-0-13-468599-1",
      title: "Clean Code",
      quantity: 2,
      unit_price: 44.99,
      subtotal: 89.98
    },
    {
      book_id: ObjectId("..."),
      isbn: "978-0-201-61622-4",
      title: "The Pragmatic Programmer",
      quantity: 1,
      unit_price: 39.99,
      subtotal: 39.99
    }
  ],
  
  pricing: {
    subtotal: 129.97,
    tax: 10.40,
    shipping: 5.99,
    discount: 13.00,
    total: 133.36
  },
  
  shipping_address: {
    street: "123 Main St",
    city: "Boston",
    state: "MA",
    zip: "02101",
    country: "USA"
  },
  
  payment: {
    method: "credit_card",
    status: "completed",
    transaction_id: "TXN-ABC123"
  },
  
  status: "shipped",
  tracking_number: "1Z999AA10123456784",
  
  created_at: ISODate("2024-12-20T14:30:00Z"),
  updated_at: ISODate("2024-12-21T09:15:00Z")
}

Customers Collection

{
  _id: ObjectId("..."),
  customer_id: "CUST-2024-5678",
  name: "Alice Johnson",
  email: "alice@example.com",
  phone: "+1-555-0123",
  
  addresses: [
    {
      type: "shipping",
      street: "123 Main St",
      city: "Boston",
      state: "MA",
      zip: "02101",
      is_default: true
    }
  ],
  
  preferences: {
    favorite_categories: ["Programming", "Science Fiction"],
    newsletter: true,
    notifications: true
  },
  
  loyalty: {
    points: 450,
    tier: "gold",
    member_since: ISODate("2023-05-15")
  },
  
  purchase_history: {
    total_orders: 12,
    total_spent: 567.89,
    last_purchase: ISODate("2024-12-20")
  },
  
  created_at: ISODate("2023-05-15"),
  updated_at: ISODate("2024-12-20")
}

🔧 Setup Database & Sample Data

// Create database
use bookstore

// Create indexes for books
db.books.createIndex({ isbn: 1 }, { unique: true })
db.books.createIndex({ title: "text", "authors.name": "text" })
db.books.createIndex({ categories: 1 })
db.books.createIndex({ "price.sale_price": 1 })
db.books.createIndex({ "ratings.average": -1 })

// Create indexes for orders
db.orders.createIndex({ order_number: 1 }, { unique: true })
db.orders.createIndex({ "customer.customer_id": 1 })
db.orders.createIndex({ order_date: -1 })
db.orders.createIndex({ status: 1 })

// Create indexes for customers
db.customers.createIndex({ email: 1 }, { unique: true })
db.customers.createIndex({ customer_id: 1 }, { unique: true })

// Insert sample books
db.books.insertMany([
  {
    isbn: "978-0-13-468599-1",
    title: "Clean Code",
    authors: [{ name: "Robert C. Martin" }],
    publisher: { name: "Prentice Hall" },
    publication_date: ISODate("2008-08-01"),
    categories: ["Programming", "Software Engineering"],
    price: { list_price: 54.99, sale_price: 44.99, currency: "USD" },
    inventory: { stock_quantity: 25, reorder_level: 5 },
    ratings: { average: 4.7, count: 1245 },
    format: "Paperback",
    status: "active",
    created_at: ISODate()
  },
  {
    isbn: "978-0-201-61622-4",
    title: "The Pragmatic Programmer",
    authors: [
      { name: "Andrew Hunt" },
      { name: "David Thomas" }
    ],
    publisher: { name: "Addison-Wesley" },
    publication_date: ISODate("1999-10-30"),
    categories: ["Programming", "Software Development"],
    price: { list_price: 49.99, sale_price: 39.99, currency: "USD" },
    inventory: { stock_quantity: 18, reorder_level: 5 },
    ratings: { average: 4.8, count: 987 },
    format: "Paperback",
    status: "active",
    created_at: ISODate()
  },
  {
    isbn: "978-0-596-52068-7",
    title: "JavaScript: The Good Parts",
    authors: [{ name: "Douglas Crockford" }],
    publisher: { name: "O'Reilly Media" },
    publication_date: ISODate("2008-05-08"),
    categories: ["Programming", "JavaScript", "Web Development"],
    price: { list_price: 29.99, sale_price: 24.99, currency: "USD" },
    inventory: { stock_quantity: 8, reorder_level: 10 },
    ratings: { average: 4.3, count: 654 },
    format: "Paperback",
    status: "active",
    created_at: ISODate()
  }
])

📦 Inventory Management

1. Add New Book

db.books.insertOne({
  isbn: "978-0-13-235088-4",
  title: "Design Patterns",
  authors: [
    { name: "Erich Gamma" },
    { name: "Richard Helm" },
    { name: "Ralph Johnson" },
    { name: "John Vlissides" }
  ],
  publisher: { name: "Addison-Wesley" },
  publication_date: ISODate("1994-10-21"),
  categories: ["Programming", "Design Patterns", "Object-Oriented"],
  price: { list_price: 64.99, sale_price: 54.99, currency: "USD" },
  inventory: { stock_quantity: 15, reorder_level: 5 },
  ratings: { average: 4.6, count: 532 },
  format: "Hardcover",
  status: "active",
  created_at: ISODate()
})

2. Update Stock After Sale

// Decrease stock after selling 2 copies
db.books.updateOne(
  { isbn: "978-0-13-468599-1" },
  { 
    $inc: { "inventory.stock_quantity": -2 },
    $set: { updated_at: ISODate() }
  }
)

// Atomic operation - safe for concurrent sales
db.books.findOneAndUpdate(
  { 
    isbn: "978-0-13-468599-1",
    "inventory.stock_quantity": { $gte: 2 }
  },
  { 
    $inc: { "inventory.stock_quantity": -2 }
  },
  { returnDocument: "after" }
)

3. Restock Inventory

// Add 50 new copies
db.books.updateOne(
  { isbn: "978-0-13-468599-1" },
  { 
    $inc: { "inventory.stock_quantity": 50 },
    $set: { updated_at: ISODate() }
  }
)

4. Low Stock Alert

// Find books that need reordering
db.books.find({
  $expr: { 
    $lte: ["$inventory.stock_quantity", "$inventory.reorder_level"] 
  },
  status: "active"
}).sort({ "inventory.stock_quantity": 1 })

// Output: Books with stock <= reorder_level

5. Update Price

// Update sale price with 20% discount
db.books.updateOne(
  { isbn: "978-0-13-468599-1" },
  { 
    $set: { 
      "price.sale_price": 35.99,
      updated_at: ISODate()
    }
  }
)

// Bulk price update for category
db.books.updateMany(
  { categories: "Programming" },
  { 
    $mul: { "price.sale_price": 0.9 }  // 10% off
  }
)

💰 Sales Operations

1. Create New Order

// Create order with transaction
const session = db.getMongo().startSession();
session.startTransaction();

try {
  // Create order
  const orderResult = db.orders.insertOne({
    order_number: "ORD-2024-001234",
    order_date: ISODate(),
    customer: {
      customer_id: ObjectId("..."),
      name: "Alice Johnson",
      email: "alice@example.com"
    },
    items: [
      {
        isbn: "978-0-13-468599-1",
        title: "Clean Code",
        quantity: 2,
        unit_price: 44.99,
        subtotal: 89.98
      }
    ],
    pricing: {
      subtotal: 89.98,
      tax: 7.20,
      shipping: 5.99,
      total: 103.17
    },
    status: "pending",
    created_at: ISODate()
  }, { session });
  
  // Decrease book stock
  db.books.updateOne(
    { isbn: "978-0-13-468599-1" },
    { $inc: { "inventory.stock_quantity": -2 } },
    { session }
  );
  
  // Update customer purchase history
  db.customers.updateOne(
    { email: "alice@example.com" },
    { 
      $inc: { 
        "purchase_history.total_orders": 1,
        "purchase_history.total_spent": 103.17
      },
      $set: { "purchase_history.last_purchase": ISODate() }
    },
    { session }
  );
  
  session.commitTransaction();
  print("Order created successfully!");
  
} catch (error) {
  session.abortTransaction();
  print("Order failed: " + error);
} finally {
  session.endSession();
}

2. Update Order Status

// Mark order as shipped
db.orders.updateOne(
  { order_number: "ORD-2024-001234" },
  { 
    $set: { 
      status: "shipped",
      tracking_number: "1Z999AA10123456784",
      updated_at: ISODate()
    }
  }
)

3. Cancel Order (with Stock Return)

// Cancel order and return stock
const order = db.orders.findOne({ order_number: "ORD-2024-001234" });

if (order.status === "pending") {
  // Return stock
  order.items.forEach(item => {
    db.books.updateOne(
      { isbn: item.isbn },
      { $inc: { "inventory.stock_quantity": item.quantity } }
    );
  });
  
  // Update order status
  db.orders.updateOne(
    { order_number: "ORD-2024-001234" },
    { $set: { status: "cancelled", updated_at: ISODate() } }
  );
}

👥 Customer Management

1. Register New Customer

db.customers.insertOne({
  customer_id: "CUST-2024-5678",
  name: "Bob Smith",
  email: "bob@example.com",
  phone: "+1-555-0124",
  addresses: [
    {
      type: "shipping",
      street: "456 Oak Ave",
      city: "Cambridge",
      state: "MA",
      zip: "02138",
      is_default: true
    }
  ],
  preferences: {
    favorite_categories: ["Science Fiction", "Fantasy"],
    newsletter: true
  },
  loyalty: {
    points: 0,
    tier: "bronze",
    member_since: ISODate()
  },
  purchase_history: {
    total_orders: 0,
    total_spent: 0.00
  },
  created_at: ISODate()
})

2. Update Loyalty Points

// Add points after purchase (1 point per dollar)
db.customers.updateOne(
  { email: "alice@example.com" },
  { 
    $inc: { "loyalty.points": 103 }  // $103.17 order
  }
)

// Check tier upgrade
db.customers.updateOne(
  { 
    email: "alice@example.com",
    "loyalty.points": { $gte: 500 }
  },
  { 
    $set: { "loyalty.tier": "gold" }
  }
)

3. Find Customer Purchase History

db.orders.find({
  "customer.email": "alice@example.com"
}).sort({ order_date: -1 }).limit(10)

📊 Sales Analytics

1. Total Sales by Month

db.orders.aggregate([
  { $match: { 
    status: { $in: ["completed", "shipped"] },
    order_date: { 
      $gte: ISODate("2024-01-01"),
      $lt: ISODate("2025-01-01")
    }
  }},
  { $group: {
    _id: { 
      year: { $year: "$order_date" },
      month: { $month: "$order_date" }
    },
    total_revenue: { $sum: "$pricing.total" },
    order_count: { $sum: 1 }
  }},
  { $sort: { "_id.year": 1, "_id.month": 1 } }
])

2. Best Selling Books

db.orders.aggregate([
  { $match: { status: { $in: ["completed", "shipped"] } } },
  { $unwind: "$items" },
  { $group: {
    _id: "$items.isbn",
    title: { $first: "$items.title" },
    total_sold: { $sum: "$items.quantity" },
    revenue: { $sum: "$items.subtotal" }
  }},
  { $sort: { total_sold: -1 } },
  { $limit: 10 }
])

3. Revenue by Category

db.orders.aggregate([
  { $match: { status: { $in: ["completed", "shipped"] } } },
  { $unwind: "$items" },
  { $lookup: {
    from: "books",
    localField: "items.isbn",
    foreignField: "isbn",
    as: "book_info"
  }},
  { $unwind: "$book_info" },
  { $unwind: "$book_info.categories" },
  { $group: {
    _id: "$book_info.categories",
    revenue: { $sum: "$items.subtotal" },
    books_sold: { $sum: "$items.quantity" }
  }},
  { $sort: { revenue: -1 } }
])

4. Customer Lifetime Value

db.customers.find({
  "purchase_history.total_orders": { $gte: 5 }
}).sort({ "purchase_history.total_spent": -1 }).limit(10)

5. Average Order Value

db.orders.aggregate([
  { $match: { status: { $in: ["completed", "shipped"] } } },
  { $group: {
    _id: null,
    avg_order_value: { $avg: "$pricing.total" },
    total_orders: { $sum: 1 },
    total_revenue: { $sum: "$pricing.total" }
  }}
])

💻 Interactive Live Console

Try these bookstore queries in the live console!

📖 Bookstore Query Console
Output:
Ready to execute queries...

🎯 Real-World Use Cases

Use Case 1: Daily Sales Report

db.orders.aggregate([
  { $match: { 
    order_date: { 
      $gte: ISODate("2024-12-20T00:00:00Z"),
      $lt: ISODate("2024-12-21T00:00:00Z")
    }
  }},
  { $group: {
    _id: null,
    total_orders: { $sum: 1 },
    total_revenue: { $sum: "$pricing.total" },
    avg_order_value: { $avg: "$pricing.total" }
  }},
  { $project: {
    _id: 0,
    total_orders: 1,
    total_revenue: { $round: ["$total_revenue", 2] },
    avg_order_value: { $round: ["$avg_order_value", 2] }
  }}
])

Use Case 2: Personalized Recommendations

// Find similar books based on customer's favorites
const customer = db.customers.findOne({ email: "alice@example.com" });
const favCategories = customer.preferences.favorite_categories;

db.books.find({
  categories: { $in: favCategories },
  "ratings.average": { $gte: 4.0 },
  status: "active"
}).sort({ "ratings.average": -1 }).limit(5)

Use Case 3: Automatic Reorder Alert

// Generate purchase order for low stock books
db.books.aggregate([
  { $match: {
    $expr: { 
      $lte: ["$inventory.stock_quantity", "$inventory.reorder_level"] 
    },
    status: "active"
  }},
  { $project: {
    isbn: 1,
    title: 1,
    current_stock: "$inventory.stock_quantity",
    reorder_level: "$inventory.reorder_level",
    suggested_order_qty: { 
      $subtract: [20, "$inventory.stock_quantity"] 
    },
    supplier: "$inventory.supplier"
  }}
])

Use Case 4: Customer Segmentation

db.customers.aggregate([
  { $bucket: {
    groupBy: "$purchase_history.total_spent",
    boundaries: [0, 100, 500, 1000, 5000],
    default: "5000+",
    output: {
      count: { $sum: 1 },
      customers: { $push: "$name" }
    }
  }}
])

// Output segments: 
// $0-$100: casual buyers
// $100-$500: regular customers
// $500-$1000: loyal customers
// $1000+: VIP customers