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:
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).