Effective mongodb + mongoose. Schema design

database, mongodb, mongoose, node.js

Solution

Disclaimer: I've embarked on this simply as a challenge to myself. See the `<rant>` below for an explanation as to why I disagree with your approach.

First step to getting a solid grasp on No-SQL is throwing out terms like "denormalize" – they simply do not apply in a document based data store. Another important concept to understand is there are no `JOINS` in MongoDB, so you have to change the way you think about your data completely to adjust.

The best way to solve your problem with `mongoose` is to setup collections for Quotes and Items separately. Then we can set up references between these collections to "link" the documents together.

var mongoose = require('mongoose');
var Schema = mongoose.Schema;

var quoteSchema = new Schema({
  items: [{ type: Schema.Types.ObjectId, ref: 'Item' }]
});

var itemSchema = new Schema({});

That handles your Quotes -> Items "relationship". To get the Orders setup, you could use an array of embedded documents like you've indicated, but if you ever decided to start querying/indexing Orders, you'd be up a certain creek without a paddle. Again, we can solve this with references:

var itemSchema = new Schema({
  orders: [{ type: Schema.Types.ObjectId, ref: 'Order' }]
});

var orderSchema = new Schema({
  quarter: String
});

Now you can use population to get what you need:

Item
  .findById(id)
  .populate({
    path: 'orders',
    match: { quarter: 'Q1-14' }
  })
  .exec(function (err, item) {

    console.log(item.orders); // logs an array of orders from Q1-14

  });

Trouble with references is that you are actually hitting the DB with a read instruction twice, once to find the parent document, and then once to populate its references.

You can read more about refs and population here: http://mongoosejs.com/docs/populate.html

<rant>

I could go on for hours why you should stick to an RDBMS for this kind of data. Especially when the defense for the choice is a lack of an ORM and Mongo being "all the rage." Engineers pick the best tech for the solution, not because a certain tech is trending. Its the difference between weekend hackery and creating Enterprise level products. Don't get me wrong, this is not to trash No-SQL – the largest codebase I maintain is built on NodeJS and MongoDB. However, I chose those technologies because they were the right technologies for my document based problem. If my data had been a relational ordering system like yours, I'd ditch Mongo in a heartbeat.

</rant>

Problem

Original source