Choosing right data structure for company tree

Question

I have a slight problem determining the right data structure for one of my problems at work. What I have to do is create a "company structure tree". In short - someone is a boss of all bosses (so he is first (or zero) level of the tree) and then there are rest of the workers, but as you know, someone can be someone elses boss etc. The tree has to go max 5 levels deep. I don't really know how to create this, even from database point of view.

I've already tried custom ViewModels and some Dictionaries, but I feel like there's a working way for this, that doesn't include 5-level nested Dictionary or List.

If anyone would know how to do this, any help would be greatly appreciated.

Cheers.

score 2 · Accepted Answer · answered Feb 02 '16 at 08:09

I suggest you use a Self-Referencing Foreign Key on the Employees table

Employees

| ID    | Name  | ManagerID |
|-------|-------|-----------|
| 1     | Tom   | NULL      |
| 2     | Andy  | 1         |
| 3     | Heidi | 2         |

Where ManagerID is a Foreign Key on the ID.

Advantages:

You are not limited to 5 levels
You can have multiple root-entries
This principle is appicable also to other self-referencing structures like menu-trees etc.
Easy to extend if you need n:n connection you could go unnormalized (just semicolon-separate ManagerID's per field 1;2) or normalized with an own cross-table.

score 1 · Answer 2 · answered Feb 02 '16 at 08:00

1

What about this structure:

CREATE TABLE Employee (
    [Id] NOT NULL IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(250),
    ...
    [Supervisor] INT NULL FOREIGN KEY REFERENCES Employee([Id])

)

Nullable supervisor means, that this is root of a tree. From C# perspective, this can be represented by Composite Pattern - object contains list of objects shared same interface (in our case exact type).

answered Feb 02 '16 at 08:00

Fka

6,044
5
42
60

So basically i can use this in my Users table and reference a supervisor to itself, without creating another table? – drajvver Feb 02 '16 at 08:07
Exactly. This have also advantage of having multiple reportees. – Fka Feb 02 '16 at 08:08

score 0 · Answer 3 · edited May 23 '17 at 11:59

0

On the database side Common Table Expression (CTE) is what you need. Also look at this.

And on the UI side, you can use D3 tree. Note D3 has got a learning curve, but its worth. Do take a look at this.

edited May 23 '17 at 11:59

Community

1
1

answered Feb 02 '16 at 08:13

VivekDev

20,868
27
132
202

I don't think he is asking for a concrete implementation framework but rather for the right way to do the data layer. Also, why suggest a javascript library when he is obviously just starting out with C#? – KarmaEDV Feb 02 '16 at 08:18

score 0 · Answer 4 · answered Feb 02 '16 at 08:43

0

This is a simple json structure. I know you're dealing with c# but doing this in mongodb or some other nosql would be extremely fast.

answered Feb 02 '16 at 08:43

Shai UI

50,568
73
204
309

Choosing right data structure for company tree

4 Answers4