How to structure database for daily events?

manpreet Best Answer 2 years ago

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">days is trivial.

Challenges:

• Knowing how many days">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">days missed vs. days">days logged is trivial (used to calculate an overall percentage). The number of days">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.

manpreet 2 years ago