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
 Database Design and Application Architecture
 Designing a multilevel company structure

Author  Topic 

Tzwen
Starting Member

7 Posts

Posted - 2014-05-28 : 06:54:41
Hi everybody!

I try to build a database model for the following structure: I have companies with up to 3 hierachical levels. For each unit I have a value (these values are given randomly and duplicates between companies (not within) are possible. Let us say (1 Level: 222-Amazon, 2 Level: 441-Amazon: Germany, 542-Britan, 3 Level: 6-Distribution, 99-Shop, 124-Programming, 5-HR. Of course for each company this is different. What I did is:

Table1:
ID_Worker
CompanyName
ID_CompanyLvL1
ID_CompanyLvL2
ID_CompanyLvL3
...
Table2:
ID_CompanyLevel1
Slot1
Slot2
...
Table3:
ID_CompanyLevel2
Slot1
Slot2
...

But with this approach I have the following problem: If two companies have the same number for a CompanyLevel1(2 or 3) unit I cannot distingush them anymore.

Another approach that is not working is

Table1:
ID_Company
ID_Worker
ID_CompanyLevel1
...
Tabel2:
ID_CompanyLevel1
Slot1
ID_CompanyLevel2
...
Table3:
ID_CompanyLevel2
Slot
ID_CompanyLevel3
...

With this approach I cannot identify which person is in e.g. which level2 unit. Could anyone help me with this i just cannot come up with the right design.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-28 : 11:23:08
I'm not following the objective completely but generally a hierarchy should be achieved with a single table (not a table per level). Sql server offers several ways to model a hierarchy.
- very common way is to have an (ID, ParentID) structure where the ultimate parent's ParentID is either itself or null.
- Sql server now offers a new type: hierarchyid.
- XML datatype can be used as well

other tables would be added to model one-to-many or many-to-many relationships between company entities and a other entities (like workers for example).







Be One with the Optimizer
TG
Go to Top of Page

Tzwen
Starting Member

7 Posts

Posted - 2014-06-06 : 05:52:09
Thank you very much, that was helpful!
Go to Top of Page
   

- Advertisement -