SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hierarchy design
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 01/15/2013 :  02:12:30  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/15/2013 :  03:06:58  Show Profile  Reply with Quote
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

Malaysia
965 Posts

Posted - 01/15/2013 :  04:00:14  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/15/2013 :  05:41:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 01/15/2013 :  08:00:35  Show Profile  Reply with Quote
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

Malaysia
965 Posts

Posted - 01/15/2013 :  08:14:04  Show Profile  Reply with Quote
hi james, thx, thats exactly what khtan teach me as well.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000