August 29, 2008 at 8:45 am
I have been working with databases designed by others for years, and for the first time and designing my own transactional database.
We are a distributed organization with employees across the eastern and central US. The application I am designing and programming is to be a time tracking web application that will allow our employees to log into a website, enter their regular hours, vacation hours, personal hours, etc.
The payroll admin will then take the data from the website and enter it (either manually or through import/export functionality) into the 3rd party payroll website who then cuts the checks for everyone.
Note that this is not necessarily a "timeclock" application where it marks when the employee started and ended his day. It's more along the lines that every two weeks an employee will log in and record his hours for the past two weeks.
Needed functionality:
- Allow employees to enter hours (including benefit hours such as vacation, sick time, etc.)
- Allow supervisors to review and approve data entered by employees
- Allow payroll admin to assign benefits to qualifying employees
- Allow payroll admin to issue benefit hours to employees
- Maintain available benefit balances for each employee
- Maintain transaction history of benefits issued and benefits consumed
- Reports, etc.
The simple version of my database design is this:
The Employees table is "the top of the RI chain"
Benefits child tables:
EmployeeBenefits child table showing what employees get what benefits
EmployeeBenefitsTransactions child table showing benefit hours issued to employees
(these 3 tables are where the payroll admin does his thing, assigning benefits and granting benefit hours to be consumed)
EmployeeHours child tables:
EmployeePayPeriods child table, each row listing an employee ID and a two week payperiod ID
EmployeePayPeriodDays child table, each row listing the employee ID, payperiod ID, and day within the payperiod
EmployeePayPeriodDaysHours child table, each row showing the hours consumed on the parent day, including hour type (regular, benefit, etc.) Note that there could be multiple rows for the same day (4 regular hours, 4 vacation hours, etc.)
(these tables are where employees will do their thing, entering various types of hours for days worked)
My question is this: How should the available benefit balances be tracked? This is a very important issue, because available benefit balance will be used to validate employee data entry, control how many additional benefit hours are granted at the employee anniversary date (employees can have 240 vacation hours max, 16 personal hours max, etc.), print on reports, etc.
There are multiple ways that I can see to do it:
1. Create views based on SQL queries with joins to multiple tables and multiple aggregated fields
Pros: Cleaner data normalization
Cons: Maintainability of queries/views and, to a lesser extent, performance. Also, not readily apparent to my successor where all information is located in order to determine benefit balance.
2. Write application logic so that every time an employee consumes a benefit in the EmployeePayPeriodDayHours table, a matching entry gets put into the EmployeeBenefitTransactions table to remove that consumption from his balance. Available balance could then be shown with a simple one table query with one aggregated field.
Pros: All information to determine balance is in one table and is more easily queried
Cons: Dirtier data normalization, more complex application code
3. Add a balance field to the EmployeeBenefits table that must be updated in app code every time benefit hours are granted or consumed for this employee.
Pros: Best performance and easy coding for queries, very easy for my successor to find balance information
Cons: Would result in complex application code to maintain the balance field correctly, if not done properly it could go out of sync with data transactions. I admin other apps where this happense, and it is a nightmare. MUST have some ironclad mechanism for keeping balance field in sync with data transactions.
What would you do? Am I missing something? On number 3, could I implement some kind of system with triggers that would keep the balance field in sync with the 2 transaction tables? Or would that become it's own maintainability and performance nightmare?
August 29, 2008 at 9:46 am
What's the volume of activity? I'd lean towards storing a balance for a couple reasons
- space is cheap
- Unless this is a really busy app, the calculations shouldn't impact performance.
- you can write regular checks (and you should, like daily) that make sure the calculations are correct, and notify someone if they are not.
August 29, 2008 at 11:13 am
Around 100 users hitting it every two weeks at about the same time, plus reporting off and on. Probably the bigger issue performance wise will be activity in other databases (accounting, inventory management, etc.) that can be significant (batch processing, etc).
August 29, 2008 at 12:32 pm
I would do the benefits hours like an inventory system. Positive entries and negative entries in the same table, with a type and a time stamp. When an employee earns more days off, they get added. When they use the time, the hours get subtracted.
Ballance is just sum(), grouped by type and employee ID, possibly by year if that pertains.
If policies are in place for things like, "you earn X hours per month up to the first year, then you earn Y hours per month", those can be easily added to a scheduled job that adds days at the end of each month based on the policies in place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply