Business Rules table for vacation time and sick days time 1

[Robotron]

If anyone has had any experience with this, or has any ideas, I would greatly appreciate it.

I am attempting to build one or more tables to accomodate the business rules which define when an employee is eligible for vacation/sick time. I am unsure of how to go about this. Any help is useful.

Phil Edwards

 

[BNPMike]

First the easy bit. People are always eligible for sick time.

The vacation bit varies from organisation to organisation. You can do the 16 days per annum bit fairly easily. The problem comes when you have controls like can't have days off at the same time as Employee X, or Group of Employees Y, or only 2 people in Group Z can be off at the same time.

Tell us a bit more about what you want to achieve.

 

[Robotron]

My apologies, Mike. I mean Vacation/Holiday. To elaborate, I have this to consider:

1) In addition to the normal holidays (New Years Day, Memorial Day, etc) an employee may take 1 of 3 options:
1) An additional 7 consecutive days off.
2) An additional 7 days pay.
3) 7 incremental days off.

2) Vacation time is in addition to holiday time is determined based on the number of years an employee has worked.

Phil Edwards

 

[BNPMike]

I think I would be inclined to calculate the entitlement perhaps monthly and store it with the employee record. Something like (start_date,end_date,entitled_days). Then I would have another table with (employee,date,vactype) which has a record for each day the employee has any one of the three types of vacation. Vactype would distinguish the 3. Pay would be assigned to arbitrary days. This way you could do SUMs/COUNTs to compare entitlement to taken etc