I’m currently developing a SaaS application and have a Firestore database structure as shown in the attached image under “Current.” The application associates a user_id with multiple client organizations, each of which has ‘n’ number of clients, and each client has ‘n’ number of proposal forms.

Now, we’re introducing a new feature (as shown under “New” in the image) where we want to add collaborators to our user_id. These collaborators should have access to every client organization under that user_id.

Given this, how should I structure my Firebase database to accommodate the new collaborators while ensuring that they can access every client organization?

What would be the best way to implement this while keeping the database as normalized as possible?

Reducing Latency:

Our current setup causes latency when fetching form information. How can I optimize the database structure to minimize this latency, especially considering the new feature with collaborators?

Are there any specific Firebase techniques or design patterns that I should follow to ensure quick data retrieval?
Any suggestions or insights on how to structure the Firebase database effectively and optimize for performance would be greatly appreciated.

1. Naive Approach: Initially, I thought of just adding a collaborator_id under each client_organization entry for each user. This seemed straightforward but quickly became a nightmare for scalability. The data duplication was crazy, and maintaining the relationships between collaborators and client organizations became complex and error-prone.

2. Denormalization Attempt: I also tried denormalizing the data by embedding collaborator details directly into each client organization. While this made fetching data faster, it led to significant redundancy and potential consistency issues whenever collaborator information needed updating.