2

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.

  • number of missed days in #1 is just days in year - logged - not tracked – Ewan Jan 12 at 4:45
  • In option 2 you could add an automated script to insert a record for each user at midnight each night. When a user logs later that day, you update their attendance. In the script you can add the logic to skip unimportant days. New users will be handled by your script also. Note that when deleting users, you might not want to remove their attendence history from this table, in that case a soft delete for the user is better and your script can test for that condition. – Rik D Jan 12 at 18:50
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.

0

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.