Welcome to the site, 67172!
There's one conventional pattern showing relations involving tables, which is usually called master-detail. This is restricted to a single list of items and details for the selected item (which might be a table again). Obviously, this is too simple for your case. You'll need to be creative yourself.
If you go for the complex design, the tricky part are the merged permssions, I think. My first idea is the following:
- The recurring pattern is that you have two sets of objects which are related: roles and users, roles and permissions, users and permissions.
- You could show each of these three associations using two tables for the two sets of objects, where a selection in one table highlights (not: selects) the associated entries in the other table.
- Each table must allow (single) selection of an entry as well as associating it with an entry selected elsewhere.
So you would have a table of roles linked with the users and with the role permissions table, a users table linked to the roles and the user permissions table, and two tables for role permissions and user permissions. Each table has a single-selection capability as well as a multi-association capability (e.g., a checkbox column).
If the user wants to see all users with a role, he selects a role (single selection in role table), and the associated role permissions as well as the associated users are highlighted (e.g., by checkboxes in these two other tables).
If the user wants to add a user to the role, he checks the association checkbox in the users table.
If the user wants to see all roles of a user, he selects the user, which will deselect the role, and set the association checkboxes in the roles list accordingly.
Some issues that need to be fixed with this idea:
- If a role is selected, a set of users are associated. There is no useful way to show which permissions are associated with these users. Similar if a user is selected: Many roles are associated, and role permissions cannot be shown.
- There are separate lists for role and user permissions. That does not fit the user model of permissions.
- If there are many items in any list, there's not enough room to show all the associated ones (because that means every selection rearranges them, creating confusion).
Other design approaches are
- A hierarchical table which lists associated items as descendants - this creates many double entries, and is tedious to interact with (expand/collapse).
- A graphical visualization - avoids double entries, because different paths can lead to the same item, but quickly get crowded for strongly connected networks.