I am a relative novice with database design. Currently our company is utilizing a spreadsheet to track employee time records, but we would like to move to a database so that we can use the information to generate more usable reports. We are relatively small with about 25-30 employees. My problem is that I am not entirely sure the best way to break down my information to be able to maintain the information I need.
I need to be able to track the following information:
- Employee ID (last 4 of SSN)
- Employee Name
- Component
- Full Time or Part Time (part time employees need to be able to track the number of hours worked to make sure they don't exceed 1000 hours in the calendar year)
- Hire Date (Full time employees accrue 40 hours of vacation after 1 year as well as 24 hours of personal leave which reset every year on their anniversary date. In addition, full time employees earn 4 hours of sick leave every 28 day schedule)
- Hours worked for the week (Sun-Saturday) (4 time cards per 28 day period)
- Sick leave used
- Vacation used
- Compensatory time used
- Holiday time used
- Personal leave used
- Sick leave accrued
- Vacation accrued
- Compensatory time accrued (calculated based on 28 day period - straight time for 160-171 hours per 28 day period - time and half for hours over 171 in the 28 day period)
- Personal leave accrued
- Running balance for each type of time
The goal is to be able to enter the time worked and any time taken off each week when time cards are submitted. We want to be able to enter an employee id at any given point and tell how much of each type of time they have in their "banks".
Right now we have spread sheet with rows for each of the time categories and columns for each week. Every 4 weeks there is a formula that computes the accrual for the 28 day period. Each employee has their own spreadsheet tab with the same dates, formulas, etc. The spreadsheet gets rather long and complex and doesn't allow for a very user friendly summary of vacation, sick leave, etc. I have looked online, but I am not finding any really good examples of how this can be accomplished. Are we better off staying with our spreadsheet or is there a simple way to organize this data for a database?
Any help would be greatly appreciated.