I've been out of IT for a long time now (20 years or so) but decided to try to brush up again by setting up my own staff absence tracking (sickness and leave etc) application.
I want to be able to see all absences for the current week and the next four weeks in a calendar grid.
I also want to show each absence 'event' (an 'event' will have a start and end date) in a list, one per 'event'.
I also want to have the ability to add notes to any of the absence 'events' or individual dates.
Also want to be able to total up days/hours taken of each 'absence type'
I'm thinking of implementing the database on the lines of 'Order Header' and 'Order Details'. I haven't included the Staff or Absence Type tables aI think they are self-explanatory.
I'm pretty sure this isn't the best way though - as it seems a bit like a 'programming solution' to a data problem but I'm not database expert - but here are my original thoughts. BTW, application is being developed in php/mysql database.
ABSENCE HEADER TABLE - used to show each 'absence event'
StaffID |EventID | From | To | Days | Type
1 | 1 | 2015-05-01 | 2015-05-06 | 6 | S
1 | 2 | 2015-07-14 | 2015-07-14 | 1 | H
1 | 3 | 2015-08-05 | 2015-08-05 | 1 | H
ABSENCE DETAIL TABLE - used to show the details in the Calendar grid