Mysql – How to model a database structure to retain student information

database-designMySQLPHPphpmyadmin

I am completely new to databases and SQL.

I have generated a database in myphpAdmin consisting of a table with the columns (name, student number, grade). Basically this database will hold the grades for all the students in a school. In my web application program, the index.php displays all information in the database on the browser.

So basically each student will have a row in the database. Over time, the grades for each student will be added to the database once they sit an exam. How can I just add more information to the grade column of each student row without losing the grades that are already there? (As the UPDATE operation in SQL seems to override the old data).

Or am I going about this the wrong way and need to structure each student as a database in themselves and then add their grades into those individual databases, with grade1, grade2, grade3, etc.?

Best Answer

You need to normalize the design. A column can, under sane circumstances, only hold a datum (singular).

So something like this 

CREATE TABLE student (
  studentid int AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE class (
  classid   int AUTO_INCREMENT PRIMARY KEY,
  classname text
);
CREATE TABLE student_class_grade (
  classid   int REFERENCES class,
  studentid int REFERENCES student,
  grade     float
);

This is super simple, so each student has a studentid, each class has a classid, and you can put as many entries as needed for stupid_class_grade. This doesn't account for the extra complexity needed for weighing tests and homework differently in the same class.

Related Question