How can I model a many-to-many relationship in Firestore without exceeding the document size limit?-CodePudding

Below is my database schema that stores a many-to-many relationship between a task and tag model. Google state that the maximum size that a document can be stored on Firestore is 1 MiB. If I continuously add tags to a task the document size would exceed that size limit.

Firestore-root
    |
    --- tasks (collection)
    |     |
    |     --- taskID (document)
    |           |
    |           --- title: "Go for a cycle"
    |           |
    |           --- completed: false
    |           |
    |           --- userID: "zaEh95kXJKapyVUqrPws58dyRIC3"
    |           |
    |           --- tagIDs: ["rWqTxB01TK9w8KRo2GHD"]
    |           |
    |           --- // Other task properties
    |
    --- tags (collection)
          |
          --- tagID (document)
                |
                --- title: "Health"
                |
                --- userID: "zaEh95kXJKapyVUqrPws58dyRIC3"
                |
                --- colour: "red"
                |
                --- // Other tag properties

A solution that I have found to work is to create a junction table, however every time I navigate to the detail view of a task I have to query the database to find those relationships which in return drives up billing costs. Can’t help but feel as though I am caught between a rock and a hard place.

Related / follow-up Q&As

CodePudding user response：

when I tap to see the details of a task a query is sent to Firestore to retrieve the tags associated with it.

Since you store the data in two different collections, yes, two different queries are needed. One to get the tasks and the second one to get the corresponding tags data. But that's not bad.

However, there are some other options that you can take into consideration. The first one would be, instead of saving the IDs of tags into an array, just save the actual data, meaning the entire "Tag" object. Or at least the important data of the tag. This practice is called denormalization. If you're new to the NoSQL databases, please note that this practice is quite common when it comes to Firebase. Also bear in mind that when you are duplicating data, there is one thing that you should know about. In the same way, you are adding data, you need to maintain it. In other words, if you want to update/delete an item, you need to do it in every place that it exists.

When using the above solution, note that there are some limits when it comes to how much data you can put into a document. According to the official documentation regarding usage and limits:

Maximum size for a document: 1 MiB (1,048,576 bytes)

As you can see, you are limited to 1 MiB total of data in a single document. When we are talking about storing text (tag IDs), you can store pretty much. I doubt you'll reach the limitation but as your arrays get bigger, be careful about this constraint. A workaround for this would be to create another document and another document for storing the tags. But also note, that besides the number of reads, you are also charged with the bandwidth needed to download the documents.

So it's up to you to decide which solution works best for your application.