Choice of tables for a project to handle attendance

Question

I'm working on my students attendance system project. I have a list of students along with their ID (jntuno) and I need to create a database in MySQL for storing the daily attendance of each student for each subject.

Table: students

Fields:

slno int(5) auto increment,
jntuno char(15),
name char(50),
primary key(slno,jntuno).

Data:

This students table holds the list of students and their IDs.

Now I created another table:

Table: dailyatt

Fields:

date date,
subject char(10),
classesconducted int(2),
`11341A0501` int(2),
`11341A0502` int(2),
`11341A0503` int(2),
.
.
.
`11341A0537` int(2),
primary key(date,subject).

This table will look like:

+------------+-----------+-----------------+------------+-----------+-----------+
|date        |subject    |classesconducted |11341A0501  |11341A0502 |11341A0503 |....
+------------+-----------+-----------------+------=-----+-----------+-----------+
|            |           |                 |            |           |           |

Now every day upon entering the attendance on the PHP website, a new row will be created for each subject taught on that day.

But many said that the database model is not a good one. Can someone suggest a better model for this kind of problem?

Answer 1

1. Please use sensible names for your column names. Even though I read you explanation I can only vaguely guess what slno and jntunno might mean. I assume jntunno is some sort of student id (that's what I'm guessing from the student table - each student seems to have a unique one) - so what about studentid? I assume slno is simple primary id - so why no name it id?

2. Your dailyatt table has a column for each student (based on my assumptions above). I assume it is supposed to show which student has attended a specific class at a specific date. Now what happens if you add a new student - you will have to add a new column to your db schema, and another one, and another one ....

Furthermore the new students might not have been enrolled for older classes yet when you add a new column you have to choose a value for old attendances. So you start making special values for that case.

Also try creating a query like How many students have attended a specific class. It will end up looking like this select 11341A0501+11341A0502+... from dailyatt where ... - not very nice.

In other words: Yes, it's a bad idea for more than one reason.

When designing a schema and you try to think in terms of objects or concepts then a table should describe that object or concept - meaning the columns should be the properties of it and the rows should be the various instances of it. So I'd make three tables:

courses - describes courses (id, name, subject/description)

classes - describes classes (id, date and time, courseid) (so this table holds all the classes held for a specific course)

classattendance - student attendance for a specific class (id, classid, studentid)

Then you could do a select count(*) as numStudentsAttended from classattendance where classid = 1234 to find how many student attended a specific class for example.

Answer 2

1. Remove all the 11341xxxx fields.
2. Add another table consisting of the student-ID and the course-number (I suppose, those 11341A0501) are course numbers.

To make it even better, introduce another table Courses with an ID (autoinc), the course-number (11341A0501) and -if you like- more information (name, description etc.)

Then, your student-visits-course table consists of two columns(student-ID, course-ID)

Also: Why is slno,jntuno a combined primary key? use the slno alone and create a separate index on the jntuno column to speeding up searching for a specific student by number.

Answer 3

In your students table, slno is already AUTO_INCREMENT, so the intention is that slno should uniquely identify a student. So why is jntunno also part of the primary key? I think it should be

CREATE TABLE students
( slno int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY
, jntunno char(15) NOT NULL UNIQUE
, name char(50)
, UNIQUE KEY (jntunno)
);

By the way, slno and jntunno are programmer-unfriendly names. Please pick something else.

ALTERing the dailyatt table every time you add or remove a student is going to be a nightmare. Database schema changes should be extremely rare events, because they are risky, hard to manage, and hurt performance while they happen. Basically, it's wrong.

CREATE TABLE class_sessions
( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, date date
, subject char(10)
, classesconducted int(2)
, UNIQUE KEY (date, subject) -- Maybe?
);

CREATE TABLE attendance
( class_session_id INT NOT NULL
, student_id int(5)
, PRIMARY KEY (class_session_id, student_id)
, FOREIGN KEY (class_session_id) REFERENCES class_sessions(id)
, FOREIGN KEY (student_id) REFERENCES students(slno)
);

If you want the data presented in one big table, with one column per student, you should leave it to your application-layer code or a stored procedure to compose the query.