Shopping Cart with Product Reservation

Metadata, courtesy of http://www.wpclipart.com/working/work_supplies/shopping_cart_racing.png.html

The traditional E-commerce shopping cart, allows the user to reserve products for a limited period of time, before releasing them back into inventory. This chapter will show you a possible way to model a shopping cart that reserves products using MongoDB.

Schema Observations

  • Ensures that the system cannot sell more quantity of a product than is available in the inventory.
  • Double bookkeeping means all transactions can be rolled back correctly.
  • Requires background process that expires carts correctly and returns any reserved products to the inventory.
  • Worst case scenario is that all the product inventory sits in the carts reserved until the carts expire and the reserved products are returned to the inventory.

Schema

The schema we will be using is a stripped down version of what a real e-commerce system would use, but serves to illustrate how to design a shopping cart with product reservation. We will be using four different collections. These are the carts, inventories, orders and products collections.

Let’s look at an example cart that contains one product.

{
    "_id": ObjectId("54fd7392742abeef6186a68e")
  , "state": "active"
  , "modifiedOn": ISODate("2015-03-11T12:03:42.615Z")
  , "products": [{
        "_id": ObjectId("54fd7392742abeef6186a68e")
      , "quantity": 1
      , "name": "JC Sneaker"
      , "price": 100
    }]
}

Below is an inventory document example that contains one reservation.

{
    "_id": ObjectId("54fd7392742abeef6186a68e")
  , "quantity": 999
  , "reservations": [{
        "_id": ObjectId("54fd7392742abeef6186a68e")
      , "quantity": 1
      , "createdOn": ISODate("2015-03-11T12:03:42.615Z")
    }]
}

The cart and inventory schemas are used to perform double bookkeeping, which avoids the possibility of selling more inventory than what is actually available.

In the example inventory document shown, the total available stock is always the sum of the quantity field at the top level of the document and all the quantity fields in the embedded array reservations. In this case the total available product is

totalAvailableProduct = 99 + 1;
totalReservedProduct = 1;

Only after the cart has been successfully checked out do we remove the cart reservation from the reservations array, thus reducing the available total stock.

If the cart is canceled or times out, we can easily return the stock for the product by reversing the reservations made for the cart using the entries in the reservations array.

The last two schemas are the products and order schemas where products contains the product information and orders contains the resulting document from a successful shopping cart checkout.

Below is an example product document.

{
    "_id": ObjectId("54fd7392742abeef6186a68e")
  , "name": "JC Sneaker"
  , "properties": {}
}

Example order schema with a single product.

{
    "_id": ObjectId("54fd7392742abeef6186a68e")
  , "total": 100
  , "shipping": {}
  , "payment": {}
  , "products": [{
        "_id": ObjectId("54fd7392742abeef6186a68e")
      , "quantity": 1
      , "name": "JC Sneaker"
      , "price": 100
    }]
}

Operations

Adding a product to the Shopping Cart

When the user attempts to reserve a specific quantity of a product, we need to perform 2 steps.

  1. First add the item to the products array in the cart document for the user, creating the cart in the process, if one does not already exist.
  2. Update the inventory document with the reservation, if there is enough quantity of the product available to cover the reservation.

When there is not enough inventory of the product available, we need to rollback the addition of the item to the cart.

Let’s start by adding a product to the shopping cart. We are making some assumptions here in order to simplify the code. Those assumptions are that we have a userId of 1 and a productId of 111445GB3;

var quantity = 1;
var userId = 1;
var productId = "111445GB3";

var col = db.getSisterDB("shop").carts;
col.update(
    { _id: userId, state: 'active' }
  , {
      $set: { modifiedOn: new Date() }
    , $push: { products: {
        _id: productId
      , quantity: quantity
      , name: "Simsong Mobile"
      , price: 1000
    }}
  }, true);

Example 1: Add product to shopping cart

The updated statement above performs an upsert. This means the document is created for the provided userId and state if one does not already exists.

The next step is to attempt to reserve the quantity of the product in the inventory collection.

var userId = 1;
var quantity = 1;

var col = db.getSisterDB("shop").inventories;
col.update({
    _id: productId, quantity: { $gte: quantity }
}, {
    $inc: { quantity: -quantity }
  , $push: {
    reservations: {
      quantity: quantity, _id: userId, createdOn: new Date()
    }
  }
});

Example 2: Reserve product inventory

This will add the cart reservation to the reservations array for the product document only if there is enough quantity to satisfy the reservation request.

