Ms-access – Database to track employee performance

database-designms access

This is going to be very basic as I'm a complete newbie when it comes to database design.

I've been tasked with setting up a way to track employee data over a number of months and potentially years. I need to keep track of their Performance, Quality and Attendance on a daily basis but I'm unsure on how to setup my tables so that I don't run into troubles down the line.

At the moment I made a very basic database in Access 2010. I have 2 tables, one holding Employee details (FirstName, LastName, Email, Manager) and one holding Manager details (FirstName, LastName, Email). I've successfully created a relationship between the manager field in the Employee table and the Manager table but I'm at a loss on how to implement the tracking of the actual stats.

How should I go about creating table(s) from now? Should I make a table each for Performance, Quality and Attendance with the primary key as the date? Or should there be a table for each employee? How should I relate the data to each employee and each day so that it doesn't become complicated?

Final goal is to use vb.net to create an application for generating reports if it matters. I don't have a problem with that step but I want to make sure I have a solid foundation first.

Any help appreciated, thanks.

Best Answer

Following the question and your comment - I'm not sure that I remember correctly, but I think that MS Access doesn't allow for a primary key with multiple columns, so you'd better add an auto-increment (autonumber in Access) integer column as PK, and add the other needed columns: EmployeeID, Date, Performance, Quality, Attendance. I'm sure that the Employee table could benefit from having a EmployeeID column (simply just use an int that's autonumber or use the SSN of that person..or whatever id you think you have and it's unique). This EmployeeID column would then be used as a reference in other tables (as the one with gathered values for P, Q and A).

Also, I wouldn't make a separate Manager table, but add a column called ManagerID to the Employee table and make it reference the EmployeeID (eg: for EmployeeID = 5, the ManagerId = 1, and based on the IDs you will get their names).

Later edit: found out I'm a dumb Access user, MS Access allows multi column PKs :-). Anyway, it's still much more simple to manage users and reference them by an ID, and not by a compound PK.

In your current situation I'd make 2 tables:

Employee - EmployeID, FirstName, LastName, Email, ManagerId; and a second with
History: ID, EmployeeID, Date, Performance, Quality, Attendance.

Should be enough for what data you want to gather now.

Related Solutions

How to design relationships for variant data

This might force a bit of change for your tables, but we do something like this at work:

Ensure each table has a int/guid primary key of some sort:

Organization Table

ID | Name
1  | MyOrganization

Then create a type-table to house your different LeadSource types:

LeadSourceTypeKey | Description
1                 | Organization
2                 | Company Office

Then on your ProductSoldList Table add 2 columns LeadSourceID and LeadSourceType

Then you can easily query the ProductSoldList TABLE and know which LeadSource to join back to:

SELECT *
FROM   [ProductSoldList] PL
       INNER JOIN [Organization] O
       ON PL.LeadSourceID = O.ID AND
          PL.LeadSourceType = 1
       INNER JOIN [Company Office] CO
       ON PL.LeadSourceID = CO.ID AND
          PL.LeadSourceType = 2

Mysql – Database Design – Employee Clocking In and Out System

One thing you might want to consider changing is making clocking.clock_out_date nullable. Until the employee clocks out there isn't a reasonable value to use here, so NULL is the best choice.

You can tell the difference between someone who is still working (theoretically) and someone who forgot to clock out by checking their previous clocking record when they clock in. If the previous record has a NULL clock_out_date then they obviously forgot to clock out.

You could also create a report that checks for employees who haven't clocked out for more than (some configurable number of hours) since they last clocked in. You should be able to get a reasonable number for the longest expected shift. It may be once in a while that you report an employee to management for not clocking out when they are actually still working, but that should be rare and the manager will know it - or find it out quickly enough, so the few false positives you might generate aren't serious.

One other thing you might want to think about is not keeping the hourly rate right on your staff table. It might be better to keep this in a child table which includes date ranges since you want to be able to let a wage go up or down over time and it would be good to (a) have a history and (b) be able to enter future-dated changes to relieve time pressure for data maintenance.

Best Answer

Related Solutions

How to design relationships for variant data

Mysql – Database Design – Employee Clocking In and Out System

Related Question