Choice of tables for handling attendance

Question

I'm working on my project in PHP [handling students attendance system]. 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.So I created my tables in this way :

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 id

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 in 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 ...
What's wrong with this database structure?

So can someone suggest me a better model for this kind of problem?

Answer 1

You are creating a column for every student, I think is the bad thing.

Why you just not create table like this one?

`date` date,
`slno` int(5),
`slnoconducted` int(2),
`subject` char(10),
primary key(`date`, `slno`, `subject`)

So, you can insert in this table some values like:

("2013-10-06", 1, 5, "Class A")

And it will be treated as: By the date 2013-10-06 the student with slno #1 (11341A0501) gained slnoconducted == 5 with the description "Class A".

After, if you want, for example, to get all records today, you can use query:

SELECT * FROM dailyatt WHERE `date` = '2013-10-06'

If you want to get all records for one student, you can use query:

SELECT * FROM dailyatt WHERE `slno` = 1

And even:

SELECT d.*, s.`jntuno`, s.`name` 
FROM `dailyatt` d
LEFT JOIN `students` s ON d.`slno` = s.`slno`
WHERE `date` = '2013-10-06'

It is better way to deal with records. If you will create column for every student, you will lose flexibility of the DB, because you will have to create/delete another column after any changes in students table.

I hope I described it correctly, because I'm not sure I have good English.

Answer 2

Image you have 200 students offered at your school. You would have 200+ columns in your second table. You should split it up to two tables. One for all the classes that are offered at school and the second for the actual attendance records.

"classes" table

"attendance table"

Then join them together.

SELECT student_id, class_name, date, class_attended
FROM attendance AS ABB2
    LEFT JOIN classes AS ABB1 ON ABB1.id = ABB2.class_id