Database to track employee performance

Question

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.

Answer 1

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.

Answer 2

I suggest you read up about database normalization. Here is a good starting point: Third normal form

Definitely do not create one table per employee as management of that database structure will soon become too cumbersome and your application will need to be modified every time a new employee is hired.

I do not know what your business rules are, but assuming that Performance, Quality and Attendance all store different types of data (i.e., different column types) then they should be in separate tables. I could not guess at what primary key you should use without knowing what types of data are stored in those tables.

Can you provide more information?