If there is not enough product quantity available in the inventory, we need to rollback the addition of the product to the cart.

var userId = 1;
var quantity = 1;
var productId = "111445GB3";

var col = db.getSisterDB("shop").carts;
col.update({
  _id: userId
}, {
    $set: { modifiedOn: new Date() }
  , $pull: { products: { _id: productId }}
})

Example 3: Rollback product addition

We do this by finding the embedded document in the products array that has the _id field equal to the productId, and then removing it from the products array by using the $pull operator.

Updating the Reservation Quantity for a Product

If the user changes his mind about the amount of product he wishes to purchase, we need to update the amount of that product in the shopping cart as well as to ensure that there is sufficient inventory to cover the newly requested quantity.

First let’s update the quantity in the shopping cart. We start by fetching the existing quantity. Then, we need to calculate the delta (the change between the old and new quantity). Finally we update the cart with the changes.

var userId = 1;
var newQuantity = 2;
var productId = "111445GB3";

var col = db.getSisterDB("shop").carts;
var cart = db.findOne({
    _id: userId
  , "products._id": productId
  , status: "active"});
var oldQuantity = 0;

for(var i = 0; i < cart.products.length; i++) {
  if(cart.products[i]._id == productId) {
    oldQuantity = cart.products[i].quantity;
  }
}

var delta = newQuantity - oldQuantity;

col.update({
    _id: userId
  , "products._id": productId
  , status: "active"
}, {
  $set: {
      modifiedOn: new Date()
    , "products.$.quantity": newQuantity
  }
});

Example 4: Update product quantity in cart

Having updated the quantity in the cart, we now need to ensure there is enough product inventory available to cover the requested change in quantity. The additionally requested quantity is the difference between (newQuantity and oldQuantity).

var userId = 1;
var newQuantity = 2;
var productId = "111445GB3";

var col = db.getSisterDB("shop").products;
col.update({
    _id: productId
  , "reservations._id": userId
  , quantity: {
    $gte: delta
  }
}, {
  , $inc: { quantity: -delta }
    $set: {
      "reservations.$.quantity": newQuantity, modifiedOn: new Date()
    }
})

Example 5: Reserve additional product inventory

This correctly reserves more product inventory, or returns any non-needed product inventory.

  1. If, the delta is a negative number the $gte will always hold and the product quantity gets increased by the delta in effect returning product inventory.
  2. If delta is a positive number the $gte will only hold if inventory is equal to delta. If the $gte condition is meet, the inventory is decreased by delta quantity, in effect reserving more product inventory.

If there is not enough inventory to fulfill the new reservation, we need to rollback the change we made to the cart. We do that by re-applying the old quantity.

var userId = 1;
var oldQuantity = 1;
var productId = "111445GB3";

var col = db.getSisterDB("shop").carts;
col.update({
    _id: userId
  , "products._id": productId
  , status: "active"
}, {
  $set: {
      modifiedOn: new Date()
    , "products.$.quantity": oldQuantity
  }
});

Example 6: Rolling back quantity change request

Expiring Carts

It’s common for customers to have put items in a cart and then abandon the cart. This means there is a need for a process to expire carts that have been abandoned. For each expired cart we need to.

  1. Return the reserved items to the product inventory.
  2. Mark the cart as expired.

Below is a script that will look for any cart that has been sitting inactive for more than 30 minutes. Any carts that are older than 30 minutes have their contents returned to their respective product inventories and are then marked expired.

var cutOffDate = new Date();
cutOffDate.setMinutes(cutOffDate.getMinutes() - 30);

var cartsCol = db.getSisterDB("shop").carts;
var productCol = db.getSisterDB("shop").products;

var carts = cartsCol.find({
    modifiedOn: { $lte: cutOffDate }, state: 'active'
  });

while(carts.hasNext()) {
  var cart = carts.next();

  for(var i = 0; i < cart.products.length; i++) {
    var product = cart.products[i];

    productCol.update({
        _id: product._id
      , "reservations._id": cart._id
      , "reservations.quantity": product.quantity
    }, {
        $inc: { quantity: product.quantity }
      , $pull: { reservations: { _id: cart._id }}
    });
  }

  cartsCol.update({
    _id: cart._id
  }, {
    $set: { status: 'expired' }
  });
}

Example 7: Expiring shopping carts

For each cart, we iterate over all the products in the cart. For each of the products, we return the quantity to the product inventory and at the same time remove that cart from the reserved array of the product document.

After returning the inventory we set the status of the cart to expired. Notice that we don’t clean up the cart document itself. We are keeping the expired cart as historic data.

