If you know anything about database design, I’d appreciate your help for a minute.
Imagine I have a table with these fields: id, parent_id, data. It holds data arranged in a tree. The tree can have an unlimited number of branches but only one trunk. I would like to add a permissions mechanism to this data. I would like to be able to assign arbitrary permissions to arbitrary users. Additionally, I’d like the permissions closer to the trunk to propagate to the limbs that branch from it, unless it is overridden. In other words, if, at level 1 in the tree, I set “READ” permission for user “JIM”, then “JIM” can “READ” that item, as well as any item that branches from it unless that branch happens to have “NOTREAD” permission set for user “JIM”.
What’s the best way to do this resulting in the cleanest operation and requiring the least number of database calls?
There are three types of operations related to permissions that I would like to be able to perform. 1) Get permissions for a specific user for a specific item. 2) Get a list of all items for which a user has a specific permission. 3) Get a list of x number of items for which a specific user has a specific permission.
You can create tables. You can alter my exiting table. Anything is acceptable.
Please provide database structure and pseudocode.
Thanks in advance.