Azure DocumentDb Modeling flat vs nested

Question

I am new to Azure DocumentDb and have a question about the best way to model data in a collection. In a collection, not all documents have to have the same schema. In a very simple example, suppose I have a school collection that contains documents about teachers and students. Several of the json properties might be the same, for example 'lastName'. I need to differentiate between teachers and students and run a query to give me all the students with a last name of "Smith". My question what is the best way to define documents that are "students" compared to "teachers". I have seen examples that add a "type" property like this:

//Student document
    {
       "id": "035cbc59-76ba-4255-9abf-fa57cdcf81f4",
        "lastName": "Smith",
        "grade": 10,
        "type": "student"
    }

//Teacher document
    {
       "id": "035cbc59-76ba-4255-9abf-fa57cdcf81f4",
        "lastName": "Smith",
        "subjectTaught": "Algebra I",
        "type": "teacher"
    }

Then you could query like this:

SELECT * from c where c.lastName = "Smith" and c.type ="student"

I have also seen another approach where the object type is nested:

  //Student document
  {
      "student": {
        "lastName": "Smith",
        "grade": 10
      },
      "id": "7d2c5595-21b1-4598-8a70-196a3feeeab0"
    }

//Teacher document
{
  "teacher": {
    "lastName": "Smith",
    "subjectTaught": "Algebra I",
  },
  "id": "7d2c5595-21b1-4598-8a70-196a3feeeab0"
}

Then your query would look like this:

SELECT c.student from c where c.student.lastName = "Smith"

From a data modeling best practice standpoint, I'm curious to which approach is better. Obviously, this is a very simple example, and real world collections would have more complex documents.

There's really no "best" way to model. It's going to depend on your app's needs and query patterns. — David Makogon

Larry Maccherone Larry Maccherone · Accepted Answer · 2016-11-15T23:00:33

Your first example (using a type field) is the most common and some of the entity frameworks support this.

However, I did some performance testing and found that it's slightly better to have separate isStudent and isTeacher fields which are boolean and either always true or the field is missing. So, using your example:

//Student document
    {
       "id": "035cbc59-76ba-4255-9abf-fa57cdcf81f4",
        "lastName": "Smith",
        "grade": 10,
        "isStudent": true
    }

//Teacher document
    {
       "id": "035cbc59-76ba-4255-9abf-fa57cdcf81f4",
        "lastName": "Smith",
        "subjectTaught": "Algebra I",
        "isTeacher": true
    }

Then query with:

SELECT * from c where c.lastName = "Smith" and c.isStudent

I've never seen anyone do it your second way nor tried to performance analyze it, but my guess is that it would have similar performance characteristics to what I recommend above.

My underlying recommendation is to do some experiments. Then if the differences are minimal, go with the one that makes the most sense to you and your developers.

Azure DocumentDb Modeling flat vs nested

1 Answers