Table Design for Student Attendance hourly for each day I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate table (student_details). 

As of now, the fields I have created in attendance_details are: attendance_no (Foreign Key which is Primary Key in the student_details table), date, hour1, hour2, hour3, hour4, hour5, hour6, hour7, hour8. 

A User will only mark the absentees, and the remaining students should be marked as present.

Is this a good design, since I want the report for each day attendance based on hours of a all students in a class for every month? The Front-end is being developed C#. 

Also, I need to keep track of every hour attendance. 

Can you help me with the SELECT query which should generate report as below?



MySQL Database fields are shown in the pic below:
It appears the OP is conflating how the data should be stored in the database with how the data will be displayed and/or entered when taking classroom attendance.  

These are not necessarily the same thing.  Display should not drive storage.

The UI could display the data the way the OP shows above in a classroom attendance form and have a middle layer that maps those values to and from more normalized tables in the database.

More work for the middle layer but more extensible and configurable when 

(1) the attendance day is longer or shorter
(2) students are added or removed from the class


An entry in an CLASS_ATTENDANCE table will have all the data that changes on a daily basis with foreign keys for the class, student, and status.

(0) CLASS_ATTENDANCE ID
(1) CLASS ID
(2) STUDENT ID
(3) DATE
(4) STATUS CODE
(5) and perhaps TEACHER ID of the person who took attendance


An entry in a CLASS table will have

(0) CLASS ID
(1) COURSE ID
(2) PERIOD ID
(3) and perhaps TEACHER ID of the person who normally teaches the class


An entry in a PERIOD table will have

(0) PERIOD ID
(1) START TIME (for example 08:00)
(2) END TIME (for example 08:55)


Periods usually do not change during a single semester

There would like be a CLASS_STUDENT table that tracks which students are assigned to which class and drives filling in the CLASS_ATTENDANCE table daily.

(0) CLASS_STUDENT ID
(1) CLASS ID
(2) STUDENT ID


This just one way to normalize the data.  And there is still some redundancy between the CLASS_STUDENT table and the CLASS_ATTENDANCE table.