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.
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.
TweetRead more entries
Keep up to date
Want to find out more?
If we have piqued your interest then we'd love to hear from you.