SQL attendance database design

Question

I am currently designing a database in mariaDb that will be used in a Meteor app (woth sequelize orm) for tracking the attendance of students in a school.

I'm not sure is the most effective way as there are few exceptions on my case:

1. teachers can move and reorganise their schedule as they please, and also because the student pay for each lesson (and certain type of absence), I can't use a "exclusion way" (eg only record absence, so no record = present)
2. the most important query needed is attendance per student, and I need to have it every time I open my app for every student.
3. second most important is a monthly attendance per teacher. (This one is needed on demand)
4. (not db related) I need to track the students presence by groups of 10 (every lessons they have to pay again)

The estimated starting size is 20 teachers, 250 students, 500 attendance/week, (every student has two lessons) 37 weeks,( max size double students and lessons).

Is running 250 queries (find) on a 20000row table time consuming?

Is on student table having a lesson_counter field that is updated every time an attendance is recorded a good idea?

Many thanks!

UPDATE:

there is a possible optimization to be made? This should represent a base for a possible email and invoice system both towards students and teachers

Solution

There are many possible improvements to your design. Let me start by answering your specific questions:

Is running 250 queries (find) on a 20000row table time consuming?

No. On modern hardware, querying 20.000 rows is going to be fast. If you have a decent indexing strategy, the queries should return in 10s of milliseconds.

Is on student table having a lesson_counter field that is updated every time an attendance is recorded a good idea?

No, it's a bad idea - on the assumption that you want a report for each student showing when they attended or missed a lesson, you have to store that data anyway. Keeping a counter is duplicating that information.

I suggest a design like the following.

An "attendance" and "absence" are logically separate things; you can model them in a single table with a flag. I've modeled them separately because I see them as different things in the business domain, with different attributes (absence has a reason code), and potentially different behaviour (for instance, an absence might have a workflow for sending an email). I prefer to have things that are logically separate in separate tables.

Student
-------
student_id
name
...

Lesson
------
lesson_id
subject
teacher_id (if only one teacher can teach a lesson)
....

enrollment
---------
lesson_id
student_id
start_datetime (or you might have the concept of "term")
end_datetime

lesson_session
-------
lesson_session_id
lesson_id
start_datetime
end_datetime
location
teacher_id (in case more than one teacher can teach a lesson)


attendance
--------
lesson_session_id
student_id

absence
------------
lesson_session_id
student_id
reason (or might be a foreign key to reasons table)

Comments

[Rick James]

250 queries -- per day? - No problem. Per second? - May be a problem.

[Patch]

Thank you. My design is about the same, only attendance and absence tables are together (and reason indeed with foreign key). Is it better to divide them? The absences are evaluated and some of them counted as present (eg student didn't wake up in time...). There are many possible improvements to your design. may I have a hint?

[Neville Kuyt]

I've updated the answer to explain why I think "absence" and "attendance" are separate tables. It's not black & white...the schema I proposed were the possible improvements - your question suggests something much more basic. If you include your actual schema in the question, you'll get more accurate answers...

[Patch]

Thank you for your answer and update! I've added my initial design (stripped)