Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
data (DATETIME)
present/absent (TINYINT, as I'll store 1/0)
comments

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

share|improve this question
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

3 Answers 3

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)

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

Students:
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
WHERE 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           |
share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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