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

Question

I have an interesting modeling problem. I am trying to create an org diagram on a website (the backend server is C # / SQL, and the interface is the javascript / google orgchart API, but the essence of the modeling problem is more general, so I did not include any of the specific technologies in the tags below, since the problem is not related to specific technical issues.

I have the following 4 database tables:

• Command - has fields Id, Name, ParentTeamId (this is another row in one table)
• A position is a position in a team. Fields: Id, TeamId, IsTeamHead, etc.
• Face - represents a person (without reference in this table to any other table). Fields: Id, FirstName, LastName, etc.
• PersonPosition - represent people in positions (this joins two tables). Fields: Id, PersonId, PositionId, StartDate, EndDate

When I have a simple vanilla organ diagram, it works fine because I basically scroll through each command (since each has its own ParentTeamId ) and creates a hierarchy of commands and shows the position in this command (using the TeamId field) with "Ishead" = true and show the person who is associated with this position as a chapter.

My problem is that (as it is not so rare), there are people who have now been given many responsibilities - they essentially have two different positions. Joe previously led marketing, and Bill was the regional head, but Joe left

Before the head of the marketing department and the regional leader, who were two different people (2 different positions ). So Bill leads marketing, but is also a regional manager in the United States. I am trying to figure out what is the right way to model and visualize this.

The first part of the modeling problem is to decide whether I should model it as two different positions . If so, I can have several entries in this PersonPosition table (both with the same PersonId ), but the problem is that it seems to me that I am re-reading the number of positions.

In addition, in terms of visualization, the same person will appear in two places. Perhaps this is correct from a functional point of view, but it seems strange that you will have the same person who will be displayed several times (maybe this is not so strange, but I wanted to get feedback on what people saw in this case as expected visualization and what seems acceptable perhaps should stimulate modeling)

Any suggestions for the “right” way to do this?

Answer 1

It sounds like you need something similar to this:

From all possible position types, we create a set of positions that exist in a particular team ( TeamPosition ) and identify the person who fills each position ( TeamPosition.PersonId ¹ ).

The head is represented by the “reverse” FK2 foreign key in Team ² . Unlike the logical flag, this naturally ensures that there can be no more than one position on each team.

This model also allows you to lead different teams with different types of positions: for example, one team may be headed by a "marketing manager" and the other by a "senior technical officer."

It is still possible that the same person performs several positions (including head position), which, as I understand it, are compatible with your requirements. And if this is true, then I really do not see a problem with displaying the same person as a member of several teams in the user interface. Alternatively, you can designate one of the person’s positions as “primary” (using the “reverse” FK, similar to the previous one), and then simply show the main position and the “More ...” button next to it (or similar).

---

¹ Make NOT NULL if there is no vacant team position. If the same position can exist several times for each team, either move PersonId to TeamPosition PK, or add a new PositionNo field to PK. If the same person cannot have several positions within the same team, add an alternative key to {TeamId, PersonId} .

² Unfortunately, MS SQL Server is a little more squeamish than some other DBMSs and will refuse to perform link actions (for example, ON DELETE CASCADE) on circular links like this. If you need reference actions, execute them using INSTEAD OF triggers.

Answer 2

I think you should consider the difference between “position” and “role”. In many organizations, several roles may exist: security coordinator, procurement, accounts payable, registrar, ... Often, especially in small organizations, one person can act in different roles at different times. Perhaps even they tell different people when they act in different abilities (for example, an emergency responder can inform the security coordinator, but the buyer reported to the chief of the operation.)

In order to correctly reflect these things, the relationships in your database should reflect, as far as possible, the relationships that exist in real life. This probably means that you will have several tables (as you already have), but it will be clean.

Another thing to keep in mind (and perhaps worth reflecting in your database) is that many organizations have a matrix: people can be in specific project teams and in some specific organizations. Electrical engineers can report to the electrical engineer manager, but they can work on various projects / products and thus belong to different project teams.

Removing everything that’s accurate is difficult. Here is my suggestion (not like yours, but with some settings):

Table 1: employees Person name, employee ID, start date, salary, vacation, ...
This is a table that says when you receive money, how much time you have, what is your “HR” status. There is only one of you - in this table there is only one of you and is used for those things that cannot be doubled (although we would all like two payment checks).

Table 2: Managers Manager ID, Report ID, Report Type
In this table, for each manager, the people who report about them and what are the relationships are indicated. You can have “primary” relationships and other relationships: “project manager”, “team leader”, ... The “primary” manager can make decisions like HR with the help of “other” managers.

Table 3: teams Team name, team identifier, manager identifier, BelongsToTeam, team description, ...
A table that describes each "organizational object", with any supporting information that may be useful. BelongsToTeam allows a hierarchical command structure, which helps with visualization.

Table 4: Roles Role name, team ID, employee ID, isPrimary
This table describes who is in this role. An employee with several roles will be displayed several times in this table and can report to different managers depending on their role. I added the "isPrimary" field here - not sure if this is redundant. In a sense, if you start with the “primary” employee role 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 main manager ... I’m worried that you You may encounter inconsistencies if you leave this in both places.

I believe that the above allows you to describe almost any organization - by allowing the "isPrimary" field in table 4, it would be possible that the same person who is your "HR boss" over everything appears as your "leader" project to the second team and may even inform you of the emergency response team ...

As for visualization - there are two obvious ways to do this with the specified structure. The first - "strictly hierarchical" - only showing people under their main manager. This is an "HR org chart" and each is displayed only once.

You may have a second team-based schedule. Now each team has its own organization, and the same person can appear in several teams. How these commands relate to each other can be tricky - but in principle, Table 3 should provide what you need in the BelongsToTeam field.

I look forward to hearing your thoughts about this!

Answer 3

As you say, it is usually for employees (formally or informally) to perform more than one role in an organization. However, with regard to HR / salary / other administrative departments, the employee will officially occupy only one position. It is important to ensure this to ensure that people are not paid twice or otherwise for / for compensation, etc.

This can be done by adding the hr/admin_position to the person table. Then, the person_position table can be used to record all the roles that they actually perform.

It also allows people to assign an "org chart" position that is different from what HR considers. This often happens when a group leader leaves; the junior team member will “advance” to the intermediate team, taking on additional responsibilities. However, no corresponding increase in wages or other benefits has occurred, as HR still believes that they are in a lower position. You can add additional columns to include notes or flags to indicate that these are temporary positions.

Answer 4

“The first part of the modeling problem is to decide whether I should model it as two different positions. If I do, I can have several entries in this PersonPosition table (both with the same PersonId), but the problem is that it seems to me that I overstate the number of positions. "

That's no problem at all. You mentioned that if someone wants to "count the number of positions", but turns to the personposition table to do this, it makes a mistake. Due to the fact that you do not understand the database or data model or something else, the fact is that if you need to access the table of positions in order to calculate the number of positions.

"In addition, in terms of visualization"

Just make it clear here that if “visualization” is your actual problem, the database people are usually not the ones who will help you.

“the same person will appear in two places. Perhaps this is correct from a functional point of view”

If this is a business rule, then this is a business rule. You, as a rule, do not interrogate them. What if different people share the same position? For example. two timers, each of which performs [its half] the same work.

", but it seems strange that you will have the same person who will be displayed several times (maybe it’s not so strange, but I wanted to get feedback on what people saw in this case as the expected visualization and what seems acceptable, perhaps should stimulate modeling). "

Ok, you just said. If this is a business rule, then this is not strange. You may have never encountered such a scenario before, but who cares?

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

Not really. All you have to do is find out exactly what information should be provided. All existing empty-name positions if they are not currently occupied? Only effectively occupied positions, with any one name, perhaps several persons holding this position? etc. etc.