So i've got a setup where there are roles for users and permissions for categories and files. Table setup is
This is working, BUT what I would like to do is add categories for roles. So, for instance, instead of picking 10 different roles for access to a category, current roles can be parented to a group and then the group can be assigned to the category and any future additions to the group get parented in for access to the category or file. And if your role is inside a category, you have access to that category.
My first thought is to only allow 1 level to groups, so no sub groups of groups and then return a parent_id column in my query.
Right now i'm returning a comma delimited list of roles for category and then testing for existence in the list with the application server.
I'm having trouble thinking of something that isn't processor intensive. Is there a best practice setup for this?