I’m storing data which logs whether or not a user has logged their attendance for a given day. Some days are unimportant (holiday, weekend), so those are also stored.
The two requirements are that:
- Calculating the number of logs and missed logs can be done quickly, and
- The structure is scallable for whenever new users are added.
Right now it seems like I’m faced with two options for how the data should be stored, each with their own advantages/disadvantages:
Option 1: Two Tables
calendar – Tracks days to be not counted
date | log | -----------+-----| 2019-01-10 | DNL | // "Do Not Log" - holiday etc. 2019-01-12 | NB | // "Non-business day" 2019-01-13 | NB |
logs – Tracks successful attendance logs
user_id | date | --------+------------| 1 | 2019-01-08 | 1 | 2019-01-09 | 2 | 2019-01-09 | // It's implied that user #2 missed their log on Jan. 8
- Data is efficiently stored.
- Tallying user logs and non-counting days is trivial.
- Knowing how many days were missed is not obvious.
Option 2: One Table (What I’ve tried)
calendar – Tracks logs and days to be counted and not counted
date | user_id | log | 2018-01-09 | 1 | 1 | // Counted, logged 2019-01-10 | 1 | DNL | // Not counted 2019-01-11 | 1 | NB | // Not counted 2019-01-09 | 2 | NULL | // Counted, missed log
- A tally of days missed vs. days logged is trivial (used to calculate an overall percentage). The number of days in the calendar is explicit.
- Adding new entries to the calendar is tricky, in the event that:
- The calendar grows in length.
- New users are added.
- Table has gaps (wherever
NULL), making traversal slower than Option 1.
My question is this: Is there a way to either use Option 1 and somehow encode the number of missed logs, or is there some other way of storing the data that meets both requirements? I’ve tried using Option 2, although scaling has become quite a challenge. Thanks in advance for any advice.