Designing MongoDB Schemas

As promised, I’ve begun to refactor my Cart Compass shopping list SPA API using Node.js/Express/MongoDb. The original deployment’s API was built with Ruby on Rails and PostgreSQL, but I’ve experimented with MongoDb a bit and the compact structure of Cart Compass’ DB seemed like a good case for using MongoDb.

MongoDb falls into the class of noSQL databases as opposed to SQL databases like PostgreSQL. The two classes are quite philosophically different with the SQL databases built on a series of interrelated tables, each table responsible for a specific area of data. Designed correctly, a SQL database offers a powerful way of storing and retrieving data, especially in large systems that require a strict associations between the tables. Each table has a very specific purpose, and, if associated correctly, allows for complex relationships while ensuring single sources of truth (no duplicated data).

By contrast, noSQL databases like MongoDB, are designed to be a bit more like the WILD WEST. You have lots of freedom how you store your data, but with that freedom comes the risk of losing focus.

The terminology used by the two systems varies:

Comparison of database terminology

As a reminder, for the SQL design I split the data into 5 separate tables:

1. Users.
2. Lists — user_id and a name.
3. List_Items (items from a specific List) — list_id, item_id and a quantity.
4. Items ( the user’s master list of items, not attached to a specific list) — item_id and a name.
5. Categories — user_id, name and a sort_order.

One of the great features of MongoDB is its ability to embed documents within documents, so called ‘subdocuments’. In addition, unlike SQL databases , our do fields can be arrays! I remember, when first learning to design SQL tables, the realization that an array of constants had to be set in its own table and referenced with foreign keys. Not so in MongoDB.

With this in mind, I planned a compact design, with all of the user’s data embedded within each user document. The idea being that when a user logs in to the app most of the data will be needed in the session from the beginning, and a single request will pull all of that data. This is similar to serializing data at the time of an API request.

Putting it together

Like the SQL version, my User model has fields for name, email, and password, all strings. In contrast the model also contains embedded fields for the categories (an array of Categories), masterListItems (an array of past items created by the user), and and array of lists (an array of lists that contains individual lists also with an array of current list items).

The User document structure in MongoDB

As you can see, a lot of information packed into one document. In order to build this blueprint, I used Mongoose, a fantastic MongoDB object modeling library for Node.js. With Mongoose, you can create schemas that are blueprints for your models. Schema properties are type casted and can include some basic validation out of the box. Below is my User schema. Notice that the final three properties are arrays whose contents is built on another schema (sub documents)

const UserSchema = new Schema({
  email: {
    type: String,
    require: true,
  },
  password: {
    type: String,
    require: true,
  },
  name: {
    type: String,
    require: true,
  },
  masterList: [MasterItemSchema],
  categories: [CategorySchema],
  lists: [ListSchema],
});
const CategorySchema = new Schema({
  name: String,
  sortOrder: Number,
});

Once the schemas are designed models can be put together:

const Category = mongoose.model('Category', CategorySchema);
const User = mongoose.model('User', UserSchema);

Once I had the models in place it was time to try out creating some documents with sample data. I create a User, add some Categories, some masterListItems and Lists with items.

MongoDB creates each object of the document with a truly unique id number. The document can be accessed using that ID as can subDocuments using their id’s. Of course, documents and subdocuments can be also queried using other properties as well.

Querying the embedded data is quite easy. To find the items in a particular list I query by the user’s id and then find use the id() method to query for a a particular item’s quantity in a particular list:

User.findById(userId)
  .then((user) => {
    const myList = user.lists.id(listId);
    const myItem = myList.listItems.id(appleItemId);
    console.log(myItem);
});
// quantity: '1/2 dozen'

Next step is to put together Express routes to interact with my collection. Stay tuned.
Here’s the link to the repo.