StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat is the best way to model an organization using people, positions and teams when people have multiple jobs?
text
Body
copied!<p>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.</p> <p>I have the following 4 database tables:</p> <ol> <li><strong>Team</strong> - which has fields Id, Name, ParentTeamId (which is another row in the same table)</li> <li><strong>Position</strong> - which represents a position within a team. Fields are Id, TeamId, IsTeamHead, etc</li> <li><strong>Person</strong> - represents a person (no linkage in this table to any other table). Fields are Id, FirstName, LastName, etc</li> <li><strong>PersonPosition</strong> - represent people in positions (this bridges the two tables) Fields are Id, PersonId, PositionId, StartDate, EndDate</li> </ol> <p>When I have a plain vanilla org chart this works perfectly because i basically loop through each <strong>Team</strong> (since each has its <strong>ParentTeamId</strong>) and build up a team hierarchy and show the <strong>position</strong> in that team (using the <strong>TeamId</strong> field) with "Ishead" = true and show the person that is associated with that position as the head. </p> <p>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</p> <p>Before the head of marketing and the regional head, which was 2 different <strong>people</strong> (2 different <strong>positions</strong>) 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.</p> <p>The first part of the modeling problem is to decide if I should model this as two different <strong>positions</strong>. If I do, i can have multiple entries in this <strong>PersonPosition</strong> table (both with the same <strong>PersonId</strong>) but the issue there is that it feels like I am overcounting number of positions.</p> <p>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)</p> <p>Any suggestions for the "right" way to do this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload