Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
The best book I ever bought regarding database design was Database Design for Mere Mortals by Michael Hernandez ISBN: 0-201-69471-9. See Answer Posted on Programmes SE –  Cape Cod Gunny Jul 21 '11 at 20:07

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Your answer was extremely helpful to me. Thank you! –  ChrisO Jul 22 '11 at 21:59

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?

share|improve this answer
    
Thanks for the reply. Performance, Quality and Attendance are all decimal numbers so I could include them in 1 table. I think the main hurdle I have at the moment is managing to store multiple people's stats for the same day as the primary keys have to be unique. I'll have a read up on the link you posted. –  ChrisO Jul 6 '11 at 23:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.