Database schema for efficient attendance management system
You don't need an outer join to calculate attendance for students. You could simply count the records in your attendance table (one time, since it would be the same for all students) and then just select from your student attendance table to get absences.
If you'd prefer to count attendance with an outer join you could. It is likely to be more than efficient enough if you have an index on your attendance table primary key and on the foreign key from student attendance table to your attendance table.
Ehs4n
Updated on June 04, 2022Comments
-
Ehs4n almost 2 years
I am developing an attendance system for school which will cater to both employees as well as students.
The current db schema is
attendance
tableid - 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 there are different tables for staff & students, which store the actual attendance values.
For staff, the attendance is stored in
attendance_staff
. The database schema isattendance_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 staff, i am storing both present as well as not present entries in the table.
Now attendance for students has to be included with it.
Since status of each day is already stored in
attendance
table, can we store not present values for each student in the student attendance table.Like, the student attendance table will store only entries for those days who are not present on a particular day.
The schema for
attendance_student
will beattendance_id - references attendance table student_id - references student table status - will be leave / absent etc other than present.
Will it be efficient to calculate the present days from attendance table using outer join??
Thanks in advance.