Implementing Many to Many Relationships on Firestore

Question

I need to model a many-to-many relation on Firestore. A summary of the requirements follow.

1. A company can hire many contractors for a project. A contractor can work for many companies on different projects at different times.

2. There should be no limit on the number of contractors or companies, i.e. collections or sub-collections should be used.

3. A contractor should be able to query by companies; and vice versa, a company should be able to query by contractors. For example, (1) a contractor might ask for a list of companies he/she worked for sorted by project & time, and (2) a company can ask for all contractors who worked for them over a month sorted by project & contractor, and possibly divided by week.

4. As far as the company is concerned, a contractor can change status, e.g. working, complete. A company changes the status of a contractor during the project lifetime. This status can be used in queries.

5. Obviously, contractors should not have access to other contractors' information.

6. A company is represented by only a single user on the mobile app. Similarly, a contractor is represented by only a single user on the mobile app.

7. The mobile app is built in React Native, which (to the best of my knowledge) is considered by Firestore as a web app.

I am thinking of using a sub-collection of documents for/under each company. Each document represents a project. All contractors' names, their statuses and start and end times are stored on this document.

At the same time, having a duplicate sub-collection of project documents for/under each contractor. Each of these duplicate documents represents a partial copy of the project's document (above). This duplicate document stores the company name and start and end time of the project.

a. Whenever a relationship is established, e.g. a contract is signed, both documents are created in a batch.

b. Status exists only on the 1st copy of the document.

c. In case of any rare changes to the almost static data, eg. name, phone, both documents are updated.

Does this design make sense? Any concerns, suggestions, better ideas? If you agree with the design, I would love to hear from you, maybe you can write in a comment something like sounds good.

AskFirebase

Answer 1

There are particular cases when you can use a sub-collection and when not to use sub-collections.

When to use sub-collections:

1) When you don't want to store a lot of fields in a document. Cloud Firestore has 20,000 field limit. (If the Company and Contractor information is very huge and can exceed more than 20,000 fields)

2) When updating the parent collection is a common operation. Firestore only lets you update the document at rate of 1 write/second. (If the Company and Contractor information is modified very often)

3) When you want to limit the access to particular fields of a document. (If you want to restrict the access to a Company's contractors or if the access to Contractor's companies should be restricted. In this case moving the restricted fields to another document in another collection is also a good idea!)

When not to use sub-collections:

1) When you want to query the collections and sub-collections together. Firestore queries are shallow. So sub-collections won't be queried when you query the parent collection so you have to query them separately. (If you have a case to show all the companies and their contractors in one window)

2) When you want to show the sub-collection when viewing the collection.(When showing a company, you might want to show its contractors. Here the number of reads will increase because instead of reading one document you are reading one document and its sub-collection all the time)

3) When you want to query collections and sub-collections together.(You can use the newly announced collections-group query whenever you want to query something that's common across the Companies and Contractors such as field of work or minimum rate)

4) If you're thinking about querying individual pieces of data, you should put them in a collection. (If the Contractor's particular attributes are usually queried by Companies or a Company's details are looked upon by multiple Contractors)

My Suggestion:

Company collection to store company information on which companies can be searched according to their qualities.

Contractors collection with the same approach since I'm assuming contractors will be queried a lot according to their attributes.

Projects sub-collection for info about the projects on which companies and contractors will collaborate. This can be a sub-collection under Company collection if only one company will be working on a project. Even if multiple contractors are going to be working on a project for a company you can store the contractor's Ids in an array in the Projects collection. This will help you avoid the Projects partial sub-collection inside each Company/Contractor collection.

But if you need to query on the project's qualities, it is better to expose them as a seperate parent collection. I leave that up to you.

Finally I would suggest a new collection Contracts which can be used to store the relationship between Company, Contractor and Project and all the information on which you can do the complex querying on. If the same company and contractor has two different projects on which they are working/collaborating, then it can be two documents in Contracts collection. This comes handy when you want to show some dashboards. Using this single collection you can show the separate statistics for a Company, Contractor and complex statistics involving both Company and Contractor.

Hope this helps.