How to handle groups of users and single users in data models

Question

I'm working on a CRM system that will have both individual users as well as "teams of users". I'm encountering a problem in assigning tasks as I would like to be able to assign tasks/events/leads to individual users as well as to whole teams.

My problem is that traditionally my database table for tasks, leads, or events would tie that particular event to a user using a "uid" column. However, I'm not sure the best way to handle this (or how other systems handle this) type of thing.

I was thinking of adding a second column "is_team" that would be just be a bool. If the is_team column was set to true than the uid would be regarded as a team id for that particular row.

Any comments, suggestions?

Are you logically grouping these users into teams in your database with a 'teams' table or something similar, or are these 'teams' more loosely tied together for only singular tasks? — Jeff Lambert, Apr 14 '11 at 18:00
They are logically grouped. It's really quite confusing as one user can have multiple permission groups (salesperson+manager) and belong to multiple teams (sales team A and sales team B) which will also have their own permissions/permgroups. — Adam, Apr 14 '11 at 20:04
This is called the Party Model, and has been covered here http://stackoverflow.com/questions/716549/what-are-the-principles-behind-and-benefits-of-the-party-model and here http://stackoverflow.com/questions/7306827/what-is-the-correct-way-to-implement-a-party-model-with-the-various-roles-that — Neil McGuigan, Jun 04 '12 at 19:22

score 1 · Accepted Answer · answered Apr 14 '11 at 17:55

1

What about nesting the Users, so you have a parent_id. In this parent_id a user can belong to a "virtual user" which is in fact a group. That way, one can assign an entity to a User or a Team.

answered Apr 14 '11 at 17:55

d1rk

348
2
10

1

this seems like by far the simplest overall solution – Adam Apr 14 '11 at 21:33

score 0 · Answer 2 · answered Apr 14 '11 at 18:30

Couple of thoughts.

First, you could remove the uid column from the tasks, leads, and events table and replace with a lookup table. You could either have two lookup tables, one for users and one for teams, or a single table that has columns for both users and teams.

Second, maybe re-examine your requirements. Do you really need the ability to assign to either a individual user or a team? In the instance of assigning to a single user, could you make them a team of one so that all things (tasks, leads, events, etc) are only associated with a team (even if that team only has one member)?

No matter what you choose, just try to keep it simple and be open to refactoring when/if you figure out a better way to represent your data.

My main reason for splitting them up is because I want to be able to have a lead assigned to a single salesperson but also assigned to a whole team of processors. It doesn't really make sense to make an individual team for each salesperson. I should also add that I am considering adding permissions to this "lookup" table. Just simple can_read, can_write columns I think. — Adam, Apr 14 '11 at 20:02

How to handle groups of users and single users in data models

2 Answers2