Org Chart Design

[cynteeuh]

I'm creating a db in Access 2007 that has a rptg structure hierarchy, i.e. 1,
2, 3, 4. Basically, level 2 reports to level 1and level 3 reports to level 2
and so on, but where I'm stuck is the final level. Do I list the mgr/head
from the previous level to the next level? This confuses me as it appears the
mgr/head rpts to self, but I can't figure out a cleaner way to do this.

I've created one table of employee contact info and there's a field (combo
box) that links to the lowest level (4) of depts. Is this the best way to
setup?

Thank you in advance for your expert advice.

Sincerely,
Cynthia

 

[Piet Linden]

I'm creating a db in Access 2007 that has a rptg structure hierarchy, i.e.. 1,
2, 3, 4. Basically, level 2 reports to level 1and level 3 reports to level 2
and so on, but where I'm stuck is the final level. Do I list the mgr/head
from the previous level to the next level? This confuses me as it appearsthe
mgr/head rpts to self, but I can't figure out a cleaner way to do this.

I've created one table of employee contact info and there's a field (combo
box) that links to the lowest level (4) of depts. Is this the best way to
setup?

Thank you in advance for your expert advice.

Sincerely,
Cynthia

IS there any reason that the person at the highest level has to report
to someone? Wouldn't the person that doesn't report to anyone be the
topmost person in the chart, by definition? So his "reportsTo" value
would be NULL, right?

 

[Fred]

Your questions relate to table structure, and table structure is the
important thing for what you are doing, yet you have told us zero about your
table structure.

 

[cynteeuh]

Correct, except in this case the two at the top level rpt to one another. My
confusion comes when I get to the bottom level and when it comes to assigning
a dept in the 'contacts' table. I currently have the last level as an option
(combo box) to select what dept the contacts belong to. When I do this, I
don't have an option or dept for the manager. That's why I'm wondering if I
should also include the manager from the previous level at the bottom level
or if there's a better way to arrange.

Basically, I have the below setup:
tblLevel1
L1ID
L1Name
L1Head (combo box pulled fr a qry on the 'contact' tbl based on their
'title')

tblLevel2
L2ID
L2Name
L2Head (same as above)
L1ID (combo box based on previous level's ID)

additional levels

tblContacts
Usual contact info (FName, LName, Title, etc.)
Dept (combo box based on the last level's ID)

The setup works out until I get to those heads that are not in the bottom
level. As I'm typing this I'm wondering if my 'dept' combo box should include
all levels as a possible choice?

Thanks for your help!

 

[Fred]

Hello Cynteeuh,

In my opinion, you should temporarily forget about combo boxes......they
seem to be getting in the way of focusing on and fully describing your table
structure as step 1. . Like what consititutes a record in each table, what
the links are, and whant the links mean. I'm not sure what constitutes a
record in each of those level tables and so it's hard to tell what you have
or what you are trying to do.

If you are primarily databasing people, and the one-per-person type
information for each (including who their supervisor is) (if not, forget this
whole paragraph) you can just put all of the people into one table, put a
supervisorsID FK field into their record, and link the table to itself
(SupervisorsID FK field to the PersonsID PK field. Then your whole
database reduces to one main table, plus maybe a few ancillary tables. If
you don't want to do that (but if the premise at the beginning of this
paragraph is still correct) you could divide the people into levels, with the
same linking fields that I described, except that the SupervisorsID FK field
links to the PersonID PK field in the table that is one level up.

Once you have a solid table structure, the combo box answers will come easy.

 

[cynteeuh]

Hi Fred,

You're correct about what I'm primarily doing and I've considered what
you've recommended. However, I need to have the various depts as we create a
phone list grouped by depts and we list the function of them. We also work
with depts on specific projects. Additionally, I'd like a structure that
represents what depts rpt to who.

For my db a record in the contacts table represents the contact info for an
employee then it's related to what dept they belong to. Make sense?

e.g.
tblL1 = each record constitutes the highest level dept, it's function, & who
heads it.
tblL2 = the same as above, except who does this head rpt to on the level
above - L1
tblL3 = the same as above, except who does this head rpt to on L2

then there's the 'tblcontacts' that includes the basic contact info i.e.
fname, lname, title, email, dept (based on the above, except pulled from the
last level), etc.

Should I include all levels in the dept option? That way when I come across
a head in a dept, it's there? Though, that seems like it may be confusing as
many of the names are similar.

Or would the correct method be to add the manager in the level below? This
option seems weird as it will appear the manager rpts to self.

Overall my db is about projects my dept works on with other depts. For each
project we create a working phone list grouped by depts. I also create a
phone list of my immediate dept along with the top two dept levels.

It's currently working, but I keep getting additional contacts from other
depts and decided I should develop the db with the correct org structure.
That way whenever I add a new contact, the dept is already there in its
proper place. At least in theory.

Pls forgive me if I'm not making sense. This is why I'm confused. I know
just enough to get me in trouble.

Help!

Sincerely,
Cynteeuh

PS I'm going to think further through your second suggestion although I
currently don't really care who the contact's supervisor is. I'm more
interested in who heads the dept and what depts roll under it.

 

[Fred]

On DB design problems, describing it can be very difficult. Or, to put it
another way, once you have struggled through describing it, you are often
90% or 100% done with coming up with your solution.

I still only have "tip of the iceberg" knowledge on the important sufff of
the situation that you are databasing and the output objectives. First a
few thoughts which might help sort it out.

Even more fundamental than and prior to table structure design is a clear
definition of what it is you are databasing, including the "entities". In
your case, the entities are probably departments, people, and relationships.

Next you have to decide/define whether your reporting relationships are
between departments or people. My first guess is that you might be on the
"wrong" track by thinking departments.....for most companies, the "people to
people" reporting situation is cleanly defined, whereas departments have a
lot of complexities and exceptions. Of course departments can also be
entities, and they can be (or not be) groups of people. A manager can be in
charge of a dpartment, and the manager can also be the person that all of the
individuals in the department report to. Either way, your decision in this
are will fundamentally determine and are needed to determine a table
structure.

BTW, when I was saying to think along the lines of "What constitutes a
record" in a table, those can be very short but important answers like
"People", "Departments" "Level 1 Departments" etc.