Database design for attendance feature

Question

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.

Thanks.

Answer 1

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

Answer 2

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:

SELECT COUNT(*) FROM TABLEA 
WHERE DATE BETWEEN TO_DATE('2013-10','YYYY-MM') AND TO_DATE('2013-11')-1/24/60/60
AND STATUS='X';

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

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

SELECT COUNT(*) FROM TABLEA 
WHERE DATE BETWEEN TO_DATE('2013-10','YYYY-MM') AND TO_DATE('2013-12')-1/24/60/60
AND STATUS='X';    

SELECT SUM(DECODE(date1,'X',1,0) + DECODE(date2,'X',1,0) + ... + DECODE(date31,'X',1,0))
FROM TABLEB
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'
   CROSS JOIN
   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.

Answer 3

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.