Holiday Tracker Database layout
Application Type
Reactive

Hi everyone,

I am new to OutSystems and after evaluating a few low-code platforms for my workplace, I think I am going to stick with OutSystems. I have done a few tutorials and built a few very simple applications and I really love the platform.

I am now trying to build my very first real-life application, so I apologize in advance for the long post and for asking banal questions.

My goal is to build an application where an employee can ask (and manage) for personal time off.


Here are the requirements:

  • There are two holiday types that an employee builds up, let’s call them “Hol” and “APH”
  • Both types are accrued each month by a fixed amount
  • An employee can request time off by selecting a start and end date and deciding which of the two types to use – also a mix of both.
  • At the beginning of the month, employees receive their payslip (not in the application) where they can check four totals (for each of the holiday types, so eight in total), which are:
  • The total amount of holiday carried over from previous years
  • The total amount accrued for the current year
  • The total amount of holidays taken during the current year
  • The total amount of holidays left


I have built the database structure but before starting to build the screen, I would really appreciate opinions on the database logic, if it makes sense.



I have two major concerns/doubts while I was building the database structure:


1 – Totals

Do I store the totals (of the above point 4, e.g. holidays left) in a database table (Totals in my diagram) or do I calculate them with aggregates when building tables?


2– Month overlapping

In either case of how to get totals, how do I deal with employees making a request that is in between two months? I thought of having an entity where I store the totals of the request (HolidayRequest in the diagram) and another entity with multiple entries related to the request (one per each day of the holiday).

Example

An employee asks to take holiday from May 30 to June 1. It would be 3 days of holidays in total, 2 days in May and 1 in June. He/She can decide which of the two types  (Hol and APH) to use. Let’s say, he/she takes 1 day of APH on May 30, 1 of Hol on May 31, and 1 of Hol on June 1.

I would store the request in HolidayRequest with: FromDate (May 30), ToDate (June 1), TotalDaysTaken (3), TotalHolTaken (2) and TotalAPHTaken (1). Then in the HolidayRequestDetailed I would have 3 entries with the same HolidayRequestId with Date and Holiday type (it is always split by one day so I don’t need an amount).


I am so sorry for the long post but I would really love some feedback before proceeding with the many challenges of building the screens.


Thank you so much in advance!

Hi Andrea,

that's a good start.  

I guess your application has 2 purposes, making sure people don't take more holiday than they have accrued, and planning.

I didn't really think about planning, that is more complex than just the start and end date of one single employee, but for the first purpose, this feels to me a form of accounting, and I would probably treat it like each employee has a holiday account, with actions that add or remove holidays from it.

Another thing that I would do differently from your example, is that you start out with modelling the different types of holiday (HOL and APH) as a static entity, but you proceed to hardcode the totals instead of relating them to that static.  The choice for a static makes it very maintainable / extensible, but hardcoding those totals kills that.

To answer your specific questions, i like the idea of storing the totals better than calculating them whenever you need them, especially if different things (accrual / carrying over / taking a day) all have an effect, if it gets complex, you would have to gather all that information to recreate the total.

As for the month overlapping, I would look at a request as a collection of days to be taken, the request itself doesn't have to hold days or dates, it is just an aggregation of a bunch of days you want to request/approve in one go.  (in your model, all information about what days to be taken, and of what type, and between what dates is in the HolidayRequestDetailed, no need for the attributes FromDate, ToDate, TotalDaysTaken, TotalHol, TotalAph, that can all be derived from the details under it.

So here's my model, It's not as complete as yours, only those things to show what I mean :


So I have no RequestDetail, that is replaced by Movement, which doesn't only hold days taken, but also days accrued.  You could see this as a transaction on the holiday account of the employee.  The movement holds information about the action taken, and running totals (last 4) of the account after that action.  So if you want to know the situation for the payslip of march, you take the last movement before beginning of march for each Holiday type to find CarriedOver, Accrued, Taken and Left as they are at that point in time.

Here's an example of how the data would look :

You can see that not all movements have a request foreign key, only those that are taking up a free day.

I guess, if you want to allow your employees to make request for the whole year ahead of time, you would have to add all the expected accruals ahead of time if your employee can't go below 0 in the Left column.

Dorine

Hi @Dorine Boudry ,

