CODEWITHSUNDEEP
mysqldatabasedatabase-designarchitectureschema
Ehs4n
Ehs4n

Reputation: 802

Database schema for efficient attendance management system

I am developing an attendance system for school which will cater to both employees as well as students.

The current db schema is

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 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 is

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 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 be

attendance_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.

Upvotes: 4

Views: 11783

Answers (1)

Joel Brown
Joel Brown

Reputation: 14398

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.

Upvotes: 2

Related Questions