2 added 20 characters in body
source | link

Third (more efficient) algorithm.

We have the same caching table as in two other answers.

We keep (either in memory or in the DB) the set (let's denote it M) of all groups for which we should either add or remove users together with the lists of users to add and to remove.

Then we just do it (add and remove users to the caching table) in the worker thread, one-by-one user or in small transactions.

Between steps of the previous algorithm, the sets of users to add or remove can be modified.

To know when to remove a user also from a parent group when we remove the user from a child group, we can use reference counting in the many-to-many relation table between groups and users.

In the set M we also should keep reference counting of how many times a user was added or removed. When it is removed, we just decrement it reference counter in the many-to-many link table table and remove only if it reaches zero. When we add it, we increment the reference counter in the many-to-many link table.

The above are my preliminary thoughts on the algorithm. They should be improved making the algorithm more detailed (to be worth of the name "algorithm") and more understandable.

Third (more efficient) algorithm.

We have the same caching table as in two other answers.

We keep (either in memory or in the DB) the set of all groups for which we should either add or remove users together with the lists of users to add and to remove.

Then we just do it (add and remove users to the caching table) in the worker thread, one-by-one user or in small transactions.

Between steps of the previous algorithm, the sets of users to add or remove can be modified.

To know when to remove a user also from a parent group when we remove the user from a child group, we can use reference counting in the many-to-many relation table between groups and users.

The above are my preliminary thoughts on the algorithm. They should be improved making the algorithm more detailed (to be worth of the name "algorithm") and more understandable.

Third (more efficient) algorithm.

We have the same caching table as in two other answers.

We keep (either in memory or in the DB) the set (let's denote it M) of all groups for which we should either add or remove users together with the lists of users to add and to remove.

Then we just do it (add and remove users to the caching table) in the worker thread, one-by-one user or in small transactions.

Between steps of the previous algorithm, the sets of users to add or remove can be modified.

To know when to remove a user also from a parent group when we remove the user from a child group, we can use reference counting in the many-to-many relation table between groups and users.

In the set M we also should keep reference counting of how many times a user was added or removed. When it is removed, we just decrement it reference counter in the many-to-many link table table and remove only if it reaches zero. When we add it, we increment the reference counter in the many-to-many link table.

The above are my preliminary thoughts on the algorithm. They should be improved making the algorithm more detailed (to be worth of the name "algorithm") and more understandable.

1
source | link

Third (more efficient) algorithm.

We have the same caching table as in two other answers.

We keep (either in memory or in the DB) the set of all groups for which we should either add or remove users together with the lists of users to add and to remove.

Then we just do it (add and remove users to the caching table) in the worker thread, one-by-one user or in small transactions.

Between steps of the previous algorithm, the sets of users to add or remove can be modified.

To know when to remove a user also from a parent group when we remove the user from a child group, we can use reference counting in the many-to-many relation table between groups and users.

The above are my preliminary thoughts on the algorithm. They should be improved making the algorithm more detailed (to be worth of the name "algorithm") and more understandable.