Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hierarchy design

Author  Topic 

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-01-15 : 02:12:30
Top level - CEO
Middle level - 3 Manager
Low level - 3 Employee

1 ceo have 3 manager and 3 manager share same 3 employee. How it should look in parent-child structure? Duplicate the child?

declare @emp table(parent_id int, id int, name varchar(20))
insert into @emp select
null,1,'CEO' union all select
1,2,'manager1' union all select
1,3,'manager2' union all select
1,4,'manager3' union all select
/*(2,3,4)*/2,5,'employee1' union all select
/*(2,3,4)*/2,6,'employee2' union all select
/*(2,3,4)*/2,7,'employee3' union all select
/*(2,3,4)*/3,5,'employee1' union all select
/*(2,3,4)*/3,6,'employee2' union all select
/*(2,3,4)*/3,7,'employee3' union all select
/*(2,3,4)*/4,5,'employee1' union all select
/*(2,3,4)*/4,6,'employee2' union all select
/*(2,3,4)*/4,7,'employee3';

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-15 : 03:06:58
see

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-01-15 : 04:00:14
errr, i know it can be done using recursive... but is there a 'needs' to duplicate lets say the employee?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-15 : 05:41:56
quote:
Originally posted by waterduck

errr, i know it can be done using recursive... but is there a 'needs' to duplicate lets say the employee?


what do you mean by duplicate employee? where is it duplicating the employee? please illustrate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-15 : 08:00:35
quote:
Originally posted by waterduck

Top level - CEO
Middle level - 3 Manager
Low level - 3 Employee

1 ceo have 3 manager and 3 manager share same 3 employee. How it should look in parent-child structure? Duplicate the child?

declare @emp table(parent_id int, id int, name varchar(20))
insert into @emp select
null,1,'CEO' union all select
1,2,'manager1' union all select
1,3,'manager2' union all select
1,4,'manager3' union all select
/*(2,3,4)*/2,5,'employee1' union all select
/*(2,3,4)*/2,6,'employee2' union all select
/*(2,3,4)*/2,7,'employee3' union all select
/*(2,3,4)*/3,5,'employee1' union all select
/*(2,3,4)*/3,6,'employee2' union all select
/*(2,3,4)*/3,7,'employee3' union all select
/*(2,3,4)*/4,5,'employee1' union all select
/*(2,3,4)*/4,6,'employee2' union all select
/*(2,3,4)*/4,7,'employee3';



This is an unusual situation indeed! I assume this is what they used to call matrix management in my old company (where everyone is responsible for everyone else but really no one is responsible for anyone). My social commentary aside, what you have is a many to many relationship (that possibly can be multiple levels deep).

Duplicating a single employee multiple times does not seem like a good idea. Instead, have two tables - an Employee table and a OrgStructure table. In the Employee table you would simply have each employee listed once, with the employee_id being the primary key. The OrgStructure table would be a many-to-many relationship between employee and his/her manager.

That would take care of representing the data and would be a normalized representation. Now how to query it is another matter. You certainly would have to make some modifications to the recursive queries that are typically used to map out the organizational hierarchy.
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-01-15 : 08:14:04
hi james, thx, thats exactly what khtan teach me as well.
Go to Top of Page
   

- Advertisement -