33

How to structure and query data from Firebase Cloud Firestore in a many to many relationship?

I have Companies and Contractors. A Contractor can work for more than one Company and a Company can have multiple Contractors. This is a straightforward many to many relationship. I want to be able to answer the questions about Companies and Contractors:

Given a Company, who are the current Contractors. Given a Contractor what Companies are they working for. What is the right way for structuring the data within Cloud Firestore?

InfoStatus
  • 6,983
  • 9
  • 41
  • 53
  • 2
    Well, it's a [document-model database](https://firebase.googleblog.com/2017/10/cloud-firestore-for-rtdb-developers.html), so I think you shouldn't think it in a relational fashion. Anyway, I think you can just have a collection of Contractors for a given company, and vice versa (so yes, you would have some sort of "duplication"). I don't think you could have a "normalized" model, as in a relational database. – jmm Oct 07 '17 at 23:11
  • 2
    I see several approaches. A company could contain a subcollection of contractors or the other way round, a contractor a subcollection of companites. This means that some contractors or companies are duplicated. Perhaps use empty documents instead and use the id to look up in the other collection. What's the best depends on your typical queries. Try it out and adapt the database to your needs. – nalply Oct 25 '17 at 19:19
  • 2
    Or in the other way round you try making a new collection which store the relationship between the contractor and the company because in case subcollection if we query the main collection we don't get the data of subcollection associated. – J Prakash Oct 26 '17 at 10:18
  • 1
    The Firestore docs say: "as a NoSQL database it differs from them in the way it describes relationships between data objects." https://cloud.google.com/firestore/docs/. What do they mean by that? – Juan Solano Jan 18 '18 at 03:54

2 Answers2

11

You can have Contractors item with a map field which has the key as the company key and the value can be true ou a timestamp to help ordering. And Companys with a map field with the contractors key as the key and some value. This aproach is mentioned in https://firebase.google.com/docs/firestore/solutions/arrays, when you try to filter and sort toghether...

Contractors
  companies
    idCompany1: timestamp
    idCompany2: timestamp
    idCompany3: timestamp

Companies
  contractors
    idContractor1: timestamp
    idContractor2: timestamp

Is this case you can make any queries like this company contractors or the companies of this contractor. Like this:

fireStore.collection("Companies").whereField("contractors." + idContractor, isGreaterThan: 0).order(by: "contractors." + idContractor, descending: true)

Yes you have to update both places in any task

Hope this helps!

André Lima
  • 315
  • 4
  • 11
  • 3
    I have exactly the same structure with clients / users. The only thing I would add to this is the Firestore security rule could be like this: allow read: if resource.data.contractors[request.auth.uid] > 0; Assuming the idContractor1 is the uid. Then only the contractors can see the allowed companies. – SammyT May 09 '18 at 02:39
  • 4
    Why the timestamp as a value? – Nate Uni Jun 27 '18 at 10:07
  • @NateUni You're question is open-ended, depends upon your requirements. One use-case is to identify when it was added for sorting purposes, remember this is a two-way/many-to-many design pattern and it is expected to be redundant. But in my opinion it would be better to use a value field of less bytes such as boolean/null. [Read more](https://firebase.google.com/docs/database/web/structure-data#fanout) – ZZZZtop Dec 19 '20 at 23:12
  • @NateUni also I forgot to mention a use case for raising the write limit for the collection to `500*n` for when your writes exceed 500/per second which you make use of by sharding timestamps, you can read more about there on [Firestore Guides](https://firebase.google.com/docs/firestore/solutions/shard-timestamp#sharding_a_timestamp_field) – ZZZZtop Dec 20 '20 at 03:21
  • Do keep in mind that this approach comes with at least a couple of [limitations](https://firebase.google.com/docs/firestore/quotas#collections_documents_and_fields). The document size in firestore can't exceed 1Mb. Also, you can essentially have a maximum of 20,000 fields per document, which means you can only store limited number of contractors per company or vice versa. – Maaz Bin Khawar May 11 '21 at 09:42
0

As André Lima has explained in his answer, you can use an Array for the relationships. But I don't think both the collections need to have a field for the array used for mapping. Only one collection needs to have a field for the relationship array.

companies: {
  contractors: ['contractor_id_1', 'contractor_id_2', 'contractor_id_2']
}

While querying, you can use the array-contains operator to filter based on array values.

Example Query (Web):

companiesRef.where("contractors", "array-contains", "contractor_id_1")

You can also use in and array-contains-any operators as per your requirement.

Debiprasad
  • 5,895
  • 16
  • 67
  • 95
  • How would you then fetch all the contractor documents in a specific company (via a company id)? Essentially something like: `contractorsRef.where("companies", "array-contains", "company_id_1"), you would need an array of companies in each contractor document I think? – labarna Feb 05 '21 at 16:00