The League of Extraordinary Developers

Telephone
0113 815 0986
Email
team@theled.co.uk

Blog

Whilst working on client projects we often find useful bits of information out. Where possible we blog about that information to pass on our knowledge. Below are some of our most recent entries.

Screengrab of the new LED site

Storing a Product Catalog for eCommerce with a Document Database

We have recently started developing our own eCommerce system that we hope to role out over a number of up coming projects. In the past we have always used things like Spree and Magento, but we have always had niggling issues with other systems, such as very complex DB structures.

Our overriding goal is to create a flexible system, that we can extend as we need for different projects. This blog post aims to cover our initial thoughts on how we are handling the modelling of the product data.

The first hurdle has been in deciding how to store the potentially very different types of product data. Clearly there are some common fields like name, sku, price but once you get further down modelling the details of different items, the data structure can look very different.

Modelling options with a RDBMS

In a RDBMS setup we really have the following modelling options for Products.

  • Entity Attribute Value (EAV)
  • Single Table Inheritence
  • Class Table Inheritance
  • Generic Columns
  • Serializing data into a blob
EAV

EAV is the technique used by solutions such as Magento. It offers a huge amount of flexibility, however if you've every looked at the DB structure of a EAV solution you'll know it soon gets very complex. On top of the complex schema, any queries to get data to the front end are also very complex, not to mention the issue of lack of database integrity.

Single Table Inheritance

Single Table Inheritance is pretty much a no-no in anyone's book. Once you start to have any more than a couple of types of products you end up with a pretty wide table full of NULL fields. Once again database integrity goes out of the window.

Class Table Inheritance

Table Inheritance is where you have your common data in a 'Product' table and then any variations are stored in separate tables such as 'FilmDVD' and 'MusicCD' tables. It would work pretty well for this, except for certain things like search queries. Sure querying on commons product fields is easy, and searching for results on a specific product type field is easy (e.g. search for all books by an author), however searching across different product types on a specific field is more hassle.

Generic Fields

Using generic fields is really not a neat solution at all. It means throwing data type enforcement out of the window, which is not something we're keen on. You can't apply indexes either, which will become an issue later.

Serializing data into a blog

The final option is to serialize all the non-common fields into a blob field. This option is pretty much pointless. It's basically trying to force the RDBMS into being a Document Database, except you get none of the benefits of using a Document Database. For example searching the data directly in the database and indexing just are not possible.

Modelling with a Document Database

Having already worked with MongoDB in the past for Incentive Maker, we knew it could give us the flexibility required for modelling product data. For example, in Incentive Maker all the generated games are all stored as different types of Game objects, which makes it easy for us to write code to search for specific details about the games, even though they don't all share the same data structure. We'll explain this a bit more below. The same principles of differing games can be applied to differing products. As stated earlier we know we have some common fields, but after that products can have all sorts of varying fields. MongoDB handles this flexible schema well though.

Before fully deciding on the MongoDB path, we did a little bit of a search and found this excellent presentation on using MongoDB for ecommerce. We'd definitely recommend you add this to your current reading list.

N.B. To help us model our data in Ruby on Rails we are using MongoMapper.

To model the common Product fields we have a class


class LedCommerce::Product
  include MongoMapper::Document

  key :sku, String, required: true, unique: true
  key :title, String, required: true

  key :description, String, required: true

  key :pricing, LedCommerce::Price, required: true

end

Each product type that we need inherits from the base Product object e.g. for film DVDs we'd have


class FilmDVD < LedCommerce::Product

  key :directors, Array, required: true
  key :writers, Array, required: true
  key :actors, Array, required: true
  key :studio, String, required: true

  ...
end

and for music CDs we'd have


class MusicCD < LedCommerce::Product
  key :band, String, required: true
  key :label, String, required: true

  ...

end

Although we have two different documents here, they are both stored as Product documents in our database


{
  [
    {
      _type: "FilmDVD",
      sku: "Film101",
      title: "Our Film DVD",
      description: "This is our stored film DVD",
      pricing: "1.99",
      directors: ["A Person", "A Nother"],
      writers: ["A Person", "A Nother"],
      actors: ["A Person", "A Nother"],
      studio: "TheLED",
      rating: 5
    },
    {
      _type: "MusicCD",
      sku: "Music101",
      title: "Our Music CD",
      description: "This is our stored music CD",
      pricing: "0.99",
      band: "The band",
      label: "The band's label"
    }
  ]
}

So basically whenever we have to work with a new product type, we just need the object to inherit from our Product object. This modelling really starts to show it's strength when we start to query the database. Note the complete lack of crazy joins etc when doing these queries that you'd get when modelling data with a RDBMS.

These examples are once again using MongoMapper.

Find all products

LedCommerce::Product.all
Find all music CD's

MusicCD.all
Find a single product by sku

This query applies to any of the base Product object properties. There are a couple of ways we can look for a product with a specific sku. Firstly we can search for a specific product type, like a MusicCD.


MusicCD.find_by_sku('Music101')

Or we can search more generically


LedCommerce::Product.find_by_sku('Music101')

In either case we will get the same object back.

Search a product for a specific key/value

We can also do queries like search for products from specific labels. Again notice how we can search on any field even from the base Product document.


LedCommerce::Product.find(:all, label: "The band's label")

Conclusion

As you can see from the above, Document Databases, such as MongoDB, are especially good at handling these kind of flexible database schema's very well.

As we get further down the line of building our new ecommerce software, we'll blog more on how we've used MongoDB. We've still got a lot of decisions to make, such as how to handle transactions effectively.

At the moment our code isn't open source. We may well change that as we get more of the project done, once we are happy with the core elements of our code.

blog comments powered by Disqus

Read more entries

Keep up to date

Blog RSS feed

Want to find out more?

If we have piqued your interest then we'd love to hear from you.

Get in contact with us

Services

  • Ruby on Rails
  • .Net
  • PHP
  • HTML5
  • CSS3
  • JavaScript
  • Web Apps
  • Mobile Web Apps
  • iOS Apps

Blog

  1. Blog title
    C4DI and the magical coffee beans
    Post Date
    Thursday, September 05 2013
  1. Blog title
    Designers - how far should their coding knowledge go?
    Post Date
    Thursday, May 30 2013
  1. Blog title
    Twitter Bootstrap - It's not all roses
    Post Date
    Friday, June 22 2012
  1. Blog title
    Keep your requests thin
    Post Date
    Friday, January 06 2012
  1. Blog title
    Storing a Product Catalog for eCommerce with a Document Database
    Post Date
    Monday, November 21 2011
  1. Blog title
    Success for Incentive Maker - Hull Digital Post
    Post Date
    Friday, July 08 2011
RSS

Twitter

Follow