Mysql – Table Design for Student Attendance hourly for each day

cdatabase-designMySQLmysql-5mysql-5.5

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

Best Answer

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.

Related Solutions

Sql-server – Data Model For Summarizing Student Info

Calculate those counts on the fly.

For most data sets of this nature, I would imagine calculating the count of tardiness events would be relatively cheap. How many rows do you expect there to be in the long run in your students or attendance tables? Probably on the order of tens of thousands at most. Contrast that to calculating an account balance from its transaction history in database for a large bank with billions of transactions. In this case I would consider persisting a summary of these aggregations somewhere.

If you wanted to pre-calculate this data anyway, I would summarize the count of tardiness events per student per year in an indexed view as suggested in the top answer here.

For example:

CREATE VIEW dbo.tardiness_summary
WITH SCHEMABINDING
AS
SELECT
     student_id
   , year
   , COUNT_BIG(*) AS tardy_count
FROM dbo.attendance
GROUP BY 
     student_id
   , year
;

CREATE UNIQUE CLUSTERED INDEX IX_tardiness_summary
ON dbo.tardiness_summary (
     student_id
   , year
;

This is not a flexible approach, however, as this view is now schema-bound to the base tables and thus any modifications to either the view or the table will require rebuilding the view. Indexed views also have many restrictions on how they may be created or queried. Their advantage is that they guarantee the summary table will stay in sync with its sources because the database engine is now doing this work for you.

Design question about student assessment data

The first thing you want to do to keep your thinking straight is to choose some clear terminology. You are using the word "assessment" in two different contexts: (i) a Test and (ii) the results for a student taking the test. I understand that "test" may not be the best term, since you might be assessing based on lots of different kinds of work. The important thing is that you need to clearly differentiate between a measuring instrument and the results of an individual student against that measuring instrument.

Keeping these concepts separate will help you to keep your thinking straight when designing your database.

I'd recommend that all of your RESULTS be kept in a single table. That will make things like report cards easier to handle. Whether the result is a percentage, a letter grade or a pass/fail, you probably want to have a common representation (percentage is a good lowest common denominator) and a weight to help you aggregate marks across assessments.

Best Answer

Related Solutions

Sql-server – Data Model For Summarizing Student Info

Design question about student assessment data

Related Question