Department / supervisor (1 Viewer)

[hooks]

Im having trouble determining where the supervisorID should go. Part of me thinks that it should go in the JobCodeMaster Table the other part of me thinks it should go in the DepartmentMaster Table. Any suggestions?

One other thing. Should i have a separate Supervisor table. Again im stuck on whether the supervisor is its own entity or just another employee?

thanks for the help.

Hooks

 

[hooks]

bump bump

 

[Sergeant]

I think the supervisor is just another employee. When you build a query, you can always put another copy of the employees table on the grid and link the supvID to the empID in the 2nd copy. If in doubt, look up "SELF JOIN"...it is the answer to the employee/supervisor dilemma.

 

[hooks]

Ive changed my layout a little. Can anyone find any problems with this layout. My goal is to make this employee management program as robust as possible.

The supervisor/Director is what i am worried about.


Thanks
Hooks

 

[hooks]

bump bump

 

[Pat Hartman]

1. Supervisors and directors are not generally assigned to jobs. They are generally assigned to departments.
2. I wouldn't use tSupervisor and tDirector. I would use the tDepartmentMaster to identify supervisors and directors if I needed a list.
3. It doesn't make sense for location to be in both the department and job tables. Location probably belongs in the department table unless you have people who work for the same department but are in different locations. Then I would put location in the employee record.
4. I also don't understand why the jobcode table contains deptID. Can a person really work for one department but have a jobcode that is linked to another?

 

[hooks]

The JobCodeMaster table is really a position table.

2. I wouldn't use tSupervisor and tDirector. I would use the tDepartmentMaster to identify supervisors and directors if I needed a list.

Makes sense. Thank you

3. It doesn't make sense for location to be in both the department and job tables. Location probably belongs in the department table unless you have people who work for the same department but are in different locations. Then I would put location in the employee record.

Good catch on that. I will remove the DeptLocationID from the DepartmentMaster table.

4. I also don't understand why the jobcode table contains deptID. Can a person really work for one department but have a jobcode that is linked to another?

I was thinking that i would need to link the JobCodeMaster table to the departmentMaster table to find all JobCodes for a particular Department. How else could i do this?


Thanks for your help with this.
Hooks

 

[Pat Hartman]

You'll need to post your modified structure so I can see what is now related to what.

 

[hooks]

Here are my current relations

 

[Pat Hartman]

Looks ok except that you are not consistant in how you name the foreign key fields. I also personally wouldn't prefix the fields in each table. When you create queries, Jet automatically prefixes each field with the "tableName.fieldname" so your own prefix is redundant and when you are working in VBA with a recordset, your field references are prefixed with the recordset name - "rsn!fieldname".