How to structure database for daily events?

Question

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:

1. Calculating the number of logs and missed logs can be done quickly, and
2. 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

Table 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  |

Table 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

Advantages:

• Data is efficiently stored.
• Tallying user logs and non-counting days is trivial.

Challenges:

• Knowing how many days were missed is not obvious.

Option 2: One Table (What I've tried)

Table 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

Advantages:

• 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.

Challenges:

• 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 log == 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.

Answer 1

Here's a few cases to spoil both designs: Teachers get sick. Unions go on strike. Servers go down. Snow days happen.

Class happens when it happens regardless of what the syllabus says. So rather than pretend we know what the future holds, simply record events as they happen.

Events:

• Instructor declares today is a class day
• Student declares their attendance today

absences = class days - attendances

Advantage: design does not require use of confusing misleading non-descriptive nulls.

Answer 2

It appears that what you need is more along the lines of this:

Calendar {
    DateId int not null;
    Date datetime not null;
}

DoLogDay {
    DateId int not null (foreign key to DateId column in Calendar);
    LogDay bit not null;
}

UserLog {
    DateId int not null (foreign key to DateId column in Calendar);
    UserId int not null;
}

The reason for introducing a DateId is so that the actual day/month/year is only specified once in the schema. You need to look up the DateId to log user attendance but I suspect you can fetch the mapping of DateId to dates when your app starts and cache them.

Also, the use of DateIds means that when you need to do calculations you have a good column for indexes because you have removed any potential weirdness that can occur with dates in your DB's code (I'm not saying that there would be weird behaviour, but I'd expect weird issues to be more likely with dates than with ints).

Note that the indexes will make insertions slower (can't just update the table, need to update the index) but this increase in insertion time may be too little to matter. You do not have to add the indexes when building the initial database. You can performance test with and without the index to be sure. Consider the indexing bit a "bonus" of the DateIds. The DateIds get rid of redundant dates as their "primary" purpose.