Database set up for Absence tracker - what's the optimal way?

Databases
1

I need to set up a staff absence tracking application. There is a requirement to show each day of absence for the current week and the next four weeks in a calendar grid. Also, to show each day of absence for user-defined dates.

There is also a requirement to show each absence ‘event’ (an ‘event’ will have a start and end date) in a list, one per ‘event’ e.g. an absence of 5 days would show as a single ‘absence event’.

There is also a requirement to have the ability to add notes to any of the ‘absence events’ or individual dates.

I’m thinking of implementing the database on the lines of ‘Order Header’ and ‘Order Details’.

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 no database expert - here are my original thoughts, I’m sure it’s hacky and very sub-optimal:

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
StaffID |EventID | Seq | Date
--------+--------+-----+------------
   1    |  1     | 1   | 2015-05-01
   1    |  1     | 2   | 2015-05-02
   1    |  1     | 3   | 2015-05-01
   1    |  1     | 4   | 2015-05-02
   1    |  1     | 5   | 2015-05-01
   1    |  1     | 6   | 2015-05-02 
   1    |  2     | 1   | 2015-07-14
   1    |  3     | 1   | 2015-08-05   
   etc. 
   
ABSENCE NOTES TABLE  
StaffID |EventID | Seq | Notes
--------+--------+-----+------------
   1    |  1     | 1   | Some descriptive text in here
2

I would make one single table, like so

StaffID |EventID | From       | To         | Type  | Note
--------+--------+------------+------------+-------+-------------------
1       | 1      | 2015-05-01 | 2015-05-06 | S     | Measles
1       | 2      | 2015-07-14 | 2015-07-14 | H     | Dog ate homework
1       | 3      | 2015-08-05 | 2015-08-05 | H     | Bridge was open
  1. There is no need to store the number of days in the database, you can calculate that from the From and To days.
  2. There is no need to store all dates separately. You have the From and To date, use those to calculate all days in between (probably in your programming language, not in SQL)
  3. There is no need for a separate table for notes, just put them in the main table.
3

How would that address the requirement to be able to enter notes against any date

4

It just says you need to be able to add notes to a date, or an event. So if you see a single date as an event too I still think my solution should suffice. It doesn’t state that there needs to be a different reason for different days within an event (and it also doesn’t make a whole lot of sense to allow that).

5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.