Without JOINs, what is the right way to handle data in document databases?

Question

I understand that JOINs are either not possible or frowned upon in document databases. I'm coming from a relational database background and trying to understand how to handle such scenarios.

Let's say I have an Employees collection where I store all employee related information. The following is a typical employee document:

{
   "id": 1234,
   "firstName": "John",
   "lastName": "Smith",
   "gender": "Male",
   "dateOfBirth": "3/21/1967",
   "emailAddresses":[
      { "email": "[email protected]", "isPrimary": "true" },
      { "email": "[email protected]", "isPrimary": "false" }
   ]
}

Let's also say, I have a separate Projects collection where I store project data that looks something like that:

{
   "id": 444,
   "projectName": "My Construction Project",
   "projectType": "Construction",
   "projectTeam":[
      { "_id": 2345, "position": "Engineer" },
      { "_id": 1234, "position": "Project Manager" }
   ]
}

If I want to return a list of all my projects along with project teams, how do I handle making sure that I return all the pertinent information about individuals in the team i.e. full names, email addresses, etc?

Is it two separate queries? One for projects and the other for people whose ID's appear in the projects collection?

If so, how do I then insert the data about people i.e. full names, email addresses? Do I then do a foreach loop in my app to update the data?

If I'm relying on my application to handle populating all the pertinent data, is this not a performance hit that would offset the performance benefits of document databases such as MongoDB?

Thanks for your help.

Answer 1

"...how do I handle making sure that I return all the pertinent information about individuals in the team i.e. full names, email addresses, etc? Is it two separate queries?"

It is either 2 separate queries OR you denormalize into the Project document. In our applications we do the 2nd query and keep the data as normalized as possible in the documents.

It is actually NOT common to see the "_id" key anywhere but on the top-level document. Further, for collections that you are going to have millions of documents in, you save storage by keeping the keys "terse". Consider "name" rather than "projectName", "type" rather than "projectType", "pos" rather than "position". It seems trivial but it adds up. You'll also want to put an index on "team.empId" so the query "how many projects has Joe Average worked on" runs well.

{
  "_id": 444,
  "name": "My Construction Project",
  "type": "Construction",
  "team":[
    { "empId": 2345, "pos": "Engineer" },
    { "empId": 1234, "pos": "Project Manager" }
  ]
}

Another thing to get used to is that you don't have to write the whole document every time you want to update an individual field or, say, add a new member to the team. You can do targeted updates that uniquely identify the document but only update an individual field or array element.

db.projects.update(
  { _id : 444 },
  { $addToSet : "team" : { "empId": 666, "position": "Minion" } }
);

The 2 queries to get one thing done hurts at first, but you'll get past it.