Hi,
For a project I’m working on I need to create a new permission system for a folder hierarchy, and I’m not quite sure what the best way is to do this. I have a few ideas and would like to hear your thoughts on them. Or perhaps some new ideas 
I’ll explain the scenario for you:
The application works with a folder structure very much like a filesystem: folders can be created inside other folders without restrictions. The structure is MySQL based. One db table with a unique folder id and a parent id. An example would be:
- Folder 1
– Folder 1.1
– Folder 1.2
– Folder 1.3
— Folder 1.3.1
---- Folder 1.3.1.1 - Folder 2
– Folder 2.1
— Folder 2.1.1
— Folder 2.1.2 - Folder 3
At the moment we only have permissions on the top level folders. The new system should allow permissions for all levels. We work with user groups, so a user can be member of multiple groups, and the permissions are assigned to the group. So, what’s a proper way to do this with MySQL/PHP? My thoughts are:
Option 1a
When the administrator selects the folders for a group, I store the lowest level folder ids that have been selected. For instance, the admin selects Folder 1, Folder 1.2, Folder 1.3 and Folder 1.3.1.1. In this case I would only store the ids for Folder 1.2 and Folder 1.3.1.1, because I can use their parent_id to look up the folders above.
Then the user logs in and selects a folder. Because I don’t have all folder ids stored in the database, I can’t “simply” join the folder table with the permissions table. I’d have to come up with pretty complex joins to get that done (if it can be done at all?). Personally I’m a fan of doing most of the “work” in PHP rather than MySQL because it’s so much easier to scale up the web servers instead of the db servers, but could this be done in pure MySQL and would it be worth consideration? In PHP I’d first have to retrieve all the group’s lowest-level folders, then loop through it and create a list of all folder ids (this time including parent ids) that the user is allowed to view. Then I can check whether the selected folder id is in the list of allowed folder ids.
Option 1b
Because I have to query the db for every folder to get it’s parent, this could potentially cause a lot of queries when choosing for option 1a. To reduce this I could retrieve them once and store them in the user’s session. I would have to create some kind of flag to update the user’s session if an administrator changes the permissions while the user is logged in.
Option 2.
When the admin selects the folders for the group, I store all folder ids (so not just for Folder 1.2 and Folder 1.3.1.1, but also for Folder 1, Folder 1.3, Folder 1.3.1). Now I can perform a select on my folder table with the selected folder id, joining the group and permissions table on folder id and group id, and I’m done.
A good thing about this is that I need few queries to get a result and no PHP loops, on the other hand I’m storing same data multiple times in the db. Another thing to keep in mind is what happens if a user moves a folder? For example, the user moves Folder 1.3 to become a child of Folder 2. With this option I would have to add my Folder 2 to my permissions table, while with Option 1 I wouldn’t have to do anything.
Option 3.
Store the folder ids the same way as Option 1a, but also create a sort of cache table that will look like the table in option 2. This cache table would be recreated when the permissions are updated. This is very much like 1b, except that what would be stored in the session is now stored in the cache table and I would still query the db.
Personally I’m thinking of going for option 1b with the PHP loop. But I’m just not sure if there are other/better ways to do this. Again, any thoughts on this matter are most welcome. Thanks in advance for taking the time to read this.
Cheers,
Pep