My app has a requirement wherin we need to capture attendance details of a class. A class typically has 50-60 students and there will be records with attendance/absense status of each student for a day.

My idea is having a table like this:

| student_id | class | section | date       | status      |
| 2          | 7     |     A   | 2013-10-24 | 1 or X      |

X - Absent , 1 - Present

This would have records for all months for all students across all classes. So 1000 students, 30 record per student per month, this table must be ending with 1000*30*12 = 360,000 records. We'll be pulling attendance report student-wise and class-wise.

My friend has a suggestion to create a table for each year which would look like this:

| student id  | class   | section | month  | date1 | date 2 | date 3 |......| date30 |
| 1           | 2       |     C   | Jan    | X or 1|  X or 1| X or 1 |      |X or 1  |

This would mean that I'll have the attendance details of each student for a month in a single record. There would be 1000 (student) * 12 (months) = 12000 records only for an year

Please suggest which would be more suitable here, and if there's any other better way of doing it.


  • Are you concerned with tardiness at all? If a student comes late, are they marked present or absent? Basically, are you sure a binary format for absence is best and why not use a binary format (0, 1) for that instead of (X, 1) if so ? Jan 21, 2015 at 14:34
  • Thanks for the suggestion, but binary values wont suffice, if we need to add extra state than just absent, or present. Adding half day attendance would require a different indicator. Jan 22, 2015 at 6:23

3 Answers 3


I would suggest something different than you have here- a separate table to record the student_class relation:

| student_id | class | date       | 
| 2          | 7     | 2013-10-24 | 

This would have one record for a student where a class was attended. For each class and date for which there is no record, it can be seen that the student did not attend.

If you want to further normalize the design, I would suggest instead three new tables:

Student_Class: (to determine enrolled students)

| class | student_id     | 
| 1     | 2              |

Class_Date: (class_date_id surrogate key for pk(class, date)) (to determine dates classes were held)

| class_date_id | class | date       | 
| 1             | 7     | 2013-10-24 |

Student_Class_Date: (to determine which students attended which class period)

| class_date_id | student_id     | 
| 1             | 2              |

This would allow you to easily define which classes were held on which date, easy updating etc.

Edit: Added additional detail with student_class implied relation from question context

  • The enrollment of a student to a class was implied by the question, re-reading it. It's easy enough to handle. I will edit shortly. Jan 21, 2015 at 14:55
  • 1
    What's the gain of using class_date_id? Why not use the (class, date) as the PK of Class_Date? You will have to have a Unique constraint anyway. Why add a surrogate id to that table? Jan 21, 2015 at 15:06
  • I've added the student_class relation that the question implies for enrollment- a student that enrolled but never attended would have a record in student_class for the class, but no records in student_class_date. Jan 21, 2015 at 15:06
  • 1
    @ypercube Depending on the manner in which the database is used, the join would be faster on class_date_id than on (class, date). There are certainly many applications in which you'd rather use (class, date). Jan 21, 2015 at 15:10

Think about how what kind of queries and reports you want to run against your data. Lets say for example you want to know how many students were absent in the month of 2013-10. Here is how that would look with each table:

WHERE DATE BETWEEN TO_DATE('2013-10','YYYY-MM') AND TO_DATE('2013-11')-1/24/60/60

SELECT DECODE(date1,'X',1,0) + DECODE(date2,'X',1,0) + ... + DECODE(date31,'X',1,0)

If you want to know the same for the months of October and November you would have this.

WHERE DATE BETWEEN TO_DATE('2013-10','YYYY-MM') AND TO_DATE('2013-12')-1/24/60/60

SELECT SUM(DECODE(date1,'X',1,0) + DECODE(date2,'X',1,0) + ... + DECODE(date31,'X',1,0))
WHERE MONTH IN ('Oct','Nov') AND YEAR='2013';

If you want to know which days in October and November had class you would do something like this:

SELECT Date FROM TableA WHERE to_char(date,'MON-YYYY') IN ('Oct-2013','Nov-2013');

SELECT Year, Month, Day FROM
SELECT Year, Month, Day, 
    CASE WHEN Day=1 AND date1 IN ('1','X') Then 1
    CASE WHEN Day=2 AND date2 IN ('1','X') Then 1
    CASE WHEN Day=3 AND date3 IN ('1','X') Then 1
    CASE WHEN Day=31 AND date31 IN ('1','X') Then 1
    ELSE 0 END ClassHeld       
   SELECT * FROM TableB WHERE MONTH IN ('Oct','Nov') AND YEAR='2013'
   SELECT level day from dual connect by level <=31
WHERE ClassHeld = 1

As a side point, you should have a table storing which students are assigned to which classes. Having that there is no need to store both attendance and absence because each can be derived from the other, so simply store the least common. If it is more likely for someone to attend then store absence and if it more likely to have an absence then store attendance and infer absence by the lack of attendance. This also eliminates the need for the status column entirely as the name of the table would either be Attendance or Absence.

  • Thanks a lot for your answer, got myself to think on the 'what report I need from my DB' approach. Jan 25, 2015 at 17:12

It is very bad Idea that you suggest, I suggest something other,

There are 50 students in class create 50 attendance tables one for each(Table name should be registration no of students), Table will have 2 attributes one for date and other for status, Because we have to go through single student attendance most of time. Hope it will make sense.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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