1

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.

  • 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 ? – LowlyDBA Jan 21 '15 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. – user2375506 Jan 22 '15 at 6:23
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

  • 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. – Thomas Cleberg Jan 21 '15 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? – ypercubeᵀᴹ Jan 21 '15 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. – Thomas Cleberg Jan 21 '15 at 15:06
  • @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). – Thomas Cleberg Jan 21 '15 at 15:10
1

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.

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

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.