Attendance and invoice database design
I am designing a database for managing a small private school (~15 teachers, 250 students). The student and teacher contacts, lessons and attendance I think I got it covered (see the image).
The problem I am facing now is the following:
The students pay in advance for 10 or 30 lessons. I then need to keep count of every lesson the student followed. Once they have no more lessons left, have a notification so that the student can pay for other 30 (or 10) lessons and then track again.
I sure can count how many lessons the student followed and compare with how many payments the student has done (eg 57 lessons, 2 payments => 3 lessons left) but is that a good strategy?
I also need to have a monthly track of how many students a teacher had.