Effective mongodb + mongoose. Schema design

Question

I'm new to mongodb and nosql databases. I would really appreciate some input/help with my schema design so I don't shoot myself in the foot.

Data: I need to model Quotes. A Quote contains many Ttems. Each Item contains many Orders. Each Order is tied to a specific fiscal quarter. Ex. I have a Quote containing an Item which has Orders in Q3-14, Q4-14, Q1-15. Orders only go max 12 quarters (3 years) into the future. Specifically, I'm having trouble with modelling the Order-quarter binding. I'm trying to denormalize the data and embed Quote <- Items <- Orders for performance.

Attempts/Ideas:

1. Have an Order schema containing year and qNum fields. Embed an array of Orders in every Item. Could also create virtual qKey field for setting/getting via string like Q1-14
2. Create a hash that embeds a Orders into an Item using keys like Q1-14. This would be nice, but isn't supported natively in Mongoose.
3. Store the current (base) quarter in each Quote, and have each Item contain an array of Orders, but have them indexed by #quarters offset from the base quarter. I.e. if It's currently Q1-14, and an order comes in for Q4-14, store it in array position 2.

Am I totally off the marker? Any advice is appreciated as I struggle to use Mongo effectively. Thank you

Answer 1

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>