Thank you so much for your reply and for taking the time to build a diagram and even an example of data.

I found everything you said super useful and I apologise but I don’t know if it’s possible in this forum to quote part of your message and include my response below (it would be very useful but I haven’t found a way to do it).


I agree with you as treating it as a holiday account and totally agree about not hardcoding the totals.

I really like your Movement table and I think that it can work, thanks!


I just have one question:

When I save the request, I guess that I need to calculate how many movements I need to save in the Movement table?

One for each holiday type but the worst case scenario would be when an employee is taking holidays in between two months and using both holiday types.

Let’s use the same example I did in my original post: 

An employee asks to take holiday from May 30 to June 1. It would be 3 days of holidays in total, 2 days in May and 1 in June. He/She can decide which of the two types  (HOL and APH) to use. Let’s say, he/she takes 1 day of APH on May 30, 1 of HOL on May 31, and 1 of HOL on June 1.

The same employee makes another request for 2 days (taken from the HOL type) from June 15. 


In the first request I would have 3 movement entries in the Movement table:

1 to store the APH and 2 for the HOL because it is in 2 different months. I think it might be better to do the logic of checking the month now before saving than do it every time I need to get the data divided by month.

While in the second request, it is one holiday type and within the same month, so it can be saved as one movement

What do you think? I tried to modified your table screenshot to visualise the example.

Thank you so much once again for your fantastic help!


Andrea

I would probably have a separate movement record for each single day, than it doesn't matter if the total request spans across a month end.

I think storing it as a span of several days is never very usefull, because when you let the employee request with just a from and to date, you will cross month ends, but you will also cross weekends and national holidays, so very often, you will have to brake it up anyway, so why not always do it on a per day base.  

I think a movement is attached to one date, and should represent the number of holiday days that is really changing in that day, and in case of taking up holiday, you can not take up more than 1 day (maybe you could have less, such as taking up half a day, if that is possible at your company)  In case of being credited extra days to take up, that can happen with more than one on a single date, so that can be more than 1.

Dorine

So as a consequence, if you would like to make a user interface that is super simple for your employee, you will have to make the system make a lot of decisions about what days are free already (weekend / national holiday) and make decisions about what days to make aph and what days hol, you'll need to have business rules in your system for that.  

If not, you'll have to let the end user enter each day separately, with the type they want on that day.  Some sort of calendar interface.  This would be a much easier job.

I just realize, in such an approach, a request doesn't even have to be about consecutive days, a request is just a bunch of holidays that logically belong together, with one single explanation, and with one single decision to approve or not.  So could be about getting every last friday of the month off, for example.

Or you could make it even easier on yourself (and a bit more work for the approving manager) by saying that a separate request needs to be entered and approved for each day involved.  I would probably not like this as an end user, but simplifies things for the developer.

Thanks Dorine!

Everything is clearer now from a database point of view and storing a movement per day seems the best solution.

The "fun" part will be building the form then. I won't be able to ask end users to create a separate request per each day. I was thinking of having the form to calculate the total number of days from a given range and then display it as a table/calculator when users see the total days they asked for and have two input fields where they can choose the number of days of each holiday type. It sounds challenging for a new user in OutSysystems like me but I will give it a try and maybe ask again for help in the forum.

Thank you so much again!

Hi Andrea,

that's a good start.  

I guess your application has 2 purposes, making sure people don't take more holiday than they have accrued, and planning.

I didn't really think about planning, that is more complex than just the start and end date of one single employee, but for the first purpose, this feels to me a form of accounting, and I would probably treat it like each employee has a holiday account, with actions that add or remove holidays from it.

Another thing that I would do differently from your example, is that you start out with modelling the different types of holiday (HOL and APH) as a static entity, but you proceed to hardcode the totals instead of relating them to that static.  The choice for a static makes it very maintainable / extensible, but hardcoding those totals kills that.

To answer your specific questions, i like the idea of storing the totals better than calculating them whenever you need them, especially if different things (accrual / carrying over / taking a day) all have an effect, if it gets complex, you would have to gather all that information to recreate the total.

