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
 Database Design and Application Architecture
 Designing a multilevel company structure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tzwen
Starting Member

7 Posts

Posted - 05/28/2014 :  06:54:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 05/28/2014 :  11:23:08  Show Profile  Reply with Quote
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 - 06/06/2014 :  05:52:09  Show Profile  Reply with Quote
Thank you very much, that was helpful!
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