SQL Server Performance

DB Design for Time Tracking.

Discussion in 'General Developer Questions' started by Ankith, Sep 27, 2006.

  1. Ankith New Member

    Hi Everyone:

    I am trying to come up with a good db design for time tracking an employee's
    time on one of the client projects that I am working on. The background is
    as follows:

    1). An Employee can work on administrative tasks and also some special
    projects.
    2). An Employee can also work on customer issues.
    3). If an employee is on a vacation he will not work neither on any projects
    or customer issues.

    The tables I have designed so far look like this:

    Table 1:

    Admin
    ---------
    AdminID
    AdminDesc

    The Admin Desc can include: Vacation,General tasks,Sick,Training etc.

    Example data

    AdminID Desc
    ------ -----
    1 Vacation
    2 General
    3 Sick
    4 Training
    5 Time Tracking

    Table 2:
    Projects
    ---------
    ProjectID
    ProjectDesc

    The Project Desc can include: ProjectA, ProjectB, ProjectC etc.

    Example data

    ProjectID ProjectDesc
    ------ -----------
    1 ProjectA
    2 ProjectB
    3 ProjectC
    4 ProjectD
    5 ProjectE

    Table 3:

    Customers
    ---------
    CustomerID
    Name
    ProblemType

    The Customer Name can include: Customer1, Customer2, Customer3 etc.
    The ProblemTypecan include: DR, Performance, Querytuning etc.

    Example data

    CustomerID Name ProblemType
    ------ -----
    1 Customer1 Disaster Recovery
    2 Customer1 Performance
    3 Customer2 Backup issue
    4 Customer3 Query Tuning

    Finally My EmployeeTable for Time tracking would be:

    Table 4:

    Employee
    ---------
    EmployeeID
    EmployeeName
    AdminID
    ProjectID
    CustomerID
    Timespentinhrs

    Example Data:

    EmployeeID EmployeeName AdminID ProjectID CustomerID Timespentinhrs
    10 Rob 4 NULL NULL 2
    10 Rob NULL 2 NULL 10
    10 Rob NULL NULL 1 25
    10 Rob NULL NULL 1 10
    10 Rob NULL NULL 2 3

    The total time spent by an employee
    in the above case would be :50

    I need to know if this can be designed in a better way? Also How do I track
    the problemtype for an employee for a customer? Also is it a bad idea to
    include the problemtype for the customer in the Employee Table?

    please give your thoughts and suggestions.

    Thanks

    Ankith.








Share This Page