I was having a doubt regarding the design of a database table to track students' attendance. Currently, my table students have atleast 4000 students.

Creating a database for attendance to track their attendance will have almost 4000*30days*12months => ~ 1,400,000 rows (neglecting vacations/ sundays).

The attendance table will have:

id (INT)
student_id (INT) 
course_id (INT)
present/absent (TINYINT, as I'll store 1/0)

I'm using PHP/MySql. With a table becoming so large, is there any other way?

  • 2
    1.4 Million rows per year are not that many. But I find it hard to understand, if this is an attendance table, where is the date or the classroom or subject these students are attending at? And what is the id supposed to store? – ypercubeᵀᴹ Oct 23 '13 at 5:49
  • You may be able to get away without storing an entry in your attendance table if the student is actually present. Also, what about half-days, or deciphering between excused/unexcused absences? – Aaron W. Oct 23 '13 at 11:04
  • @ypercube: Yes, i'll store all those things as well. My biggest concern was that of the large number of rows. So, I framed the question in a hurry. – xan Oct 23 '13 at 11:10
  • @AaronW.: I'll be storing those as well. My biggest concern was that of the large number of rows. So, I framed the question in a hurry. – xan Oct 23 '13 at 11:10
  • I can only assume that ID is a foreign key into the table containing the properties of the lecture. Am I correct? – Twinkles Oct 23 '13 at 13:37

To expand more on @Twinkles alternative, I'm concerned about why you're storing present and absent students in your attendance data. Is it because you want to be able to run a report that shows all kids and whether they were absent on a given day? There's a better way to do it.

Most systems I've seen store either the that the student is present (Positive attendance) or the student is absent (Negative attendance, although rarely referred to by that name). The decision on which to use came down to the structure and reporting requirements of the school (and whoever they report to).

If you're school is concerned when the student is absent for reasons like having to track Truant students, you should use a standard (negative) attendance structure. Most K-12 schools in the US do it this way.

If you're school only awards credit for courses after a verified number of hours have been completed (e.g. Johnny doesn't pass the course until he's completed his coursework and attended 12 sessions of the class), then a positive attendance structure could be used.

Assuming your data looked like this: (Ignoring the course_id in your example for simplicity)

id | student_id | date       | 
1  | 2          | 2013-10-24 | 

student_id | student_name   | 
1          | Johnny Johnson |
2          | Bobby Tables   |
3          | Suzie Smith    |

To get a report of all the kids at your school and their attendance status for the day, you could run a query like:

SELECT s.student_id, s.student_name
,  CASE WHEN a.id IS NOT NULL THEN 'Absent' ELSE 'Present' END as attendance_status
FROM Students s
LEFT JOIN Attendance a on s.student_id=a.student_id AND a.date = '2013-10-24'

Which would return:

Result :
student_id | student_name   | attendance_status |
1          | Johnny Johnson | Present           |
2          | Bobby Tables   | Absent            |
3          | Suzie Smith    | Present           |
  • Hi, In return I will only get the students list if and only if one student is absent, How will I get the students list if all the students are present? – Sajal Shrestha Feb 28 at 13:13

You will very probably define an index over ID, student_ID and presence. Since these three values are represented by 9 byte, the whole index will be less than 20MB in size and will fit completely in your memory. I wouldn't worry about it.

Alternative: you could consider only storing a row for every student that missed the lecture.


It sounds like a good case for archiving or decreasing granularity. I suspect the exact data becomes less relevant over time. My personal preference would be to have three levels of granularity: daily, weekly and yearly. After data is over a week old you summarise it to weekly. After it's over a year old, summarise it to yearly. I would've said monthly too, but the week to month mapping isn't precise.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.