Product Catalog

Concrete Table Inheritance¶

One approach, in a relational model, is to create a table for each product category. Consider the following example SQL statement for creating database tables:

CREATE TABLE `product_audio_album` (
    `sku` char(8) NOT NULL,
    ...
    `artist` varchar(255) DEFAULT NULL,
    `genre_0` varchar(255) DEFAULT NULL,
    `genre_1` varchar(255) DEFAULT NULL,
    ...,
    PRIMARY KEY(`sku`))
...
CREATE TABLE `product_film` (
    `sku` char(8) NOT NULL,
    ...
    `title` varchar(255) DEFAULT NULL,
    `rating` char(8) DEFAULT NULL,
    ...,
    PRIMARY KEY(`sku`))
...

This approach has limited flexibility for two key reasons:

• You must create a new table for every new category of products.
• You must explicitly tailor all queries for the exact type of product.

Single Table Inheritance¶

Another relational data model uses a single table for all product categories and adds new columns anytime you need to store data regarding a new type of product. Consider the following SQL statement:

CREATE TABLE `product` (
    `sku` char(8) NOT NULL,
    ...
    `artist` varchar(255) DEFAULT NULL,
    `genre_0` varchar(255) DEFAULT NULL,
    `genre_1` varchar(255) DEFAULT NULL,
    ...
    `title` varchar(255) DEFAULT NULL,
    `rating` char(8) DEFAULT NULL,
    ...,
    PRIMARY KEY(`sku`))

This approach is more flexible than concrete table inheritance: it allows single queries to span different product types, but at the expense of space.

Multiple Table Inheritance¶

Also in the relational model, you may use a “multiple table inheritance” pattern to represent common attributes in a generic “product” table, with some variations in individual category product tables. Consider the following SQL statement:

CREATE TABLE `product` (
    `sku` char(8) NOT NULL,
    `title` varchar(255) DEFAULT NULL,
    `description` varchar(255) DEFAULT NULL,
    `price`, ...
    PRIMARY KEY(`sku`))

CREATE TABLE `product_audio_album` (
    `sku` char(8) NOT NULL,
    ...
    `artist` varchar(255) DEFAULT NULL,
    `genre_0` varchar(255) DEFAULT NULL,
    `genre_1` varchar(255) DEFAULT NULL,
    ...,
    PRIMARY KEY(`sku`),
    FOREIGN KEY(`sku`) REFERENCES `product`(`sku`))
...
CREATE TABLE `product_film` (
    `sku` char(8) NOT NULL,
    ...
    `title` varchar(255) DEFAULT NULL,
    `rating` char(8) DEFAULT NULL,
    ...,
    PRIMARY KEY(`sku`),
    FOREIGN KEY(`sku`) REFERENCES `product`(`sku`))
...

Multiple table inheritance is more space-efficient than single table inheritance and somewhat more flexible than concrete table inheritance. However, this model does require an expensive JOIN operation to obtain all relevant attributes relevant to a product.

Entity Attribute Values¶

The final substantive pattern from relational modeling is the entity-attribute-value schema where you would create a meta-model for product data. In this approach, you maintain a table with three columns, e.g. entity_id, attribute_id, value, and these triples describe each product.

Consider the description of an audio recording. You may have a series of rows representing the following relationships:

This schema is totally flexible:

• any entity can have any set of any attributes.
• New product categories do not require any changes to the data model in the database.

The downside for these models, is that all nontrivial queries require large numbers of JOIN operations that results in large performance penalties.

Avoid Modeling Product Data¶

Additionally some e-commerce solutions with relational database systems avoid choosing one of the data models above, and serialize all of this data into a BLOB column. While simple, the details become difficult to access for search and sort.

Non-Relational Data Model¶

Because MongoDB is a non-relational database, the data model for your product catalog can benefit from this additional flexibility. The best models use a single MongoDB collection to store all the product data, which is similar to the single table inheritance relational model. MongoDB’s dynamic schema means that each document need not conform to the same schema. As a result, the document for each product only needs to contain attributes relevant to that product.

Querying¶

This query returns the documents for the products of a specific genre, sorted in reverse chronological order:

query = db.products.find({'type':'Audio Album',
                          'details.genre': 'jazz'})
query = query.sort([('details.issue_date', -1)])

Indexing¶

To support this query, create a compound index on all the properties used in the filter and in the sort:

db.products.ensure_index([
    ('type', 1),
    ('details.genre', 1),
    ('details.issue_date', -1)])

Querying¶

To return this data use the pricing information that exists in all products to find the products with the highest percentage discount:

query = db.products.find( { 'pricing.pct_savings': {'$gt': 25 })
query = query.sort([('pricing.pct_savings', -1)])

Indexing¶

To support this type of query, you will want to create an index on the pricing.pct_savings field:

db.products.ensure_index('pricing.pct_savings')

Since MongoDB can read indexes in ascending or descending order, the order of the index does not matter.

Querying¶

Use the following query to select documents within the details of a specified product type (i.e. Film) of product (a movie) to find products that contain a certain value (i.e. a specific actor in the details.actor field,) with the results sorted by date descending:

query = db.products.find({'type': 'Film',
                          'details.actor': 'Keanu Reeves'})
query = query.sort([('details.issue_date', -1)])

Indexing¶

To support this query, you may want to create the following index.

db.products.ensure_index([
    ('type', 1),
    ('details.actor', 1),
    ('details.issue_date', -1)])

This index begins with the type field and then narrows by the other search field, where the final component of the index is the sort field to maximize index efficiency.

Querying¶

MongoDB supports regular expressions within queries. In Python, you can use the “python:re” module to construct the query:

import re
re_hacker = re.compile(r'.*hacker.*', re.IGNORECASE)

query = db.products.find({'type': 'Film', 'title': re_hacker})
query = query.sort([('details.issue_date', -1)])

MongoDB provides a special syntax for regular expression queries without the need for the re module. Consider the following alternative which is equivalent to the above example:

query = db.products.find({
    'type': 'Film',
    'title': {'$regex': '.*hacker.*', '$options':'i'}})
query = query.sort([('details.issue_date', -1)])

The $options operator specifies a case insensitive match.

Indexing¶

The indexing strategy for these kinds of queries is different from previous attempts. Here, create an index on { type: 1, details.issue_date: -1, title: 1 } using the following command at the Python/PyMongo console:

db.products.ensure_index([
    ('type', 1),
    ('details.issue_date', -1),
    ('title', 1)])

This index makes it possible to avoid scanning whole documents by using the index for scanning the title rather than forcing MongoDB to scan whole documents for the title field. Additionally, to support the sort on the details.issue_date field, by placing this field before the title field, ensures that the result set is already ordered before MongoDB filters title field.