Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JacobPressures
Posting Yak Master
112 Posts |
 Posted - 2005-10-13 : 11:15:46
|
| my current DB Schema can be found athttp://www.geocities.com/jacobpressures/index.htmlIf 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- InactiveEMPLOYEETESTS- EmployeeTestID- TestID- EmpID- EmpTestStartDate- EmpTestFinishDateThese 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! |
|
|
|
|
|
Database Design Question