Categories

See More
Popular Forum

MBA (4887) B.Tech (1769) Engineering (1486) Class 12 (1030) Study Abroad (1004) Computer Science and Engineering (988) Business Management Studies (865) BBA (846) Diploma (746) CAT (651) B.Com (648) B.Sc (643) JEE Mains (618) Mechanical Engineering (574) Exam (525) India (462) Career (452) All Time Q&A (439) Mass Communication (427) BCA (417) Science (384) Computers & IT (Non-Engg) (383) Medicine & Health Sciences (381) Hotel Management (373) Civil Engineering (353) MCA (349) Tuteehub Top Questions (348) Distance (340) Colleges in India (334)
See More

How to structure database for daily events?

Course Queries Syllabus Queries

Max. 2000 characters
Replies

usr_profile.png

User

( 4 months 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 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.

usr_profile.png

User

( 4 months ago )

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.

what's your interest


forum_ban8_5d8c5fd7cf6f7.gif