Leonidas: Timesheets

Timesheets, if you have to keep one, are one of the most frustrating things to keep up with. I understand some organizations do not keep time, some only keep enough time to know you were present for eight hours, then there are those that want to know what you accomplished in every 15-minute block of every day. That last one is pretty tough, but it opens the door to a ton of analysis and budget-keeping advantages.

In Leonidas, timesheets are fairly integral to the operation of everything else. Most employees only log in to Leonidas once a day (or week) to enter their time and then they leave. I set out to make sure it is the easiest component to use to lower any aggravation.

Data Retention

One thing to note right off the bat: I had to accommodate all the time entries from our previous time-keeping system, which came to life in late-2000. Querying on over twenty years of data is not exactly quick, but MariaDB supports table partitioning. Partitioning based on the time entry’s year, I could cut the query space down to a much more manageable chunk since 99% of all queries would be against “this” year or “last” year.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE `time_entry_hours` (
    `entry_id` bigint(20) unsigned NOT NULL,
    `date`     date NOT NULL DEFAULT '1000-01-01',
    `hours`    decimal(5,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (`entry_id`,`date`),
    KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PARTITION BY RANGE (year(`date`)) (
    PARTITION `pBlank` VALUES LESS THAN (0) ENGINE = InnoDB,
    PARTITION `p2000` VALUES LESS THAN (2001) ENGINE = InnoDB,
    -- more definitions ...
    PARTITION `p2040` VALUES LESS THAN (2041) ENGINE = InnoDB,
    PARTITION `pFuture` VALUES LESS THAN MAXVALUE ENGINE = InnoDB
)

With this CREATE statement, time entries are placed in their partition based on the year of their date and any query with a date in the WHERE clause will automatically only span the appropriate partitions.

ISO-8601

When you think of timesheets, you think of a weekly record of the tasks and time spent. Representing “a week” actually took a bit of time to figure out until I stumbled upon the ISO-8601 week date format, which allowed me to represent a full week with just a small number (YYYYWW). This yearweek number is readily parsed by DateTime, available in SQL, easy on the URL when scrubbing between weeks, and manipulates easily.

Looking back, I could (should?) make the time_entry_hours table above only store the day of the week (1-7) and the yearweek in time_entries would allow users to move their time entries between weeks. I don’t think I did this because I needed to query hours directly with date ranges instead of week ranges. Maybe it is an experiment I can carry out for academic purposes.

1
2
3
4
5
6
7
8
9

CREATE TABLE `time_entries` (
    `entry_id`     bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `task_id`      mediumint(8) unsigned NOT NULL,
    `user_id`      smallint(5) unsigned NOT NULL,
    `yearweek`     mediumint(6) unsigned NOT NULL,
    `description`  mediumtext DEFAULT NULL,
    PRIMARY KEY (`entry_id`),
    -- additional keys and relationship definitions
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

As the time_entries table approaches a million records and time_entry_hours approaches two-million, performance continues to stay consistent. This data integrates into budgeting components elsewhere in Leonidas, it helps the company determine employee workload, and even leads to calculating burn rates to determine if the company needs to hire new employees.