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

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

Looks like you need something similar to this:

Out of all the possible position "types" (Position) we build a set of positions that exist in the specific team (TeamPosition) and identify the person that fills each position (TeamPosition.PersonId¹).

The head is represented by the "reverse" foreign key FK2 in Team². Unlike a boolean flag, this naturally ensures there cannot be more than one head position per team.

This model also allows different teams to be headed by different types of positions: for example one team might be headed by a "head of marketing" while the other is headed by a "senior technical officer".

It is still possible to have the same person fulfill multiple positions (including head positions), which is compatible with your requirements, as far as I understand. And if that's true, then I don't really see a problem in showing the same person as a member of multiple teams in the UI. Alternatively, you could designate one of the person's position's as "primary" (using a "reverse" FK similar to above) and then just show the primary position and a "More..." button beside it (or similar).

---

¹ Make it NOT NULL if there cannot be a vacant team position. If the same position can exist multiple times per team, either move the PersonId to TeamPosition PK, or add a new field PositionNo to the PK. If the same person cannot have multiple positions inside the same team, add an alternate key on {TeamId, PersonId}.

² Unfortunately, MS SQL Server is a bit more squeamish than some other DBMSes, and will refuse to do referential actions (such as ON DELETE CASCADE) on circular references like this. If you need referential actions, implement them via INSTEAD OF triggers.