Tables and Relationships for Employees / Supervisors

[wvmbark]

I'm hoping someone can help me determine the best way to set up my tables and relationships for an Employee database (contact info, training, work schedules, etc.)

I'm currently stuck on the set-up and proper relationship properties for the following two tables...

tblEmployees
EmpID -PK
LastName
FirstName
Dept (Maintenance Control, Technical Support..)
EmpType (Analsyst, Controller, Supervisor...)
SupID -FK of EmpID via self-join)
Address
...other

tblSupCodes
SupCode -PK (C001, C002...)
Desc (W/E Nights A, W/E Nights B...)
SupID
MgrID
Mon (08:00-17:30, 17:00-01:00...)
Tues
Wed
..other days

-tblSupCodes is a table that applies to only "Controllers" from tblEmployees.EmpType
-Codes basically identify work shifts and groups.
-Each Controller is assigned a SupCode which identifies their supervisor.
-Supervisors can have 1 or several codes (they may supervise several work groups on the same shift).


Here's where I'm stuck...
(1) tblEmployees has a field for SupId (which comes from a self-join on EmpID), but for Controllers I need a field that shows their SupCode. I don't want to use SupCode for Controllers and EmpID for everybody else, nor do I want to have to enter data into two different fields that "should" point to the same supervisor. Should I have both SupID and SupCode fields in tblEmployees? If so, do I leave all SupCode's null if not a controller? If so, do I leave all SupID's null if they are a controller?

(2) For each Controller, I need to also capture their manager MgrID (which can also come from tblEmployees.EmpID). Do I enter MgrID in tblSupCode or tblEmployees? If I put it in tblEmployees do I now create another self-join using tblEmployees_2? Or??

This seems like it should be quite simple but I'm just going around in circles with all the fields and instances that basically identify just a single employee.

(3)Once I get the table/relationship structure squared away, I may also need some help with the best way to create forms for assigning Controllers and Superviors their SupCodes. For example: If a SupCode gets a new SupID (supervisors swap shifts, new sup, etc.) I don't want to have to go to tblEmployees and update all the Controller's SupID's. Controller's keep their SupCode but different supervisors can fill the roll of that SupCode, so each time there's a supervisor change I just want to change it at tblSupCode. But then everything's working differently for only one group of employees...

Any input or assistance will be greatly appreciated!

Mike

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmm

In most intances, the employee-boss relationhip is easily expressed in a single table. Each employee is assigned a number, They Employee record includes a SupervisorID field which is simply the EmployeeID of their supervisior. Reasonably simple queries show any desired / necessary relationship. Additional fields may be included in the single table or in seperate tables with an associated key field. If you have VISIO, it includes a template for the diagramatic organization structure, and can output a table based on the structure.




MichaelRed

 

[wvmbark]

My tblEmployees SupID does use the EmpID of the Supervisor and data input works great using the self-join method I've seen in several posts here. I assume that if I went a step further and added Cascade Updates/Deletes, and if a supervisor's position is replaced by another employee, I could change the SupID at one record and the rest of the employees with the same SupID would update accordingly. ??

But now I have this situation where one Supervisor is over several different groups of one employee type (EmpType = "Controllers"). So not every employee with that SupID will update when a particular SupCode is assigned to a different Supervisor.

So, are you suggesting to add another field (SupCode) to tblEmployees and utilize that field to capture Controllers' supervisors, and use SupID for the other employee types?

I guess I'm worried that it may be difficult to sort, filter, and group by Supervisors when the data is in two different fields.

Mike

 

[MichaelRed]

Obvopiuls I'm not understanding something. Given the many to one relationships between the "supervisor" and Employees, it makes no difference what "group" either belongs to. The relationships is the more-or-less classic note-leaf (tree). The supervisor can have many immeditae, secondary, teriteriary and ancilliary employees. Their other designations / functions / ancilliary attributes have nothing to do with the node-leaf )tree) structure. You seem to be expressing some relationship outside of this arrangement, but I'm (obviously) not understanding what this is or how it may violate the relationship between the node(s) and Leaf(s).




MichaelRed

 

[wvmbark]

Please excuse the mess... I've yet to figure out how to post columns.

