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 |
|
.net_web_guy
Starting Member
2 Posts |
 Posted - 2011-06-05 : 18:27:31
|
| Hi Everyone,We are in the process of creating a .net web app and a portion of this application will allow a user to map an organizational hierarchy role to an employee. I am having an issue with designing the database tables (SQL Server 2005) that will support this functionality within the application and I wanted to see if anyone had any pointers or suggestions.So within this application, the term organization will represent the root level hierarchy. An organization may have many divisions and a division many have many subdivisions. The problem that I am running into is that on our prototype designs, the architects have a link that will allow any given user to keep adding a lower level hierarchy under a subdivision type. I feel as if I need to know up front how many levels will be needed so I can design the database accordingly, but at this point it is really unknown and they insist upon providing this capability.Here is what I had planned on doing to capture the hierarchy down to the subdivision level.First, create an organization table that will hold the distint list of organizations. An identity column will be propagated down to the next table.Second, create a division table that will hold the distinct list of divisions and also hold that divisions organization identity value.Third, create a subdivision table that will hold the distinct list of subdivision values and also hold the identity values for the division and organization hierarchy that relate to that subdivision. I will use the identity value from this table and include it in my main record. From here, I can derive the text values for the division and organization (view or something similar) because it will all be tied together. At this point I can't wrap my mind around allowing a user to create another hierarchy level that is lower than subdivision. If we do keep this functionality, I need to come up with another approach on the database side because what I have laid out above will not support it.So does anyone have any ideas that may push me in the right direction or have any of you faced a similar problem in the past? Does this approach make sense? Thank you for taking the time to read this post. Any comments are definitely appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-06-06 : 11:29:43
|
| A classic model for a hierarchy is to have a single table with ID/ParentID columns. So for you: [divisionID] as the primary key and [ParentDivisionID] will be one of the attributes of that division. That way a single table can hold an unlimited number of levels. The root will have a NULL parentID value. There are lots of posts here describing how to query and display hierarchies.Be One with the OptimizerTG |
 |
|
|
.net_web_guy
Starting Member
2 Posts |
Posted - 2011-06-06 : 11:33:25
|
| Thanks TG, that is exactly what I did. I guess I was getting myself confused with some of the details and once I took a step back, I implemented the ParentDivisionID as you stated above. My root level divisions will just have a NULL in this column. Thanks for the reply!!! |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-06-06 : 11:50:44
|
| My pleasure - good luck!Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Organization design hierarchy