How can I model a database structure to retain student information?

Question

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.?

Answer 1

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.

Answer 2

At a minimum, you would probably want to have at least two tables in your database, student and graded_assignment (or graded_test, if all grades are based on tests only).

student would have the student's name and id.

graded_assignment would have the student's ID (to tie the grade to a specific student), the total possible point value of the assignment, and the actual number of points earned by the student.

The total grade would be calculated through a query, something like:

SELECT s.name, s.id, SUM(g.points_earned) * 100 / SUM(g.points_possible) as percent_grade
  FROM student s
         INNER JOIN graded_assignment g ON (s.id = g.student_id)
 GROUP BY s.name, s.id
;

In reality, you would probably have a separate assignment table that would list information about each assignment/test/whatever, including the total points available, and the graded_assignment table would link back to that.

In all likelihood, you'd have a number of other tables. Students would be in one or more class; each class would have an instructor, who might teach multiple classes; assignments would be tied to a class. You might even have a scenario where an instructor teaches the same course to multiple classes, which might change what an assignment got tied to.