How to model users in multiple workspaces

Question

In Slack and other apps, a user's email can be tied to multiple workspaces.

What’s the best approach to architecting that? Do you list workspace IDs on the user record, or do you maintain a list of user IDs on the workspace level? How do you handle JWT claims?

• Robert Harvey: You wouldn't list workspace ID's on the User record, since that's a one to many relationship. Nor would I store that at the workspace level. It's a many to many relationship, is it not? You need a linking table containing UserID and WorkspaceID.

Answer 1

A single user can belong to multiple workspaces, and a single workspace can contain multiple users. There is no clear aggregate relationship here, i.e. neither entity really "contains" the other.

Let's look at the two options you suggested:

• Storing user IDs in the workspace entity is not going to scale very well when you have thousands of users in a single workspace. You have to update the entire list whenever a user joins or leaves, and it would be very inefficient to list all workspaces for a user.
• Storing workspace IDs in the user entity is somewhat better, assuming a typical user will only join a handful of workspaces. It would still be very inefficient to list all users in a workspace.

What you really want is a separate entity that models a user's membership in a workspace. A minimal version of this entity will simply contain the IDs of the associated entities (user and workspace), but there will likely be other data that fits in here, such as a user's preferences for a specific workspace. Throw some indexes on the IDs and you have an efficient way to query for all users in a workspace or all workspaces for a user.