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.
| 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_WorkerCompanyNameID_CompanyLvL1ID_CompanyLvL2ID_CompanyLvL3...Table2:ID_CompanyLevel1Slot1Slot2...Table3:ID_CompanyLevel2Slot1Slot2...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 wellother 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 OptimizerTG |
 |
|
|
Tzwen
Starting Member
7 Posts |
Posted - 2014-06-06 : 05:52:09
|
| Thank you very much, that was helpful! |
|
|
|
|
|
|
Designing a multilevel company structure