CODEWITHSUNDEEP
c#sql-serverdatabase-designhierarchical-data
Shailesh Jaiswal
Shailesh Jaiswal

Reputation: 3654

What should be the table schema for hierarchical data?

I have just attended an interview, where I was given a question including this scenario and some sample data:

A is a boss of B, C and D. D is the boss of E and F. Then F is the boss of G and H, ie we have hierarchical data.

In another way we can say that there is project manager, team leader then developer. Now we want to create table to store the data.

Further, a developer can later become a project manager.

So how should we create the table? What columns should the table contain?

My answer was:

We should have one table for project manager which contains columns PM_ID and PM_Name where PM_ID is the primary key. Then we should create another table for team leaders. In that table we should have TL_ID as primary key, TL_Name and PM_ID as foreign key.

Then we should create developer table in that we should have D_ID as primary key, D_Name and then TL_ID as foreign key.

Can you please tell me whether I have provided the correct schema for the table?

If anything is wrong then can you please provide me with what should be the correct schema for the above case?

Upvotes: 2

Views: 369

Answers (4)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95572

  • When a developer becomes a project manager, does that person cease to be a developer?
  • All these relationships can change over time. Do you need to know which relationships have changed, and when they changed?
  • Does every person have to have a boss?
  • Does every boss have to have subordinates?
  • When a boss dies or leaves, what happens to that boss's subordinates?
  • Can a developer work for more than one team leader at a time?

Sometimes, interview questions are less about the structure, and more about your ability to recognize and resolve ambiguity in the requirements. (Also, not that it's necessarily relevant here, but when most people point at something and say "hierarchy", if you look closer you'll find "network".)

Upvotes: 1

Andy Hayden
Andy Hayden

Reputation: 375485

I think the simplest table is going to contain this information (in just one table):

EmployeeID | LineManager | Role
-----------+-------------+-----
A          | A           | boss
B          | A           | pm
C          | A           | pm
D          | A           | pm
E          | D           | tl
F          | D           | tl
G          | F           | dev
H          | F           | dev

Upvotes: 0

Sico
Sico

Reputation: 1183

You more than likely need a hierarchy table http://msdn.microsoft.com/en-us/library/bb677174%28v=sql.100%29.aspx and then use a cte query to display the results.

Upvotes: 2

AD.Net
AD.Net

Reputation: 13399

In this case your table needs to have a Foreign key to itself.

Upvotes: 0

Related Questions