I am currently designing a system to support our employee staffing process. I collect weekly preferences from employees, and feed that into a system for staffing. Availabilities can be defined as absolute dates (e.g. Employee John is available on 01/01/2017 from 14:00 to 16:00). Or by recurrence, where one can define a weekly recurring availability (e.g. Employee John is available Weekly on Sundays from 14:00 to 16:00).
Weekly Recurring Availabilities: start time/end time - most of the queries would be by local time, but we might also need the UTC time in the queries. we keep the timezone as well to resolve DST issues. employee_id
Absolute Availability: start time/end time - absolute datetime employee_id is_available - can represent a time an employee isn’t available at all
Employee: id zone_id is_active
The following queries should be supported: - Get availabilities by employee - Get availabilities by zone and date range - Should filter out inactive employees, should return also recurring availabilities, and should also convert the recurring dates to absolute datetime records, for example: Given employee John has a single weekly recurring availability, every Sunday from 14:00 to 16:00, an API consumer might request all employees that are available between the upcoming Sun-Sat, the output should be: employee_id, start_datetime, end_datetime
We need to build an ETL on the data - transfer it to a data warehouse db. We are required to transfer recurring availabilities as their real time availability - for instance if I have a recurring availability on Sunday - I should transfer future availabilities matching to this - row for each week with the actual date for a range of month or two in the future. Is there any approach to handle that? Should we do it be saved in the data warehouse with the specific dates? or should the reports over it do that?
We currently thought on saving the availabilities in mongo in one collection for availabilities (recurring and absolute), and copy fields of is_employee_active and zone_id to each availability. We also thought of assigning start_time/end_time an absolute date for recurring availabilities with proximate date matching the selected day.
I would like to get a feedback on what's the best approach, thanks.