Database schema for an effective attendance management system

Question

I am developing a school attendance system that will serve both staff and students.

Current db schema

attendance table

 id - primary key for this table daydate int(11) - stores timestamp of current day timing_in varchar(18) - Start time for institution timing_out - Closing time for institution status - Status for the day, can be working day - 1 or holiday - 2 

Then, for staff and students, there are different tables that store actual attendance values.

For staff, attendance is stored in attendance_staff . Database schema

 attendance_id - foreign key, references attendance table staff_id - id of staff member, references staff master table time_in - stores in timing of a staff member time_out - stores out timing of a staff member status - attendance status - can be one among the list, like present, absent, casual leave, half day, late mark, on duty, maternity leave, medical leave etc 

For employees, I save both current and non-displayed records in a table.

Student participation should now be included.

Since the status of each day is already stored in the attendance table, can we store the values ​​not presented for each student in the student attendance table.

For example, a student attendance table will only store entries for days that are not present on a particular day.

The scheme for attendance_student will be

 attendance_id - references attendance table student_id - references student table status - will be leave / absent etc other than present. 

Would it be useful to calculate the current days from the attendance table using an external join?

Thanks in advance.

Answer 1

You do not need an external connection to calculate student attendance. You can simply count entries in the attendance table (once, as this will be the same for all students), and then simply select from the student attendance table to get absent.

If you prefer to count traffic with an external connection, you could. It will probably be more than quite effective if you have the primary key index of the attendance table and the foreign key from the student attendance table to the attendance table.