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

How many employees do you have? None of them know anything about databases? What kind of business are you in?

Sounds like RFH to me.....

Small company of 17 employees. i have some employees that i had to recenly teach copy & paste.

What is an RFH?

RFH to me stands for Request for Help; except I bet the H in blindmans usage means "Homework"

We get a lot of people coming on with homework requirements. It is curious to find a business owner with so many employess getting stuck into the nitty gritty of database design. Ordinarily one would expect your time to be worth more than that of a professional in this area (especially given the learning curve you are on).

Anyhoo - I would probably not have a current and historical table- just have the one. I also don't understand what DepartmentHours in Timesheet indicates.

Even worse yet, I am the controller that's in the middle of year end hell. Now it makes sense why i had a heck of time finding examples on the web. I am using Departments as a generic term for job cost centers.

The flaw i find in my design is with the timesheet table doesnt allow for an employee to work 7.5hrs and then spread that across several different departments. They would need to enter the time classifcation for each department. For example: I can work a regular 7.5hr day, but i need to allocate 3hrs to Dept1, 2 hrs to Dept2 and 2.5 to Dept3.

Well, there is no getting around that. How could you write any business logic that would know how they distributed their hours? That must be entered by hand.

I would expect, however, you to have a single hours field in your timesheet. These hours are then effectively categorised by TimeClass and assigned to the Department cost centre. As mentioned by blindman, if you assign your day's "regular time" to three different cost centres then you have to enter three rows - no way round it (or at least if you db is properly designed at least). You can usually alleviate repetative data entry with your UI e.g. the user enters their identifier one on entry not on every row they enter.

Also - what is an "sk"?

That is correct, the employees allocate their time worked manually.

So, the database is normalized properly?

sk = secondary key

Thanks!
Boo Steelers, go Pens!

Ah - the proper term is Alternate Key. And I am afraid you have your alternate keys all wrong

You have introduced surrogate keys into your database (all the autonumbers certainly). The Alternate keys are what as known as Natural Keys, in so far as they are compossed of information in the real world. They can (and indeed often have to be) a combination of two or more columns.

To see how your alternate keys are problematic consider tbl_BeginningTimeCurrentYear. Based on your Alternate Key being Year and that Year is "always the first day of the year" - how many rows can you have per year?

For each employee and year they will have 3 rows (approved leave, sick leave and vacation)

Not with your current alternate key - you can only have one row. You have pretty well defined your composite alternate key - year, employee, timeclassid. If you maintain your currentyear and previous year you don't even need the year column as part of the PK in the first table since it is current! However, I still think you should have these as a single table with the above ak.

In fact, I would make that your PK and dump the autonumber but that is just my preference.

Rudy posted this sometime ago and I think it is my fave online db design article:
http://www.tonymarston.net/php-mysql...se-design.html
See how you do.