Currently have a many-to-many relationship, but now need to group one of the entities

Question

I have a case where there are representatives that are responsible for certain clients. They can be responsible for many clients, and those clients can have many reps. I have the tables reps, clients, and the join table of client_reps.

It's common in our company to have multiple reps be considered as a group. So they even have a name for the group and their own group email address. How would I even begin to properly introduce a group concept into this existing structure?

To give an example, we have reps with IDs 1, 2, and 3. And then a client with ID 1. All those reps have their own names and email addresses with their accounts. But now they want to be grouped under a single entity, with their own name and email. Instead of assigning those three individual reps, they would be assigned as a single group. The system also has to know that this group consists of those three reps for reporting purposes.

It might even be acceptable to still assign those reps individually, but as long as the system can distinguish that those three are part of a particular group.

Answer 1

What you are talking about behaves like distribution groups. Here is an EER diagram of how you can solve this.

Reps can belong to many groups and a group may have many reps. Each group has it's own id, name and email.