Database – OLTP vs OLAP – MongoDB

There is no "right" way to use MongoDB, only trade-offs. Let's take a simple example:

Option 1: Have everything in one document, and $push packets into an array

Option 2: Make a new document for each packet. Have each one point to their 'parent' object.

The trade-offs:

1. When searching for a packet, Option 1 will return the entire document, and not tell you where in the array your packet is. Option 2 will return the specific packet.

2. Option 1 will fail over and die if you have too many packets (because of the 16MB limit on document size)

3. Option 1 is much faster if you always list all packets for a specific object. Option 2 requires many disk seeks to load all the packets. On modern hard drives, disk seeks are 100x slower than sequential disk reads.

4. Option 2 takes a little more disk space (because they all must link to their parent, instead of implicitly as in Option 1).

5. Option 2 has roughly constant write time, but Option 1 will have variable write time. In option 1, you are expanding an existing document when adding packets. Sometimes it won't fit and must be moved somewhere else. This can slow down the system. But if you're constantly adding packets to one document at a time, it will probably eventually be moved to the end where it can expand without being moved around, so that's slightly better.

The rule of thumb is: Always assume you will store everything in one document until you find a reason to break it up. But beware of the trade-offs each way.

Why are you using a non-relational database in this case?

You said:

When listing an event I have to manually join refs in order to show the actual properties of the employees and crews (such as name), it's a bit tedious but what worries me most is performance:

and

My biggest problem so far though is what I believe is known as deep querying

[emphasis added]

You already have some strong indicators that a non-relational database is not the right fit for your needs. Use a relational database instead. Changing the schema in this case, won't compensate for issues within the toolset.

Try your design out with a relational database and see if the issues go away. That will provide the proof you need that you haven't picked the right tool to use in this case.

In a comment, you added that your stuck with this particular environment and are seeking alternative schema designs.

From what I have seen, the best way to compensate in this case is to denormalize all of your tables. Instead of 3 tables, you'll have 1 table where you lay out the crew and the employees per event.

That does raise some challenges with making sure you don't double assign employees, but having a status flag for the event should help control for that. Essentially you'll query to see if employee ABC is already assigned to an event (or crew) that is still active.