Results 1 to 2 of 2
  1. #1
    Join Date
    May 2005
    Posts
    12

    Normalization/Design Issue

    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!

  2. #2
    Join Date
    Sep 2005
    Posts
    22
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •