StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A common approach to solving this kind of hierarchy traversal problem is to use a technique called <strong>visitation numbers</strong>, which I describe in detail in <a href="https://stackoverflow.com/questions/7420885/how-do-i-design-a-database-to-store-properties-selecting-attributes-by-synonyms/7423459#7423459">my answer to this question</a>. Using visitation numbers, you can easily find the list of all nodes that are below any given node anywhere in the hierarchy.</p> <p>Note that you would still record each employee's direct reporting superior using an involuted foreign key on the employee table.</p> <p>For your case, you also have dotted-line reporting (Staff C to Section Head C) outside of the regular reporting hierarchy. This means that you will need two approaches. The first is to use visitation numbers for regular reporting, where managers can see all of their direct and indirect reports, and then something else for dotted-line reporting.</p> <p>It seems like you have two kinds of rules for dotted line reporting. Some dotted line supervisors can see sub-staff and others can only see their dotted line direct-reports. Since people can have more than one dotted line supervisor, you need to add an <strong>intersection table</strong> to record these dotted line relationships. This intersection table can also include a <strong>flag attribute</strong> that indicates whether or not the dotted line supervisor is able to see the only the immediate dotted line subordinate or that person and all of their subordinates as well.</p> <p>Either way, the dotted line relationships are recorded directly between an employee and their supervisor and the regular reporting relationships (which may be indirect) are managed by visitation numbers.</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