Retail Reference Architecture Part 2: Approaches to Inventory Optimization

Series:

1. Building a Flexible, Searchable, Low-Latency Product Catalog
2. Approaches to Inventory Optimization
3. Query Optimization and Scaling
4. Recommendations and Personalizations

---

In part one of our series on retail reference architecture we looked at some best practices for how a high-volume retailer might use MongoDB as the persistence layer for a large product catalog. This involved index, schema, and query optimization to ensure our catalog could support features like search, per-store pricing and browsing with faceted search in a highly performant manner. Over the next two posts we will be looking at approaches to similar types of optimization, but applied to an entirely different aspect of retail business, inventory.

A solid central inventory system that is accessible across a retailer’s stores and applications is a large part of the foundation needed for improving and enriching the customer experience. Here are just a few of the features that a retailer might want to enable:

• Reliably check real-time product availability.
• Give the option for in-store pick-up at a particular location.
• Detect the need for intra-day replenishment if there is a run on an item.

The Problem with Inventory Systems

These are features that seem basic but they present real challenges given the types of legacy inventory systems commonly used by major retailers. In these systems, individual stores keep their own field inventories, which then report data back to the central RDBMS at a set time interval, usually nightly. That RDBMS then reconciles and categorizes all of the data received that day and makes it available for operations like analytics, reporting, as well as consumption by external and internal applications. Commonly there is also a caching layer present between the RDBMS and any applications, as relational databases are often not well-suited to the transaction volume required by such clients, particularly if we are talking about a consumer-facing mobile or web app.

So the problem with the status quo is pretty clear. The basic setup of these systems isn’t suited to providing a continually accurate snapshot of how much inventory we have and where that inventory is located. In addition, we also have the increased complexity involved in maintaining multiple systems, i.e. caching, persistence, etc. MongoDB, however, is ideal for supporting these features with a high degree of accuracy and availability, even if our individual retail stores are very geographically dispersed.

Design Principles

To begin, we determined that the inventory system in our retail reference architecture needed to do the following:

• Provide a single view of inventory, accessible by any client at any time.
• Be usable by any system that needs inventory data.
• Handle a high-volume, read-dominated workload, i.e. inventory checks.
• Handle a high volume of real-time writes, i.e. inventory updates.
• Support bulk writes to refresh the system of record.
• Be geographically distributed.
• Remain horizontally scalable as the number of stores or items in inventory grows.

In short, what we needed was to build a high performance, horizontally scalable system where stores and clients over a large geographic area could transact in real-time with MongoDB to view and update inventory.

Stores Schema

Since a primary requirement of our use case was to maintain a centralized, real-time view of total inventory per store, we first needed to create the schema for a stores collection so that we had locations to associate our inventory with. The result is a fairly straightforward document per store:

{
	“_id”:ObjectId(“78s89453d8chw28h428f2423”),
	“className”:”catalog.Store”,
	“storeId”:”store100”,
	“name”:”Bessemer Store”,
	“address”:{
		“addr1”:”1 Main St.”,
		“city”:”Bessemer”,
		“state”:”AL”,
		“zip”:”12345”,
		“country”:”USA”
	},
	“location”:[-86.95444, 33.40178],
	…
}

We then created the following indices to optimize the most common types of reads on our store data:

• {“storeId”:1},{“unique”:true}: Get inventory for a specific store.
• {“name”:1}: Get a store by name.
• {“address.zip”:1}: Get all stores within a zip code, i.e. store locator.
• {“location”: 2dsphere}: Get all stores around a specified geolocation.

Of these, the location index is especially useful for our purposes, as it allows us to query stores by proximity to a location, e.g. a user looking for the nearest store with a product in stock. To take advantage of this in a sharded environment, we used a geoNear command that retrieves the documents whose ‘location’ attribute is within a specified distance of a given point, sorted nearest first:

db.runCommand({
	geoNear:“stores”,
	near:{
		type:”Point”,
		coordinates:[-82.8006,40.0908], //GeoJSON or coordinate pair
		maxDistance:10000.0, //in meters
		spherical:true //required for 2dsphere indexes
	}
}) 

This schema gave us the ability to locate our objects, but the much bigger challenge was tracking and managing the inventory in those stores.

Inventory Data Model

Now that we had stores to associate our items with, we needed to create an inventory collection to track the actual inventory count of each item and all its variants. Some trade-offs were required for this, however. To both minimize the number of roundtrips to the database, as well as mitigate application-level joins, we decided to duplicate data from the stores collection into the inventory collection. The document we came up with looked like this:

{
	“_id”:”902372093572409542jbf42r2f2432”,
	“storeId”:”store100”,
	“location”:[-86.95444, 33.40178],
	“productId”:”20034”,
	“vars”:[
		{“sku”:”sku1”, “quantity”:”5”},
		{“sku”:”sku2”, “quantity”:”23”},
		{“sku”:”sku3”, “quantity”:”2”},
		…
	]
}

Notice first that we included both the ‘storeId’ and ‘location’ attribute in our inventory document. Clearly the ‘storeId’ is necessary so that we know which store has what items, but what happens when we are querying for inventory near the user? Both the inventory data and store location data are required to complete the request. By adding geolocation data to the inventory document we eliminate the need to execute a separate query to the stores collection, as well as a join between the stores and inventory collections.

For our schema we also decided to represent inventory in our documents at the productId level. As was noted in part one of our retail reference architecture series, each product can have many, even thousands of variants, based on size, color, style, etc., and all these variants must be represented in our inventory. So the question is should we favor larger documents that contain a potentially large variants collection, or many more documents that represent inventory at the variant level? In this case, we favored larger documents to minimize the amount of data duplication, as well as decrease the total number of documents in our inventory collection that would need to be queried or updated.

Next, we created our indices:

• {storeId:1}: Get all items in inventory for a specific store.
• {productId:1},{storeId:1}: Get inventory of a product for a specific store.
• {productId:1},{location:”2dsphere”}: Get all inventory of a product within a specific distance.

It’s worth pointing out here that we chose not to include an index with ‘vars.sku’. The reason for this is that it wouldn’t actually buy us very much, since we are already able to do look ups in our inventory based on ‘productID’. So, for example, a query to get a specific variant sku that looks like this:

db.inventory.find(
	{
		“storeId”:”store100”,
		“productId”:“20034”,
		“vars.sku”:”sku11736”
	},
	{“vars.$”:1}
)

Doesn’t actually benefit much from an added index on ‘vars.sku’. In this case, our index on ‘productId’ is already giving us access to the document, so an index on the variant is unnecessary. In addition, because the variants array can have thousands of entries, an index on it could potentially take up a large block in memory, and consequently decrease the number of documents stored in memory, meaning slower queries. All things considered, an unacceptable trade-off, given our goals.

So what makes this schema so good anyhow? We’ll take a look in our next post at some of the features this approach makes available to our inventory system.

Learn More

To discover how you can re-imagine the retail experience with MongoDB, read our white paper. In this paper, you'll learn about the new retail challenges and how MongoDB addresses them.

---

Learn more about how leading brands differentiate themselves with technologies and processes that enable the omni-channel retail experience.

Read our guide on the digitally oriented consumer

---

<< Read Part 1

Read Part 3 >>