title: Database schema for efficient attendance management system
body: <p>I am developing an attendance system for school which will cater to both employees as well as students.</p>
  
  <p>The current db schema is </p>
  
  <p><code>attendance</code> table</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Then there are different tables for staff &amp; students, which store the actual attendance values. </p>
  
  <p>For staff, the attendance is stored in <code>attendance_staff</code>. The database schema is</p>
  
  <pre><code>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
  </code></pre>
  
  <p>For staff, i am storing both present as well as not present entries in the table.</p>
  
  <p>Now attendance for students has to be included with it.</p>
  
  <p>Since status of each day is already stored in <code>attendance</code> table, can we store not present values for each student in the student attendance table.</p>
  
  <p>Like, the student attendance table will store only entries for those days who are not present on a particular day.</p>
  
  <p>The schema for <code>attendance_student</code> will be</p>
  
  <pre><code>attendance_id - references attendance table
  student_id - references student table
  status - will be leave / absent etc other than present.
  </code></pre>
  
  <p>Will it be efficient to calculate the present days from attendance table using outer join??</p>
  
  <p>Thanks in advance.</p>