A Better Way to Store Ecommerce Product Information

Amazon Web Services DynamoDB, MongoDB, Couchbase, and MarkLogic are all examples of NoSQL databases that may offer a better way to store product catalog information.

How an ecommerce site stores and accesses product information can have a significant impact on site performance.

Online retail businesses, small and large, may benefit from a popular database technology that could offer a better way to store product information. To help understand this technology, which is called NoSQL, it can help to describe just how complex product information can be.

The Product Attribute Problem

Many — if not most — ecommerce platforms store product information in a relational database, where information is kept in tables similar to a spreadsheet. Imagine, as an example, a product listing for a small, red t-shirt. The shirt might have a unique ID, a stock keeping unit (SKU), a title, a size, and a color.

Each of these five attributes would take up a column in the database table. A row in the table represents the complete set of product information.

For a site that sells only t-shirts, this might work well. You could imagine additional rows in the database table representing additional shirts, say the “Awesome Shirt,” in four sizes and two colors.

The database query to display this product information would be relatively simple to write and fast to load. Not much of a problem. But what if this t-shirt retailer also wants to sell pants?

Pants have a different set of attributes. Size is not measured in small, medium, and large, but typically in waist and inseam.

To solve this problem, one could add columns to the table.

This will work, but it may not scale well. Imagine what would happen if the retailer added more product types.

• Belts. Need a length attribute and a material attribute.
• Hats. Need a hat type attribute (i.e., snap back or elastic).
• Shoes. Need an attribute for lace length and the number of islets.
• Stickers. Need a height, width, and adhesive type attribute.
• Music. Need an artist attribute and a way to store a list of tracks.
• Movies. Need a director attribute and attributes for actors, the movie’s rating, the year it was made, and more.

All this, and in the example, there is not yet an attribute for price, sale price, cost, weight, shipping dimensions, or similar.

Very quickly, the ecommerce product database table would be hundreds or thousands of columns wide. What’s worse, most of the columns would be empty. The small, red t-shirt would still need just five or so columns, leaving thousands blank. This single table solution can be expensive to host since it wastes memory.

An ecommerce solution could eliminate the wasted space if it assigned each new product type its own table.

There would be a product table just for t-shirts.

A separate table would store information for pants.

This solution would work, but it would be very hard to manage.

For every new type of product, there would need to be a new, custom database table. The ecommerce platform would need to keep track of all of these tables, including understanding how they relate to each other.

Showing a list of products from a single manufacturer might take several database queries or “joins,” which are combinations of columns from different tables.

A third solution to the product attribute problem would be to implement an entity, attribute, value system — EAV. Popular ecommerce platforms like Magento take this approach.

EAV solutions frequently use multiple tables to store portions of the product information. Here’s the entity portion.

A different table would store the value for individual attributes.

Here two tables are storing the product information, but there are EAV database solutions for product catalogs that include three, four, five, or more separate database tables. This approach can make accessing product information quite slow. Nonetheless, EAV can be the best solution for relational databases, to manage a product catalog.

NoSQL for Ecommerce Product Information

NoSQL — non-relational databases — take a different approach to storing information. There are no tables of structured columns and rows. In a sense, NoSQL trades consistency for availability, speed, and flexibility.

Although non-relational databases or key-value pair databases have been around for decades, NoSQL has recently become popular with large Internet businesses, including Google, Facebook, eBay, and Amazon. These companies need to access significant amounts of complex information very quickly.

NoSQL architecture differs among implementations. But in general, NoSQL will store all of an item’s data in a single container.

Thus, one product equals one container (sometimes called an item or a document) in the database.

Sometimes, these items or documents will be in JSON format or similar, meaning that it will look very familiar to web developers.

For an ecommerce product, there will typically be a unique item ID, a set of common product attributes, and some attributes that are unique to a particular product. The small red shirt might look like the following.

{
     unique_id: 'HY8765NBg6yYT77nBhNkln543NjNhYTR',
     sku: 'POI0987654321',
     type: ‘t-shirt’,
     title: 'Amazing Shirt'

     attributes: {
         size: 's',
         color: 'red'
     }
 }

A particular pair of pants would have some things in common with the shirt, but in NoSQL there would be the flexibility to have different attributes.

{
     unique_id: '99iIiGtg6GHGjfj776098JjHGtgfffhhh',
     sku: 'POI0987654323',
     type: 'pants',
     title: 'Neat Pants'

     attributes: {
          waist: '30',
          inseam: '30',
         color: 'black'
     }
 }

With this approach, a retailer could sell any sort of product it wants without having to modify a database table or figure out how to keep track of the different sorts of attributes. A belt would simply be another container in the database.

{
     unique_id: 'jjfJlkjsdfALKJljJLKDJ877KJNBB',
     sku: 'BE1888477',
     type: 'belt',
     title: 'Studded Belt'

     attributes: {
          size: '30',
          length: '30',
          color: 'black',
          material: 'leather'
     }
 }

In this way, NoSQL is much more flexible. It is simply easier to store and manage product data. What’s more, querying for an individual product can be faster. This is especially true as the number of products in the database increases.

NoSQL Considerations

An online store using a SaaS ecommerce platform may not be able to take advantage of NoSQL. But retailers using licensed ecommerce platforms or custom solutions may find that NoSQL is a better choice for product catalog information.

There are plenty of applications for which relational databases make more sense, but NoSQL can be a better solution for product information.