Google Firestore - structuring deeply nested ordered data

Question

I'm trying to figure out how to structure my data for my exercise app with Cloud Firestore. The data model is as follows:

The app has many sets of exercises
Each set of exercises has an ordered list of exercises
Each exercises has an ordered list of exercise parts
Each exercise part has an ordered list of instructions
Each instruction has several properties (e.g. text, imageUrl, etc)

My first thought was to simply store the data in subsequent subcollections with numbered keys. However, I need to be able to query all data from a set of exercises (including all exercises, parts, and instructions). Since subcollections are not included in the results of a document query, storing all of the data as subsequent subcollections would require me to make many queries to retrieve all the data for a single set of exercises.

I could also store each of the above as a separate top-level collection and keep a reference to each of the parent types (e.g. an instruction would have a setId, exerciseId, and partId). I could then do a query for each of exercises, parts, and instructions, filtering on setId. However, this still requires multiple queries and also would require mapping through at each level to reconstruct the data on the client.

Finally, since I don't anticipate a need to query past the exercise level, I could store all parts and instructions within the exercise document in nested object / arrays. This smells a bit to me, plus when I tried to add several levels of nested data in this way via the web console, firestore threw an error.

Any guidance would be greatly appreciated.

Steve Robertson Steve Robertson · Accepted Answer · 2018-06-23T23:16:56

Because all of your data is ordered, you could create your own integer-based exercise ID and query off of it. This ID would be built by a leading "1" then the set #, exercise #, part # and instruction #. Example:

exerciseID: 102030101
    "text": "First, do x."
    "imageURL": "Set2Exercise3Part1Instruction1.jpg"
exerciseID: 102030102
    "text": "Second, do y."
    "imageURL": "Set2Exercise3Part1Instruction2.jpg"
exerciseID: 102030103
    "text": "Third, do z."
    "imageURL": "Set2Exercise3Part1Instruction3.jpg"

Then to query all the instructions, pics, etc. for set #2, exercise #3, part #1 you would include the following line:

exerciseAppInfo.where("exerciseID", ">=", "102030101").where("exerciseID", "<=", "102030103")

Google Firestore - structuring deeply nested ordered data

2 Answers