3

I'm working on a Meteor app which lets users create events and assign them crew members. I've played around with MongoDB before and made some experiences along the way, where for instance my first attempt went overboard with embedded objects and quickly became sluggish and hard to maintain. Several people therefore suggested to try a more traditional design approach and that's what I've done this time.

I've split my collections up with manual refs in mind,

Events

_id : ObjectID,
propA: "Some prop",
propB: "Some other prop",
employees: [
    {employee_id: ObjectID, crew_id: ObjectID},
    {employee_id: ObjectID, crew_id: ObjectID},
]

Employees

_id : ObjectID,
propA: "Some prop",
crew_id: ObjectID

Crews

_id : ObjectID,
propA: "Some prop"

The reasoning is that the events collection will become quite substantial during the applications life cycle and thus I consider duplication to be a bad thing. Another noteworthy feature is that an employee must belong to a certain crew, but on an actual event can work in any crew.

Intially I was quite happy with this design, but I have however found some problems which has lead me here.

1) 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: Rather then running one query I have to run three (first get the event, then loop trough the employee array and then join employee with the Employee collection and finally join the crew with the Crew collection).

2) My biggest problem so far though is what I believe is known as deep querying. Before adding an employee to an event I have to know if he/she is already assigned, thus requiring to query the Events collection for an Event with a given ID and also find check if the given Employee ID exists in the employee array. So far I've been unable to do this in a single query.

So my question boils down to this. Is my design fine as it is or should I change it given my use cases? Or alternatively, if I keep my current design how should I tackle problem 1 and 2 (that is, manual joins and refs are fine. But is performance a real possible showstopper down the line?).

10

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.

  • Thank you for the reply. It might sound strange, but I absolutely agree with you as I also think this would be better as a relational database. What I forgot to mention in my question is that the choice of a schema less db is due to the lack of support for anything else in Meteor at the moment. So I guess my question also can be reformulated to "Is there a way around this". As you've already pointed out though, I'm guessing not. – Index Dec 16 '13 at 16:02
  • @KGChristensen - you can use a pipe wrench to hammer in nails. It will take you a lot longer. Your hands will hurt a lot more. You'll bend a lot more nails, and you'll ruin the surface that you're working on. Finding and using the right tool to begin with will save you a lot of grief in the long run. – GlenH7 Dec 16 '13 at 16:04
-1

I agree with GlenH7 answer - you probably want a relational DB.

That said:

1) 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) [...] what worries me most is performance: [...]

Consider caching employees and crews - you won't have that many active, and that should solve the performance issue.

2) Before adding an employee to an event I have to know if he/she is already assigned, thus requiring to query the Events collection for an Event with a given ID and also find check if the given Employee ID exists in the employee array.

You could actually use some 'normalization' here:

Events:

_id : ObjectID,
propA: "Some prop",
propB: "Some other prop",

Events-Employees:

_id : ObjectID,
eventId: "_id of event",
employeeId: "_id of employee",
event_start: "denormalized event data for querying employee availability",

That's two queries to retrieve full event information (assuming employee/crew cache), but a single query to see who's available at a given time. You can even make that a covered query as an index with two ids and dates should remain small(ish).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.