TLDR: for large many-to-many relations with an intermediary node representing an access list, would you recommend duplicating the access list to the smaller nodes?
Let's say I'm building a ticketing system (like JIRA). Tickets are in Projects, and Users can access some of those Projects (and so their Tickets). It's a read-heavy system. Users are expected to have access to a lot of projects (>30). Projects are expected to have a lot of tickets (> 500)
A) A naïve Firestore modelisation would be:
/users/uid
name
/projects/uid
name
accessList: userRef[]
/projects/uid/tickets/uid
name
status
In my security rules, when accessing a ticket, I can check that the current user uid is part of the project's (parent) accessList. (cost one additional 1 read per operation)
To populate a home page that would contain all the open tickets on projects accessible to the user, I would need to do N+1 queries :
- Get the list of all the projects accessible to the user
- For each project, load its tickets where status == 'open'
B) A second approach would be to add a projectRef inside the ticket document
/users/uid
name
/projects/uid
name
accessList: userRef[]
/projects/uid/tickets/uid (or /tickets/uid)
project: projectRef
name
status
To populate the home page I would need 2+ queries :
- Get the list of all the projects accessible to the user
- Get all tickets where the ticket.project is in the list of accessible projects ids and status == 'open'
But, this would be affected by the max 30 disjunctions. This means that if a user has access to more than 30 projects in their portfolio, I'll need to split/merge queries.
(Same security rule as A, so it still costs one additional read per operation to check the parent access list)
C) A third approach would be to duplicate the access list on each ticket
/users/uid
name
/projects/uid
name
accessList: userRef[]
/projects/uid/tickets/uid
duplicatedAccessList: userRef[] (maintained via Cloud Functions)
name
status
To populate the home page I would need 1 query :
- Get all tickets where the ticket.duplicatedAccessList contains the user uid
This would work for more than 30 projects and the securityrules would no longer need to check the parent. Downsides:
- Updating the accessList in one project requires updating all the project's tickets duplicatedAccessList. It would not be atomic, as a Firestore transaction are limited to 500 documents max.
- I think I would have to create Cloud Functions to perform the updates (and forbid the user from updating the duplicatedAccessList in a ticket).
- If done with Cloud Functions, it means that newly created tickets will not appear instantly on the user's homepage after they created it. Tickets created offline would not appear at all, until back online and processed by the Cloud Function. Or I would need to set a temporary accessList at creation, which would be later overridden by the CloudFunction.
Am I missing another way to model this? Despite its added complexity, I feel that only solution C) can scale, and it will lower the cost (no need to read the parent in the security rules) but I may have missed other downsides.
