Many-to-many assignments in user system

Question

I have a few software architecture/design questions related to a technically quite simple problem. We already have all the functionality implemented properly, however from a design perspective it is super messy and unclear, which is why I'd like to refactor it using a nice structure - which is what this question is about. Considering all objects I will defined below, what should their interdependencies look like? While we are using a NoSQL DB, my question is much more oriented towards the structures we actually have in the code than any optimizations based on what type of database we are using. I'll explain a simplified version of the problem to exemplify the issue.

Let's say I have groups/clubs and users.

Groups can have many users.

Users can be either admins or regular members of each group they are a part of.

In terms of editing information from a more frontend driven perspective, I have the following needs:

• When looking at a user, I need to be able to see a list of groups he is a part of, and whether he is an admin or regular user (which I can then (un)assign, for example say through a simple checkbox for each group.)

• When looking at a group, I want to see all users but be able to distinguish between admins and regular members.

• When looking at a group, I need to be able to add users to it if I am an admin of the group, as well as assign their role (admin/regular)

• Regular users should be restricted from actions reserved for group admins.

Even in this very small subset of functionality, there is already some kind of conflict when looking at specific implementations. Say, for example, each group has a list of regular members and a list of admins. Then I could very easily edit privileges for a given group; However, when editing a specific user directly I'd have to first of all get all the groups he is a part of, then carefully edit only the member lists of specific groups. The converse is true when the design is the opposite: The user having a list of groups he is a part/admin of. You could also consider duplicating information, but that would then pose a design flaw in the sense that you'd have to update stuff twice. (Note that I'm still talking at a more abstract level, not necessarily how it is implemented in a database - Although, optimally, the database structure should be the same as the logical one)

Either way, I always have to either iterate through all possible users and see if they are part of, say, group X, or iterate through all possible groups to see if user A is part of that group.

I hope my explanation wasn't too unclear, but basically I'm looking for advice on how to best structure the relations between my objects to enforce a strict logical model as well as make implementation of it "clean".

Thanks!

Edit: To clarify, the structural problem we have is when sending back information to the client. The client needs to know the relationship between groups and users, as well as what the role of the user is. Therefore, when querying a group, where do I put the role information, knowing I have a structure of group-has many->users. I don't want to put a flag of isAdmin on the users, since that information is specific to the user-group combination, not the user itself. Similarly, when querying all groups the user is a part of, where do I put the role information? The thing is that I want to stay consistent across both cases, without duplicating information either.

Answer 1

I am not the greatest master in these kinds of "problems". But the most flexible solution I can come up with is:

• 1 table to hold the data for the groups
• 1 table to hold data for the users
• 1 table to assign users to groups, also specifying access level (regular, admin) and any other roles / rights might be needed.

In the "worst" case, the third table will have Ngroups x Nusers entries (all users are members in all groups).

Answer 2

How about sending the client (i.e. in JSON), one array whose elements are array of 3 strings?

[
  [ "alice", "admin", "discussions" ],
  [ "bob", "user", "discussions" ],
]

The client can sort/filter/aggregate as needed for UX, with no joins required.

Answer 3

I'd probably have two operations for retrieving the data. Simple structures for that are as follows:

groups for user:

{<group>: <user-type>, ...}

users for group

{<user>: <user-type>, ...}

And for updates, you really just need one way to do it. Conceptually, whether you add users to groups or groups doesn't matter. I think it's more natural to add users to groups.

Access to see and update these should be handled at the server side based on the user that is making the request. For example, a non-admin user could be allowed to see the groups they are a member of but not the users in those groups. An admin on a group could retrieve and update that group but not be allowed to get the groups for other users i.e. the groups for user would always fail if the user calling it was not the user requested. You'd probably want to have a super-user type admin, however that can do such things.