I have a Folder entity that can be Moderated by users. Folders can contain other folders. So I may have a structure like this:
Folder 1
Folder 2
Folder 3
Folder 4
I have to decide how to implement Moderation for this entity. I’ve come up with two options:
Option 1
When the user is given moderation privileges to Folder 1, define a moderator relationship between Folder 1 and User 1. No other relationships are added to the db.
To determine if the user can moderate Folder 3, I check and see if User 1 is the moderator of any parent folders.
This seems to alleviate some of the complexity of handling updates / moved entities / additions under Folder 1 after the relationship has been defined, and reverting the relationship means I only have to deal with one entity.
Option 2
When the user is given moderation privileges to Folder 1, define a new relationship between User 1 and Folder 1, and all child entities down to the grandest of grandchildren when the relationship is created, and if it’s ever removed, iterate back down the graph to remove the relationship. If I add something under Folder 2 after this relationship has been made, I just copy all Moderators into the new Entity.
But when I need to show only the top-level Folders that a user is Moderating, I need to query all folders that have a parent folder that the user does not moderate, as opposed to option 1, where I just query any items that the user is moderating.
Thoughts
I think it comes down to determining if users will be querying for all parent items more than they’ll be querying child items… if so, then option 1 seems better. But I’m not sure.
Is either approach better than the other? Why? Or is there another approach that’s better than both? I’m using Entity Framework in case it matters.
3
Option 1 is the easiest and cleanest and minimal solution. You are right that there’s less work involved in updating permissions when you move folders and there’s less work involved in working out where a user’s moderation permissions were actually set. So I would seek to implement this method. If you start adding additional records beyond what the super-admin originally entered (as per option 2), you start adding noise to your model and then you’ve got to filter the actual information back out again.
If you’re storing your data in a database, you should look up the Nested Set Model. In this model, you add a ‘left’ and ‘right’ field to your folders table, and give those fields values such that a folder’s left and right values encompass the left and right values of all descendant folders (there is a diagram on the wikipedia page that nicely demonstrates this). This model makes it very simple to identify all the children, grandchildren, etc. of a folder and all the parents of a folder – something that can be quite complex with just a standard parent id link.
I built a system with this requirement and used an in memory object graph to represent the folders, and ran permission checks against that, rather than having to pull the data out of the database.
http://martinfowler.com/bliki/MemoryImage.html
For option 2 you may want to store whether or not privileges are inherited from the parent or not. Otherwise you may loose user intent when changing the parent.
The better approach is keeping it logical. Recurse on your ancestors to determine privileges. Depending on how deep your average hierarchy is you may get away with doing a couple of queries, measure it. You could recurse in a Stored Procedure on the database tier also if you are using SQL.
Only if this is not fast enough I would materialize the privileges. I’d try to do that in a query caching layer and not in the authoritative / normalized database.
If you are going to use SQL, you can also use a “recursive CTE” which is a type of sql query syntax which allows you to do recursive, hierarchical queries as a single query. See this answer for more details: https://stackoverflow.com/questions/23722056/recursive-queries-cte-to-find-an-archive-permission