October 13, 2005 at 10:35 am
my current DB Schema can be found at
http://www.geocities.com/jacobpressures/index.html
If it is hard to read you might be able to download it and using the magnifying glassing in pictures. I did the best i could with the screen shot. SQL Server doesn't provide very good tools for screen shots and printing.
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/evaluations/surveys" with "categories/subheadings" that have "questions."
You can see the actual table names on the jpg.
Tests, Categories, Questions. I may change the names because of scope changes.
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.
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.
I thought about adding a password field to the Employees Table and then where ever that password was used in the EmployeeTests table i would know who did the evaluation on that person. But passwords change. It is best to put another employeeID there or supervisorID. This will give me an EmployeeTest (or EmployeeEvaluation) with the person being evaluated and a person (or supervisor) doing the evaluation.
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!
October 14, 2005 at 7:52 am
Don't know too much about your business rule. But the following works. You just need to use different FK name for the two references. And based on your information, your design should solve your problem.
- SupervisorID (Can I do this? Refers back to Employees.EmpID 2 columns with the same reference?)
October 17, 2005 at 8:54 am
Thanks very much for your help. I've decided to take it a little further and create a Supervisors Table
SUPERVISORS
- SupervisorID
- EmpID
- Password
- DateBegan - Date employee became supervisor
- DateEnded - Date employee left or loss position
- Inactive - Used for logical delete
This implementation i think will allow me to get rid of Nulls in the Password column, allow better referential integrity when a supervisor leaves the position but is still employed because when the bit column is not checked then the EmployeeTest.SupervisorID column would reference an EmpID that would no longer be valid. If the employee regains a supervisory position, giving them a new Supervisors.SupervisorID would allow me to see all the changes in position if a question arises.
Thanks very much! Any additional suggestions or cautions are welcomed. Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply