my current DB Schema can be found at
http://www.geocities.com/jacobpressures/index.html

I'm focusing on Employees Table and EmployeeTests Tables

Here is my issue. With this db, i can do tests and surveys. I'm trying to modify it slightly so that i can do employee evaluations also. I want a good design and not something that is just functional.

Currently, there are tests with categories that have questions.
I may change the names because of scope changes. Test may become Evaluations and EmployeeTests may become something like EmployeeEvaluations.

Each user in the vb app signs in and begins test.

However, to do an evaluation, two employees are involved. The supervisor does an evaluation on anohter employee. the supervisor enters the name of the employee and then does an evaluation on him/her.

As you can see there is only one employee table. I've considered teh recursive relationship by having a column where an employee is managed by another employee but i don't see how that applies to this situation. Besides our lower skilled staff often switch departments and thus supervisors. It will be alot of work changing who supervises whom.

To solve this problem I've thought about placing a SupervisorID into EmployeeTests so that I would know who did the evaluation on that employee.

I wasn't sure if that was the best design because it has two relationships between the same tables and I've never done taht before. I guess i can enforce integrity through a stored procedure, by making sure that the "SupervisorID" is an approved supervisor. I was thinking i can add a boolean (bit) column on the Employees table that would be true if he is a supervisor and false if not.

Is there a different or better way? I want this DB to last and not have complications in the future. This is a learning experience for me and an opportunity actually get into the programming field.

Here are the attributes in the two tables:

EMPLOYEES
- EmpID
- FirstName
- LastName
- BadgeNumber
- DeptID
- CenterID
- Inactive

EMPLOYEETESTS
- EmployeeTestID
- TestID
- EmpID
- EmpTestStartDate
- EmpTestFinishDate

These are my proposed additional attributes:

EMPLOYEES
- EmpID
- FirstName
- LastName
- BadgeNumber
- DeptID
- CenterID
- Inactive
- Supervisor bit column
- SupervisorPassword (creating column allowing Nulls. I try to avoid)

EMPLOYEETESTS
- EmployeeTestID
- TestID
- EmpID
- EmpTestStartDate
- EmpTestFinishDate
- SupervisorID (Can I do this? Refers back to Employees.EmpID 2 columns with the same reference?)

This also creates another null column. Most tests, surveys or evaluations will not need a supervisorID.

Any suggestions?


Thanks!

In your description you make no reference to the position held.
Clearly employees have positions and employees have supervisors
who also have positions.

If you have a table

position
-------
pid
description
reports_to

won't this resolve your difficulty

Bill