Database design for attendance feature 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.
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