What is the best way to model an organization using people, positions and teams when people have multiple jobs? [closed]

Question

I have an interesting modeling problem. I am trying generate an org chart on a website (the backend is C# / SQL server and the frontend is javascript / google orgchart API, but the crux of the modeling problem is more generic so I didn't include any of the specific technologies in the tags below as the issue is not around any tech specific issues.

I have the following 4 database tables:

1. Team - which has fields Id, Name, ParentTeamId (which is another row in the same table)
2. Position - which represents a position within a team. Fields are Id, TeamId, IsTeamHead, etc
3. Person - represents a person (no linkage in this table to any other table). Fields are Id, FirstName, LastName, etc
4. PersonPosition - represent people in positions (this bridges the two tables) Fields are Id, PersonId, PositionId, StartDate, EndDate

When I have a plain vanilla org chart this works perfectly because i basically loop through each Team (since each has its ParentTeamId) and build up a team hierarchy and show the position in that team (using the TeamId field) with "Ishead" = true and show the person that is associated with that position as the head.

My issue is that (as not so uncommon), there are people that now have been given multiple responsibilities - they essentially have 2 different jobs. Previously, Joe was head of marketing and Bill was regional head but Joe left

Before the head of marketing and the regional head, which was 2 different people (2 different positions) So Bill is the head of Marketing but is also regional manager in the US. I am trying to figure out what is the correct way to model and visualize this.

The first part of the modeling problem is to decide if I should model this as two different positions. If I do, i can have multiple entries in this PersonPosition table (both with the same PersonId) but the issue there is that it feels like I am overcounting number of positions.

Also, from a visualization point of view, the same person would show up in 2 places. Maybe that is correct from a functional point of view but seems odd that you would have the same person listed multiple times (maybe its not so odd but wanted to get feedback on what people have seen in this case as the expected visualization and what seems acceptable maybe should drive the modeling)

Any suggestions for the "right" way to do this?

Answer 1

I think you should consider the difference between a "position" and a "role". In many organizations, multiple roles may exist: safety coordinator, purchasing, accounts payable, receptionist, ... Often, especially in smaller organizations, a single person may act in different roles at different times. It may even be that they report to different people when they act in different capacities (for example, the emergency responder may report to the safety coordinator, but the purchaser reports to head of operations.)

In order to properly reflect these things, the relationships in your database need to reflect, to the extent possible, the relationships that exist in real life. This probably means you will have multiple tables (as you already have), but it will keep things clean.

The other thing to keep in mind (and possibly worth reflecting in your database) is that many organizations are matrixed: people may be on certain project teams, and part of certain organizations. The electrical engineers may all report to the electrical engineering manager, but they might be working on different projects / products, and thus belong on different project teams.

Capturing all that accurately is hard. Here is my suggestion (not unlike yours, but with some tweaks):

Table 1: employees Person name, Employee ID, Start Date, Salary, Vacation, ...
This is the table that says when you get paid, how much vacation time you have, what your "HR status" is. There is only one of you - this table has only one of you, and is used for those things which can't be doubled up (although we'd all like two pay checks).

Table 2: managers Manager ID, Report ID, Report kind
This table states, for each manager, who are the people reporting to them, and what kind of relationship it is. You might have a "primary" relationship, and other relationships: "project leader", "team leader", ... The "primary" manager might make HR type decisions, with inputs from "other" managers.

Table 3: teams Team name, Team ID, Manager ID, BelongsToTeam, Team description, ...
A table that describes every "organizational entity", with any auxiliary information that might be useful. The BelongsToTeam allows a hierarchical structure of teams, which helps with visualization.

Table 4: roles Role name, Team ID, Employee ID, isPrimary
This table describes who is in a given role. An employee with multiple roles will show up multiple times in this table, and may report to different managers depending on the role they have. I added an "isPrimary" field here - not sure if that is redundant. In a sense, if you start with the "primary" role of the employee in table 4, and find out who the team manager is in table 3, you should end up with the person in table 2 who is the primary manager... I worry that you might end up with inconsistency if you leave this in both places.

I believe the above allows you to describe almost any organization - by allowing the "isPrimary" field in table 4, it would even be possible that the same person who is your "HR boss" over all shows up as your "project leader" in a second team, and might even be reporting to you on the emergency response team...

As for visualization - there are two obvious ways to do this with the above structure. The first is "strictly hierarchical" - only showing people underneath their primary manager. This is the "HR org chart", and everyone shows up only once.

You can have a second chart which is "team based". Now, every team has its own organization, and the same person can show up in multiple teams. How these teams relate to each other can be tricky - but in principle, table 3 should provide what you need with the BelongsToTeam field.

I look forward to hearing your thoughts on this!