TimeSheet - Database design

[Squid2008]

For many years I had my employees enter their time and job cost information into a spreadsheet. Each employee had their own password protected workbook. Each worksheet was a month and there was also a summary worksheet. There were two sections to the spreadsheet, time and job cost. For each day they would enter their time classification(s) and the department(s) they worked in. The total time must equal to total departments time. There is a column to calcate overtime if the total of time classications exceeded 7.5.

The following is the columns of the worksheet:
Date, Regular Time, SickLeave, ApprovedLeave, Vacation, LeaveWithoutPay, LeaveNoCharge, Total Time (calculated), Overtime (calculated). Department1, Department2, Department3, Total Departments (calculated), Proof (Total Time - Total Departments).

The summary worksheet compiled the information entered by quarter. Also calculated their remaining time per classification. The calculation is time rolled over from previous year + time available current year - time used current year. Rolled over time and time available are static values entered the first day of each year. time used current year is calculated.

I want convert this into a database. So i can analyze and control the information better. But need some help designing the database. Below is what I have so far:

tbl_Employee
EmployeeID (pk)
EmployeeName

tbl_Department
DepartmentID (pk)
DepartmentName

tbl_TimeClass
TimeClassID (pk)
TimeClass - regular time, sickleave, vacation, etc.

tbl_BeginningTimeCurrentYear
TransactionID (autonumber)(pk)
Year - alway the first day of the year (sk)
EmployeeID (fk)
TimeClassID (fk)
TimeClassHours

tbl_RolledOverTimePreviousYear
TransactionID (autonumber)(pk)
Year - alway the first day of the year (sk)
EmployeeID (fk)
TimeClassID (fk)
TimeClassHours

tbl_TimeSheet
TransactionID (autonumber)(pk)
Date (sk)
EmployeeID (fk)
TimeClassID (fk)
TimeClassHours
DepartmentID (fk)
DepartmentHours

Scenarios:
1. An employee can have one or more time classifcations per day
2. An employee can work in one or more departments per day

Any help or suggestions will be greatly appreciated.
Thanks in advance
Mike

[MMcCarthy]

Have a look at this structure.

tbl_Employee
EmployeeID (pk)
EmployeeName

tbl_Department
DepartmentID (pk)
DepartmentName

tbl_TimeClass
TimeClassID (pk)
TimeClass - regular time, sickleave, vacation, etc.

tbl_Job
JobID (pk)
JobName

tbl_Costing
CostingID (pk)
EmployeeID (fk reference to tbl_ Employee)
DepartmentID (fk reference to tbl_Department)
CostingDate
TimeClassID (fk reference to tbl_TimeClass)
CostingHours
JobID (fk reference to tbl_Job)

OK the first thing is that Department Hours is a calculated field and shouldn't be stored.

Secondly, you don't use separate tables for each year.

Most of what you were trying to do with tables you should be doing with queries.

All you need from your tables is a basic structure to store the information. You can then use queries to calculate the rest.