Author |
Topic |
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-01-15 : 02:12:30
|
Top level - CEOMiddle level - 3 ManagerLow level - 3 Employee1 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 selectnull,1,'CEO' union all select1,2,'manager1' union all select1,3,'manager2' union all select1,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
|
seehttp://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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? |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-15 : 08:00:35
|
quote: Originally posted by waterduck Top level - CEOMiddle level - 3 ManagerLow level - 3 Employee1 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 selectnull,1,'CEO' union all select1,2,'manager1' union all select1,3,'manager2' union all select1,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. |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2013-01-15 : 08:14:04
|
hi james, thx, thats exactly what khtan teach me as well. |
|
|
|
|
|