SQL - 在任意级别加入树时是否有推荐的方法？

Question

thanks for reading. I'm sure there is a sensible way to do what I am trying to do, but my lack of DB experience is letting me down.

I'm going to simplify greatly to pin point my question..... I have 3 tables

• a table called employees (employee_id, name, parent_id etc).
  • a table called projects (project_id, project_name etc)
  • a table linking the two called assigned_projects (id, employee_id, project_id)

The Employee table is up to 6 levels deep (although can be as shallow as 3 in some places) and contains about 900 employees. e.g.

    A
   / \
  B   C
     /|\
    D E F

Now, projects can be assigned at any level. If I assign it to employee A, then it should be linked also to all his subordinates (B->F). If I assign it to C, then it should be linked to D+E+F, but I would also want to see that it is within A's remit.

When I bring up an employee's profile, I am using a CTE anchored at the appropriate employee to recursively find all subordinates. The issue I have is, for each of those subordinates, how to I join to the assigned_projects table to see what projects are in this person's remit?

Options I considered:

• do the CTE piece first, then within the php, iterate through all employee_ids to run separate queries that pull project_ids from the assigned_projects table
• when initially assigning a project to an employee, iterate through the tree and add an entry to assigned_projects for each appropriate employee. Problem here is that if employees move around the tree (change jobs), I want them to inherit/remove projects accordingly.

To be clear, when bringing up profile of C, I would want to see all projects assigned to A,C,D,E,F. When bringing up profile of B, I would want to see all projects relating to A or B.

It seems like there should be a way of doing this in SQL using some cleverness that I just dont have the experience for? Or should I change my table structure.... I'm open to that too.

Hope I explained that properly. Thanks for any help, appreciate your time.

Answer 1

I think that you may have created a world of hurt for yourself. The thing with this kind of tree representation is that you will end with some form of recursion in order to achieve your goal. The performance will probably suck and changes will be complicated. I suggest that you read http://ebooks.cawok.pro/Morgan.Kaufmann.Joe.Celkos.Trees.and.Hierarchies.in.SQL.for.Smarties.May.2004.eBook-DDU.pdf for a better alternative.

It's not without its headaches. You really need stored procedures for INSERT and DELETE operations, but the kind of operation you are talking about is far simpler once you have a nested set model tree set up. To find the data you want you would simply join your tree table to the project table and query for all projects where the employees assigned have leftvalue and rightvalue between the manager's leftvalue and rightvalue. To assign to employees by virtue of their manager you look for the node with a MAX(leftvalue) < the employee's leftvalue and a MIN(rightvalue) > the employee's rightvalue.

IF OBJECT_ID('tempdb..#tree') IS NOT NULL
DROP TABLE #tree
create table #tree (emp varchar(10), lval int, rval int)

INSERT INTO #tree 
SELECT 'Bob',1,12
UNION
SELECT 'Harry',2,5
UNION
SELECT 'Barry',6,11
UNION
SELECT 'Dave',3,4
UNION
SELECT 'Mavis',7,8
UNION
SELECT 'Maud',9,10

IF OBJECT_ID('tempdb..#Projects') IS NOT NULL
DROP TABLE #Projects
CREATE TABLE #Projects(projectid INT,projectname VARCHAR(20))
INSERT INTO #Projects
SELECT 1,'Project Mammoth'
UNION
SELECT 2,'Project Minnow'
UNION
SELECT 3,'Project medium'

IF OBJECT_ID('tempdb..#ProjectReleations') IS NOT NULL
DROP TABLE #ProjectRelations
CREATE TABLE #ProjectRelations(emp VARCHAR(10),projectid INT)
INSERT INTO #ProjectRelations
SELECT 'Barry',1
UNION
SELECT 'Maud',2
UNION
SELECT 'Dave',3


--Find Mavis' immediate manager (Barry)
SELECT
    *
FROM
    #tree 
    LEFT JOIN
    #ProjectRelations ON 
        #tree.emp=#Projectrelations.emp
    LEFT JOIN
    #Projects ON
        #Projectrelations.projectid=#Projects.projectid
WHERE
    #tree.lval=(
        SELECT
            MAX(tree.lval)
        FROM
            #tree
            inner join
            #tree as tree ON
                tree.lval < #tree.lval 
                AND
                tree.rval > #tree.rval
        WHERE
            #tree.emp='Mavis'
    )

--Find all projects under a particular employee (including projects assigned to that employee). If you want only projects assigned to subordinates use #tree.lval > rather than #tree.lval >=
SELECT
    *
FROM
    #tree 
    INNER JOIN
    #ProjectRelations ON 
        #tree.emp=#Projectrelations.emp
    INNER JOIN
    #Projects ON
        #Projectrelations.projectid=#Projects.projectid
WHERE
    #tree.lval >=   (
        SELECT
            lval
        FROM 
            #tree
        WHERE
            #tree.emp='Bob'
    )

I don't know if this helps, but trees are a pain in SQL, and my personal feeling is that of the various evils, nested sets are less horrific.