1

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?

enter image description here

MySQL Database fields are shown in the pic below:

enter image description here

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
0

Yes this design is poor, just imagine to add another hour.

This is a better aproach:

Students(**StudentID**, LastName, ...[other Student Details])

Courses(**CouseID**, Description, ...[any other Course Specific Stuff]) or to whatever they attend. If you can be sure that there is and will always be only ONE you can skip this

If possible have something like a timetable that is applied for every week. You could also have a table to match Courses and Lessons so that if a course takes place ever 5th lesson every Monday.

This is the tricky part because you don't give any information about it but in gernal it would be bad to just match Students and Lessons on Some Timestamp. Because you can't validate this. In your case you'd have 8 Lessons per day (only work days?) But with this design you can add a 9th one if needed.

Lessons(**LessonID**, StartTime, EndTime, Weekday)

Now the core part:

Attendances(Stunden[FK], Course[FK], Lesson[FK], Date)

Date is important you don't wanna have a list of every possible lesson for the next 10 years.

Also do not store any attended or not attended information. Have a "default" state (not attended) and if there is no mapping for a Student, some Lesson and a Course in the Attendances Table then he didn't attend.

If you want to add some information like

  • did attend
  • did not attend excused
  • did not attend not excused

add some integer/byte to the Attendances Table.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.