Organizational Hierarchy

[djb]

Hi Group,

I have set up a table with organizational data. I wish to represent the relationship of the organizations as they exist in a hierarchy. I have placed a such information in other fields such as the Level that the organziation is at in the hierarchy as well as the parent or immediate organization that each reports up to.

So ...

HQ
-- Branch1
-- Branch2
--OfficeA
--OfficeB
--SubOffice1
--Branch3

Etc. is a generic example of the hierarchy.

So how did I at least set up the table correctly and how would I go about querying the data to do reporting and data analysis?

Any help in the right direction would be appreciated.

By the way, I even set up a junction table and manually entered in the Organizations PK value in one field and the other Organziations PK (the Parent or Reports Too Org) in anther field. This at least helps me do some analysis but I can't seem to represent the hierarchy with this. Also, I don't know how to update this table in the Form as I am entering in the organzational data.

Sorry about the multiple items in this post but it is all interelated.

Thanks for the help in advance.

djb

 

[Tim Ferguson]

So how did I at least set up the table correctly and how would I go
about querying the data to do reporting and data analysis?

Without much information, it looks as though you have at least four
separate entities:

HQs(*HQ-ID, TelexNumber, ...)

Branches(*HQ-ID, *BranchNum, ChiefExec, ...)

Offices(*HQ-ID, *BranchNum, *OfficeCode, Manager, ...)

SubOffices(*HQ-ID, *BranchNum, *OfficeCode, *SubOffNo, Submanager,...)


If there are important aspects of these things that are shared, you might
consider creating a sub-classing arrangements, with each of these things
foreign-keyed into Organizations.

It is hard to be more specific without more information about what you have
to achieve.

Best wishes


Tim F

 

[djb]

Hi Tim

Hmmm ... Basically I am looking to represent my organizaitonal hierarchy in my Org table. Let me change the example

Hopefully this will clarify my situation

Tom reports to Dick who reports to Harry who reports to Sam

-Sam - Presiden
--Harry - Vice Presiden
---Dick - Directo
----Tom - Manage

Each of these people are in the Person table and for each person record I have indcated the person they report to. So I think this is a nested table structure.

Quetion 1 -

How would represent the organizational data like I illustrated above

also

I created a junction table that stores the orgID and the orgID for the parent, but I had to manually add the values to the table. Using this junction table with the Org table linked to the junction table then linked to a duplicate of the Org table I can query the relationship but it doesn't do a complete replication of the hierarchy

Question2

How I can update the ID fields in the junction table automatically while adding and deleting Orgs

Dave

 

[Adrian Jansen]

From a post by Allan Browne:

There's a basic example in this article:
Self Joins: tables that look themselves up (Pedigrees example)
at:
http://users.bigpond.net.au/abrowne1/ser-06.html

For more complex coverage, see Joe Celko's articles on SQL trees, e.g.:
http://www.mvps.org/access/modules/mdl0027.htm
http://www.intelligententerprise.com/001020/celko.shtml
http://www.devx.com/premier/mgznarch/vbpj/2001/07jul01/sqlpro0107/rein0107/
rein-1.asp


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Hi Tim,

Hmmm ... Basically I am looking to represent my organizaitonal hierarchy

in my Org table. Let me change the example.

Hopefully this will clarify my situation.

Tom reports to Dick who reports to Harry who reports to Sam.

-Sam - President
--Harry - Vice President
---Dick - Director
----Tom - Manager

Each of these people are in the Person table and for each person record I

have indcated the person they report to. So I think this is a nested table
structure.

Quetion 1 -

How would represent the organizational data like I illustrated above.

also

I created a junction table that stores the orgID and the orgID for the

parent, but I had to manually add the values to the table. Using this
junction table with the Org table linked to the junction table then linked
to a duplicate of the Org table I can query the relationship but it doesn't
do a complete replication of the hierarchy.

 

[Mike Sherrill]

Tom reports to Dick who reports to Harry who reports to Sam.

FWIW, many organizations don't have a "tree" hierarchy. For example,
Tom might report both to Dick and to John.

 

[djb]

Thanks for the links

Looks like I have my work cut out for me. I will take a step back and deal with some of the other "basic" stuff before looking into this

Thanks again for the help.

 

[Tim Ferguson]

Hopefully this will clarify my situation.

Tom reports to Dick who reports to Harry who reports to Sam.

<deafened and bemused by the goal-posts racing into the distance>

This is, of course, nothing to do with the situation you described in your
original post. The whole point of DB design is that there is rarely, if
ever, a "right" approach for a "type" of scenario. It always comes down to
the semantics of the particular business need.

Ask a particular question and you'll get a particular answer. Ask a
general, philosophic question and you'll get (lots of) general answers. Ask
a general question dressed up as a particular and you'll get rubbish.

All the best


Tim F