tblEmployees
EmpID EmpType SupID SupCode
1 Analyst 12
2 Controller 13 COO1
3 Controller 13 COO2
4 Controller 14 COO3
5 Controller 15 COO4
6 Controller 15 COO5
7 Controller 16 COO6
8 Controller 17 COO7
9 Controller 18 COO8
10 Controller 19 COO9
11 Controller 19 COO10
12 Supervisor 21
13 Supervisor 20
14 Supervisor 20
15 Supervisor 20
16 Supervisor 20
17 Supervisor 21
18 Supervisor 21
19 Supervisor 21
20 Manager 22
21 Manager 22
22 Division Mgr 22


tblSupCodes
SupCode Desc SupID
COO1 W/D Days A 13
COO2 W/D Days B 13
COO3 W/D Days C 14
COO4 W/D Nights A 15
COO5 W/D Nights B 15
COO6 W/D Nights C 16
COO7 W/E Days A 17
COO8 W/E Days B 18
COO9 W/E Nights A 19
COO10 W/E Nights B 19

If the SupID (supervisor) for SupCode C001 is changed from 13 to 19 in tblSupCodes, then all employees in tblEmployees with SupCode C001 needs to have their SupID update to 19 - not every employee with SupID 13. So, I'm trying to figure out if this is the correct table structure and if so, how do I make maintain the integrity between SupID and SupCode in tblEmployees? Or, is there a better approach that I'm overlooking?

Mike

 

[MichaelRed]

but this does not represent the single table with a normal (e.g. 1 supervisor to many employees) relationship.
You have:
EmpID[tab]EmpType[tab]SupID[tab]SupCode
2[tab][tab][tab]Controller[tab]13[tab][tab][tab]COO1
3[tab][tab][tab]Controller[tab]13[tab][tab][tab]COO2


Which shows employees # 2 & 3 having the same "Boss" (Emp # 13) but different 'supervisors.

This harkens back to an outdated form of management ofter refered to as "Matrix Management".

The single table is sufficient to have the node:leaf relationship completly identified. It is not ammenable to the matrix management.

The first issue is to express the relationships correctly in SOME form. If you are 'concinced' that the above does that then ...




MichaelRed

 

[wvmbark]

No!! Every employee does have only 1 supervisor...

SupCode "COO1" identifies a work shift and a particular work group on that shift. Everybody on that shift and in that group is assigned a SupCode "C001". tblSupCodes identifies who the supervisor for employees assigned SupCode "C001" is. That same supervisor may supervise 2, 3, or more SupCodes, which is to say he supervises employees in several different work groups. The work shifts/groups remain constant, but the supervisors over those groups change from time to time.

So, if employees with SupCode C001 get a different supervisor, I don't want to go to every individual C001's employee record to update his SupID to the supervisor assigned C001. I want to just change the SupID in tblSupCodes

If I only had Controllers in tblEmployees, I could use SupCode instead of SupID and could obtain the Supervisor via a link to tblSupCode - where the SupID for those employees is. But I have analysts and admins and managers, etc. So in tblEmployees, do I maintain SupID for every employee including controllers. If so, how do I ensure that both the SupCode (would be null for everyone except controllers) points to the same supervisor (EmpID) that's in the field for SupID??

Am I making any sense????????

Mike

 

[wvmbark]

MichaelRed,

but this does not represent the single table with a normal (e.g. 1 supervisor to many employees) relationship.  
You have:
EmpID    EmpType    SupID    SupCode
2            Controller    13            COO1
3            Controller    13            COO2


Which shows employees # 2 & 3 having the same "Boss" (Emp # 13) but different 'supervisors.

Using your example... If the SupID for C002 is changed (in tblSupCodes) to 14, then the employee where EmpID =2's SupID should reflect that is supervisor is now 14.

Controllers are assigned to work groups. Work groups are assigned 1 supervior. 1 Supervisor can supervise several work groups. Supervisors move between work groups and can change several times per year. So if two groups share the same supervisor today, tomorrow maybe only one group will report to that same supervisor. So the employee's SupID in tblEmployees should always reflect the SupID assigned to his group or "SupCode".

Mike

 

[MichaelRed]

I think i understand. It is at least a partial matrix management. Wheather you refer to the "controller" or additional "supervisor" is not material. You are still attempting to manage multiple "bosses" of the same employee. My immediate thought would be to have two seperate tables. One having the Employee:Supervisor relationship the other having the Controller:Employee relationhip. Treat each of them in the same nmanner, i.e. as hte single table defining the entire management structure. If a "Controller" is reawssigned, address that thtrought the one table (only). If the employee or "Supervisor" changes, deal with thar aspect through the other table. In some few situations, adjustments may need to be made in both, but even there it should only be a few records in the set of tables. Exampes of this woould seem to limited to instnaces of employees or controllers whose status re the shift assignment changes.

