hierarchy table(s)?

[Godfrey]

Hi,

Please can you help me with db design? I have to build a table or tables that will accurately reflect a hierarchy. The hierarchy design is essentially the tree or file view we see on our computers file explorer. I plan on using a Tree List component from VB6 in a Client App to display these hierarchical relationships.

I am going to relate this first to what we see in Explorer. The business rules would be as follows:

1. There is a Root (top-level)- There may be many Root Levels.
2. There are Parent Enitities beneath the Root level. The Parent Entity relates back to the Root level. There can be many Parent Entities.
3. There are Child Entities that relate back to the Parent Entity. There can be many Child Enitites. Child Entities can have Child Entities (which would make them a Parent to that particular Child Entity.) There can be an unlimited number of these Child-to-Child Entities as well.

4. There are Files beneath the Parent and Child entities. There many be an unlimited number of Files associated with those levels.

To related this to my specific task:

1. ClientID's - There can be many/an unlimited number (relates back to 1. Root above).

2. ClientID's have Busness Entities ID's (for example Departments, like the Acct. Dept.). There can be many/an unlimited number of BusinessEntityIDs. (Relates back to 2. Parent Entities above).

3. BusinessEntityIDs have have ChildEntityIDs (ie: using the Acct. Dept., again, the accounting dept can have Accts Payable and Accts Recievable). There can be many/an unlimited number of ChildEntityIDs. (Relates back to 3. ChildEntities above). Also, these "sub deparments" --entities can have child entities beneath them. So, a ChildEntityID can be a Parent to other ChildEntityIDs. Again, an unlimited number of these relationships can exist.

4. BusinessRoleFunction (ID?) --There are job descriptions at this level. Again, an unlimited number may exist.

My initial table would look something like this (i think!):

ClientID
ParentEntityID
ChildEntityID
BusinessRoleFunctionID

Okay...now I'm lost. How do I effectively design this? I suppose it could also be viewed as an Organization Chart.

Thank you very much for your help!

Godfrey

 

[DanJR]

Hi Godfrey,

I'm a little confused how you have related your problem domain to the windows explorer example, since i get the impression that you have more than one type of entity: e.g. [tt]CLIENT[/tt] and [tt]BUSINESS UNIT[/tt].

4. BusinessRoleFunction (ID?) --There are job descriptions at this level. Again, an unlimited number may exist.

sorry, i'm a little lost at what this means too - which level? How are these "roles" assigned e.g. to a [tt]BUSINESS UNIT[/tt] or to a [tt]CLIENT[/tt]...?

I get the impression that [tt]BUSINESS UNIT[/tt] is the only heiercharical bit that you are wanting to model?

Nevertheless, if you want to model hierarchies/trees in a relational model then it depends on where you want your performance to lie e.g. do you want fast inserts (Adjacency List Model) or do you want fast retrival of all sub-ordinates for a given node at the expense of slower inserts (Nested Sets Model) etc etc.

I recommend that you at least reference Joe Celko's work, search his name with Google with reference to hierarchies as i'm pretty sure there are websites that explain modelling hierarchies within the relational model (ok, i found this link). In particular, the book by Joe Celko entitle "Trees and Hierarchies in SQL For Smarties" is dedicated to this topic. I have this book and think its worth reading for its price.

cheers,
dan

 

[Godfrey]

Hi Dan,

I appreciate you taking the time to read my thread. The hierarchy will look something like this:

1. ClientID - (A client has deparments)
2. ParentBuinessDeptEntityID - (Dept. may have sub Depts)
3. ChildBuinessDeptEntityID - (Sub dept. have job functions)
4. JobRoleFunctionID - (Jobs have employees/staff)
5. StaffID - (The people who do the job.)

So, the lineage, is 1,2,3,4,5.

Retrieval speed will be more important than the insert speed.

I too, spent a good part of yesterday scouring the net looking for some information that will assist with a solid design. These were both helpful with what I have in mind:

http://www.dbmsmag.com/9809d05.html

and

http://www.odtug.com/Handouts_05/Kramm DATA.pdf

I'll spend today doing some hand-written mock-ups, using the "helper" or entity map tables they refer to. Maybe I'll see it then. I'm a mixed up over how many of these helper tables I will need.

However, if anyone sees the solution, I sure would appreciate being pointed in the right direction.

Thanks!