Checkout

The customer clicked the checkout button on the website and entered their payment details. It’s time to issue a purchase order, and clean up the cart and product reservations.

var userId = 1;

var cartsCol = db.getSisterDB("shop").carts;
var productCol = db.getSisterDB("shop").products;
var orderCol = db.getSisterDB("shop").orders;

var cart = cartsCol.findOne({ _id: userId, state: 'active' });

orderCol.insert({
    created_on: new Date()
  , shipping: {
      name: "Joe Dow"
    , address: "Some street 1, NY 11223"
  }
  , payment: { method: "visa", transaction_id: "2312213312XXXTD" }
  , products: cart.products
});

cartsCol.update({
  { _id: userId }
}, {
  $set: { status: 'complete' }
});

productCol.update({
  "reservations._id": userId
}, {
  $pull: { reservations: {_id: userId }}
}, false, true);

Example 8: Checking out shopping cart

We perform the following actions during checkout

  1. Add a finished order document to the orders collection representing the payed for order.
  2. Set the cart to done status
  3. Remove the cart from the reservations arrays of all products where it’s present using a multi update.

Indexes

For the carts collection we reference carts by the state field to find expired carts. We can add an index for this field to make the query more efficient.

var col = db.getSisterDB("catalog").carts;
col.ensureIndex({state:1});

Example 9: Create the state field index

For the inventories collection we look up embedded documents in the reservations array by the _id field. We need to create a multi key index for this field.

var col = db.getSisterDB("catalog").inventories;
col.ensureIndex({"reservations._id":1});

Example 10: Create the reservations._id field index

For the products and orders collections we do not need any other indexes than the default _id for our limited examples.

Scaling

Secondary Reads

All the operations against the cart are write operations so secondary reads are not useful for this schema as they might show out of date carts.

Sharding

Sharding the carts is fairly straight forward as each cart is self-contained so the easiest shard key for this is a hashed _id key. This allows for the writes to be distributed as randomly as possible across all the shards.

var admin = db.getSisterDB("admin");
db.runCommand({enableSharding:'shop'});
db.runCommand({
    shardCollection: 'shop.carts'
  , key: {_id:'hashed'}
});

Example 11: Hashed _id shard key

Performance

A simple exploration of the performance on a single machine with MongoDb 3.0 shows the difference between MMAP and WiredTiger for a narrow simulation using the schema simulation framework mongodb-schema-simulator.

Scenario

https://github.com/christkv/mongodb-schema-simulator/blob/master/examples/scripts/single_or_replset/cart_reservation/cart_5_item_reservation_successful_scenario.js

MongoDb runs locally on a MacBook Pro Retina 2015 with ssd and 16 gb ram. The simulation runs with the following parameters against a single mongodb instance under osx 10.10 Yosemite.

successfully checkout cart parameters

Parameters Value
processes 4
poolSize per process 50
type linear
Resolution in milliseconds 1000
Iterations run 25
Number of products in cart 5
Size of each product in bytes 1024
Number of users 1000
Execution strategy slicetime

MMAP

The MMAP engine is run using the default settings on MongoDB 3.0.1.

Successfully Checkout Cart

successfully checkout cart

Statistics Value
Runtime 88.241 seconds
Mean 46416.268 milliseconds
Standard Deviation 21937.134 milliseconds
75 percentile 62873.337 milliseconds
95 percentile 67818.018 milliseconds
99 percentile 70497.126 milliseconds
Minimum 20.034 milliseconds
Maximum 74220.014 milliseconds

WiredTiger

The WiredTiger engine is run using the default settings on MongoDB 3.0.1.

Successfully Checkout Cart

successfully checkout cart

Statistics Value
Runtime 108.916 seconds
Mean 63597.036 milliseconds
Standard Deviation 27672.244 milliseconds
75 percentile 83614.989 milliseconds
95 percentile 87405.772 milliseconds
99 percentile 89546.218 milliseconds
Minimum 41.414 milliseconds
Maximum 93681.381 milliseconds

This is an update heavy schema and this impacts WiredTiger more than MMAP due to a lack of in place updates in WiredTiger. Also note that the total runtime is higher than the25 seconds` of load we apply, pointing to the fact that the system is overloaded for this hardware configuration.

Notes

important

The reservation of items using an embedded array in an inventory document could lead to the size of the document growing a fair bit if there are a lot of shoppers reserving the same product at the same time. In these cases, we recommend the optimistic shopping cart pattern reviewed in the next chapter.