I know that all organizations basically operate on some form of matrix management -regardless of the official org chart. On the otherhand, as a manager, I would be somewhat uncomfortable in your outlined organization. The "controller" position appears to be one of authourity without corresponding responsibility (or the reverse). They have the authourity to 'supervise" the employees in terms of the immediate tasks of their shift work, but not the 'authourity" to actually participate in their management (e.g. personnel reviews, promotions, or other long term job situation. Conversley, the 'supervisor' has the long term responsibility with out the immediate / first hand knowledge of the performance. This part doesn't help you in any way, but having been in the matrix management mode I have enough 'horror stories' re its malfunction and malfesance to write an encyclopedia. Your mention of the 'wandering controller' scenario strikes very colse to more than one of these incidents.




MichaelRed

 

[wvmbark]

MichaelRed,

I really do appreciate you hanging in there with me, but we're not on the same page yet.... Here's the breakdown of my organization (we call it a department, but it's actually several small departments within on Division).

Admins, Analysts, and Controllers - bottom-tier employees, each reports to one Supervisor.

Supervisors - lowest tier of management employee, may have zero employees reporting to them, or may supervise one to many employees; reports to Managers

Managers - 3rd Tier (2nd managment tier), may have zero employees reporting to them, or may supervise (manage) one to many Supervisors; in my organization/dept./division all Managers report to one Division Manager.

One group of employees, "Controllers", each have one Supervisor or "boss". Each Controller is part of work group. That work group has one Supervisor that each Controller in that group reports to. The work group has a "SupCode" that identifies the current Supervisor for that work group. One Supervisor may supervise 1 or more work groups.

So, just because Controller Bob's Supervisor changes from Joe to Tom, it doesn't mean that everybody under Joe get's their Supervisor changed to Tom - only those in Bob's work group now report to Tom.

When I change the SupID for SupCode "C001" from Joe to Tom, I need tblEmployees SupID to correctly identify Tom as the Supervisor for every Controller with SupCode "C001".

Also, for other employees - let's say "Analysts"... if their Supervisor changes, I don't want to go to every employee individually to update their SupID, I just want to change it one place for the Analysts under that Supervisor and change the SupID, and all the Analysts' SupID now reflects their current supervisor.

Any clearer yet???

 

[MichaelRed]

No. I still think the two table situation makes it possible without much ado about the situation. Teminology aside, it still looks like you want / need to be able to address eiter the single employee:supervisor relatinoship or the group of employees:supervisor relationship. I do not see that your current structure supports these seperate operations with any useful degree of seperation. I do see at least the outline of the manipulations which supports the seperate operations and requires minimal coordination for the odd situations.




MichaelRed

 

[wvmbark]

MichaelRed,

I'm not sure what your suggesting. Let's pretend that I'm in 4th grade. Could you explain a little further, possilby with an example of a table structure that will work for my application. Also, please address the following questions.

(1) Should I have SupID only, or SupID and SupCode, in tblEmployees? Or neither, and use tblEmployees and tblSupervisors AND a join table tblEmployeesSupervisors?

(2) Since Controllers (which are Union employees) have fields that aren't relevant for other employees (Seniority Date, SupCode, etc.), should their employee info reside in the same table as all employees - with nulls in those fields for every other employee? Or, should I have 2 Employee tables - 1 for Controllers, 1 for everyone else?

(3) How do I update Employee's SupID's? (For Controllers, the SupCode has to be included in the criteria? to ensure not every employee with that Supervisor updates just because one employee's does.)

Thank you!!!

Mike

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmm ... 4th grade? ... mayhap beyond my ability.

Two tables. Inherently seperate.

Inclusion of ancilliary material is more of a business descision than a database structure issue. Purists would certainly seperate the items you mention to a seperate recordset. Acutal usage might depend more on the overall situation. Do you have thousands of employees or just 25? With thousands, the issue is somewhat easier, and tends towards seperation.

The two table thought is that each table represents an "axis" of authourity. Their intersection occurs only where a change of status is necessary in BOTH, as might happen when / if a unionized employee (previously on shift work) ascends to a management position. Presumably, in that instance, both tables as affected. Each table includes ONLY the relationship pertinent to that axis.

Since both include the EmpId fields, the concept is to change the AFFECTED table. If the shift shpervisor changes then that table is updated. If an employee switches shifts, the shift table is updated. If an employee is transfered to a different supervisor then the employee table is changed. Each of these 'events' is independent. Any coincidental changes need to addressed in both (e.g. the promotion example noted above). Otherwise the twain shall never meet. One table expresses the employee:supervisor relationship. The other table expresses the shift work assignment of employee:shift with what ever 'straw boss' is accociated.

Your first table Employee:supervisor is the pattern for both tables. Each shoould (ideally) only include the single one:many relationship of employee to their "boss" for the situation. Ancilliary information is, up to some point, reasonable. However each table should be independent of the other and NOT include information which is not pertinent to the 'axis' of authourity which it represents. Thus, the second table which is primarily dealing with the unionized personnel MIGHT be a canidate to include some minimal informqation re their union status, such as a union membership identifier (particularly if there is more than one union involved as these may impose different constraints on the assignment).

I could easily NOT be fully appreciating the total picture of the situation, and am offering a perspective based on the information in this thread, primarily the two example tables and -to a much lesser degree- your clarifications.

My experience with matrix management is the basis of the overall suggestion of the independent tables. In my experience, hte term "matrix" accruately describes the situation where many / most employees report to more that one superviosor. Usually, one "boss" is the administrative leg while the other is the task manager. The two hshare the responsibility of the employee. Their responsibilities are well shown in a standard two dimensional matrix, with the caveat that the intersect cells of the matrix are defined as the employee. This is more easily dealt with (programatically) with two independent rstructures / recordsets.




MichaelRed

 

[wvmbark]

I'm not sure I fully understand you, yet. Are you suggesting

xxxxxxxxxx My interpretation #1 xxxxxxxxxxx

2 tables

(1) tblEmployees(EmpID, Name, EmpType, SupID, Address, other)

and

(2) tblSupCodes (SupCode, Description, SupID)

where there is zero interaction/connectivity/links between the two, AND

Where, in the event that a supervisor (which supervises several groups of employees) for one work group of employees is replaced by a different supervisor, I would perform 2 actions

(1) In tblSupCodes, change the SupID for the record belonging to that work group, AND

(2) In tblEmployees, go to each employee record for the employees in that work group and update the SupID there also.

If YES, Would I need a SupCode (work group ID) field in tblEmployees to be able to identify those employees (If YES, then every other employee type would have null in that field)?

xxxxxxxx OR, my Interpretation #2 xxxxxxxxxxxxx


tblEmployees (EmpID, Name, EmpType, Address, other)

tblSupervisors(EmpID, Name, EmpType, Address, other)

tblEmployeeSupervisors (EmpID, SupID)

And totally independent of the above 3 tables...

tblShift (SupCode, Description, SupID)

tblShiftEmployees (SupCode, EmpID, SupID)

Or something like that?

xxxxxxxxxxxxxxxxxxxx

Does your statement:

In my experience, hte term "matrix" accruately describes the situation where many / most employees report to more that one superviosor.  Usually, one "boss"  is the administrative leg while the other is the task manager.  The two hshare the responsibility of the employee.  Their responsibilities are well shown in a standard two dimensional matrix, with the caveat that the intersect cells of the matrix are defined as the employee.  This is more easily dealt with (programatically) with two independent rstructures / recordsets.

imply that this is your understanding of my situation?

If YES, let me clarify by stating once again that it is not. Each employee has only ONE supervisor.

SupCode Description Supervisor
C001 Shift 1 Group A Bob
C002 Shift 1 Group B Bob
C003 Shift 2 Group A Tom
C004 Shift 2 Group B Sue

Bob is the supervisor for 10 employees on "Shift 1 Group A" and 10 other employees on "Shift 1 Group B". If Sue gets assigned to "Shift 1 Group A", she is now the Supervisor for those 10 employees, and 10 on "1/B" still report to Bob.

Please forgive me, but I am having difficulty understanding if your suggestions are based on OneEmployee:TwoSupervisors or OneEmployee:OneSupervisor, and if it's on the former, when in fact it's the latter, does this change anything?

xxxxxxxxxxxxxxxx

Ultimately I need to determine
(1) How many tables and what in each?
(2) Where do I establish Employee:SupCode -> in tblEmployees in addition to SupID, or in a new table tblShiftsAndEmployeesOnThatShift?
(3) For each time a supervisor position is filled with a new supervisor, do I need to go to each employee record and record the change to SupID there AND go to each employee(that's assigned a SupCode) in a second table and change the SupID there as well?


Mike

 

[MichaelRed]

I'm sorry. I am obviously unable to explain this clearly. I hope someone else is more capable than I.




MichaelRed