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.