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:

Data in students table

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?

up vote 4 down vote accepted
  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.

  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.

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.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.