Payroll System: Database structure for flexible storage of weekend off scheme

Question

Objective: The Leaves Management and Payroll system requires the official weekend hodiday scheme stored in the database to be referred to while generating the monthly salary for each employee in the organisation. Generating flexible Database Schema for storing the weekend scheme is the objective.

Problem: The database schema should be flexible enough to allow the changes in the definition of a weekend. The weekend in our scenario may be defined as:

1. Sunday
2. Sunday + 1st or 2nd or 3rd or 4th Saturday
3. Sunday + (1st and 2nd) or (1st and 3rd) or (1st and 4th) or (2nd and 3rd) or (2nd and 4th) or
(3rd and 4th) Saturday
4. Sunday + all Saturdays

Answer 1

maybe store the scheme like that 11111 - means Sunday and all Sats 10000 - means only Sunday 11010 - means Sunday and 1st and 3rd Sats

so first number is Sunday, second - 1st Saturday, etc

You could the use Bitwise OR to check for some values like

checking for 1st saturday

SELECT * FROM table_name WHERE holiday_scheme | 01000 > 0

Hope that helps.

Answer 2

Bitwise logic can be clever, but I believe that scheme does not scale well as this expression does probable translate in a table scan. If that 'table_name' is big, it can punish you a lot on joins.

It can be better if you create an work_day_schedule and associate that schedule to the 'table_name' (ex.: employee). Now, a table scan on the work_day_schedule would not hurt much and you can do what bitwise magic you want.