Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>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.)</p> <p>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.</p> <p>The other thing to keep in mind (and possibly worth reflecting in your database) is that many organizations are <em>matrixed</em>: people may be on certain <em>project teams</em>, and part of certain <em>organizations</em>. 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.</p> <p>Capturing all that accurately is hard. Here is my suggestion (not unlike yours, but with some tweaks):</p> <p><strong>Table 1: employees</strong> Person name, Employee ID, Start Date, Salary, Vacation, ...<br> 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).</p> <p><strong>Table 2: managers</strong> Manager ID, Report ID, Report kind<br> 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.</p> <p><strong>Table 3: teams</strong> Team name, Team ID, Manager ID, BelongsToTeam, Team description, ...<br> 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.</p> <p><strong>Table 4: roles</strong> Role name, Team ID, Employee ID, isPrimary<br> 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.</p> <p>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...</p> <p>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.</p> <p>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.</p> <p>I look forward to hearing your thoughts on this!</p>
    singulars
    1. This table or related slice is empty.
    1. POWhat is the best way to model an organization using people, positions and teams when people have multiple jobs?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USFloris
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYour statement "there's a difference between a position and a role" is CRITICAL. As you say, but I will repeat 'cos it's so important, management/reporting hierarchy is not related to teams at all (whether or not the management style is officially matrixed). Many cross-functional teams are comprised from people from people selected from all over an organisation. Indeed a more junior employee might head a team with a more senior staff member as 'merely' a team member. This leads to a slight improvement: you also need Organisation as a separate table to model departments and lines-of-business.
      singulars
      1. PO
      1. USAndrew Alcock
    2. CO@Floris - i am curious of your opinion around using a PositionTeam table (as Branko suggests) to achieve this idea that a single position can be a different "role" in multiple teams
      singulars
      1. PO
      1. USleora
    3. COI think it is better to show the direct link from employee to team (or composition of team in terms of employees) rather than from position; multiple people may have the same position (title). The exception would be where someone is part of a team _ex officio_ - but those situations don't really need their own table.
      singulars
      1. PO
      1. USFloris
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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