Proper structure for keeping track of hours

[MrHans]

Hi all,

I'm building a database to keep track of holiday hours and absent hours of my personnel.
Before building all forms and reports I want to set up the data structure properly.

I currently have 1 table that stores everything.
EmployeeID, HourTypeID, NrOfHours.

The problem is that with holidays for example I have positive and negative values. The new year starts with a new budget and then holidays need to be deducted, so lots of negative values.

Would it be better to create a dedicated table for each type of hour? Like tblAbsent, tblHolidays, tblOvertime, tblHolidayBalance and then calculate the balance with queries?

How would this be properly structured?

Thanks in advance!

 

[The_Doc_Man]

Would it be better to create a dedicated table for each type of hour?

Almost certainly not better. This is a variant on a type of "inventory" where your transactions either add or subtract items based on the kind of transaction. You would have a table of transaction types with a code, a name, and a +1 or -1 as a multiplier for hours represented for that class of transaction.

Then you would have a table of transactions where you would have the employee ID, date, transaction code, and number of hours for that code. (This allows you to note if someone took half a day to go for a medical appointment.) Then to figure someone's current hourly budget, you just do a query that sums the transaction amounts multiplied by the +1 or -1 in the transaction type table.

If you are looking at hours budgeted then you would have a yearly "+" transaction to add to hours available. If you then do something special for hours that can't be worked, like a holiday that fell on a workday, you have a "-" transaction. You have to decide how you count absences (vacation, sick leave, building shutdown 'cause of terrorists, etc.) and assign transaction types (and + or - signs) for each type. Hours worked would also be counted against the budget as a "-".

At the end of any period (day, week, month, or year), you have a ready-made "Where did the budget go" analysis waiting to be harvested because you can do a GROUP BY on the transaction type codes for each employee.

 

[MrHans]

Thank you Doc Man, that's a very interesting concept. I didn't think of it that way.

My current HourType table is comparible to your TransactionType table, so I just need to add the multiplier value there.

I will work further on this concept, thank you for sharing your insights!

 

[The_Doc_Man]

Happy to help!