Many to Many relationship in Firebase

Question

I have a Firebase database. 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:

1. Given a Company, who are the current Contractors.
2. Given a Contractor what Companies are they working for.

What are the alternatives for structuring the data within firebase?

Answer 1

The self-answer is indeed one way of modeling this. It's probably the most direct equivalent of how you'd model this in a relational database:

• contractors
• companies
• companyAndContractorsAssignment (the many-to-many connector table)

An alternative would be to use 4 top-level nodes:

• contractors
• companies
• companyContractors
• contractorCompanies

The last two nodes would look like:

companyContractors     companyKey1         contractorKey1: true         contractorKey3: true     companyKey2         contractorKey2: true contractorCompanies     contractorKey1         companyKey1: true     contractorKey2         companyKey2: true     contractorKey3         companyKey1: true 

This bidirectional structure allows you to both look up "contractors for a company" and "companies for a contractor", without either of these needing to be a query. This is bound to be faster, especially as you add contractors and companies.

Whether this is necessary for your app, depends on the use-cases you need, the data sizes you expect and much more.

Recommended reading NoSQL data modeling and viewing Firebase for SQL developers. This question was also featured in an episode of the #AskFirebase youtube series.

Update (2017016)

Somebody posted a follow-up question that links here about retrieving the actual items from the "contractors" and "companies" nodes. You will need to retrieve those one at a time, since Firebase doesn't have an equivalent to SELECT * FROM table WHERE id IN (1,2,3). But this operation is not as slow as you may think, because the requests are pipelined over a single connection. Read more about that here: Speed up fetching posts for my social network app by using query instead of observing a single event repeatedly.