Database link tables to model
Suppose you have an application that stores images in categories. Images can be uploaded, deleted, viewed, ... by users that were given right to view, upload, delete, ... Categories can be nested, and the rights can be set on every level.
Table category: id, name
Table user: id, name
Table user_category: id, user_id, category_id, view, delete, upload
Should I create a UserCategory object that has a User and a Category object + the view, delete & upload rights?
Fetching the categories for a user will thus result in a List<UserCategory>.
Category.countImages() -> results in a full count of all images in the category
UserCategory.countImages() -> counts images in the categories where the user has right to
Is this a good approach?
Re: Database link tables to model
That sounds reasonable. From a database perspective you've got a many to many relationship between a Category and a User with permissions tied to this relationship.
Realize that there are some potential performance issues with this approach. Say I'm 50 levels deep trying to determine if a User has permission to do something. Presumably you want to deny permission if some parent up the chain doesn't allow the operation that the User is trying. You now need to walk up the tree examining each parent node to determine if you should grant the permission. Now have it 50,000 levels deep. That may not be your use case so this may not be a big issue.