As for the month overlapping, I would look at a request as a collection of days to be taken, the request itself doesn't have to hold days or dates, it is just an aggregation of a bunch of days you want to request/approve in one go.  (in your model, all information about what days to be taken, and of what type, and between what dates is in the HolidayRequestDetailed, no need for the attributes FromDate, ToDate, TotalDaysTaken, TotalHol, TotalAph, that can all be derived from the details under it.

So here's my model, It's not as complete as yours, only those things to show what I mean :


So I have no RequestDetail, that is replaced by Movement, which doesn't only hold days taken, but also days accrued.  You could see this as a transaction on the holiday account of the employee.  The movement holds information about the action taken, and running totals (last 4) of the account after that action.  So if you want to know the situation for the payslip of march, you take the last movement before beginning of march for each Holiday type to find CarriedOver, Accrued, Taken and Left as they are at that point in time.

Here's an example of how the data would look :

You can see that not all movements have a request foreign key, only those that are taking up a free day.

I guess, if you want to allow your employees to make request for the whole year ahead of time, you would have to add all the expected accruals ahead of time if your employee can't go below 0 in the Left column.

Dorine

Hi @Dorine Boudry ,

Thank you so much for your reply and for taking the time to build a diagram and even an example of data.

I found everything you said super useful and I apologise but I don’t know if it’s possible in this forum to quote part of your message and include my response below (it would be very useful but I haven’t found a way to do it).


I agree with you as treating it as a holiday account and totally agree about not hardcoding the totals.

I really like your Movement table and I think that it can work, thanks!


I just have one question:

When I save the request, I guess that I need to calculate how many movements I need to save in the Movement table?

One for each holiday type but the worst case scenario would be when an employee is taking holidays in between two months and using both holiday types.

Let’s use the same example I did in my original post: 

An employee asks to take holiday from May 30 to June 1. It would be 3 days of holidays in total, 2 days in May and 1 in June. He/She can decide which of the two types  (HOL and APH) to use. Let’s say, he/she takes 1 day of APH on May 30, 1 of HOL on May 31, and 1 of HOL on June 1.

The same employee makes another request for 2 days (taken from the HOL type) from June 15. 


In the first request I would have 3 movement entries in the Movement table:

1 to store the APH and 2 for the HOL because it is in 2 different months. I think it might be better to do the logic of checking the month now before saving than do it every time I need to get the data divided by month.

While in the second request, it is one holiday type and within the same month, so it can be saved as one movement

What do you think? I tried to modified your table screenshot to visualise the example.

Thank you so much once again for your fantastic help!


Andrea

I would probably have a separate movement record for each single day, than it doesn't matter if the total request spans across a month end.

I think storing it as a span of several days is never very usefull, because when you let the employee request with just a from and to date, you will cross month ends, but you will also cross weekends and national holidays, so very often, you will have to brake it up anyway, so why not always do it on a per day base.  

I think a movement is attached to one date, and should represent the number of holiday days that is really changing in that day, and in case of taking up holiday, you can not take up more than 1 day (maybe you could have less, such as taking up half a day, if that is possible at your company)  In case of being credited extra days to take up, that can happen with more than one on a single date, so that can be more than 1.

Dorine

So as a consequence, if you would like to make a user interface that is super simple for your employee, you will have to make the system make a lot of decisions about what days are free already (weekend / national holiday) and make decisions about what days to make aph and what days hol, you'll need to have business rules in your system for that.  

If not, you'll have to let the end user enter each day separately, with the type they want on that day.  Some sort of calendar interface.  This would be a much easier job.

I just realize, in such an approach, a request doesn't even have to be about consecutive days, a request is just a bunch of holidays that logically belong together, with one single explanation, and with one single decision to approve or not.  So could be about getting every last friday of the month off, for example.

Or you could make it even easier on yourself (and a bit more work for the approving manager) by saying that a separate request needs to be entered and approved for each day involved.  I would probably not like this as an end user, but simplifies things for the developer.

Thanks Dorine!

Everything is clearer now from a database point of view and storing a movement per day seems the best solution.

The "fun" part will be building the form then. I won't be able to ask end users to create a separate request per each day. I was thinking of having the form to calculate the total number of days from a given range and then display it as a table/calculator when users see the total days they asked for and have two input fields where they can choose the number of days of each holiday type. It sounds challenging for a new user in OutSysystems like me but I will give it a try and maybe ask again for help in the forum.

Thank you so much again!

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.