How would I optimize this database design?

Question

I am creating an application which generates statistical information for a pupil's performance over their school career. To achieve this, I need to store every grade ever attained by a pupil.

But I have no idea how to optimally store this information. This is a preliminary design, but don't know how it will hold up in practice.

So each pupil after a full 16 year education will have 288 pieces of grade data, 18 per year. In addition to that there is their personal information, which holds their name, DOB etc.

So, how could I optimally hold this data?

Answer 1

Your design is far too specific. You will need to change the schema every time the grading scheme changes or a new course is added!

Instead, you could have an abstracted design with Course, Grade, and SubGrade tables. Courses will have Names, and Grades and SubGrades will have types. That will give you lots of flexibility.

Answer 2

it would be too difficult to add another grade or another course in the current design

Actually, it would be better to have 4 tables, 'Students', "Grades", 'Courses' & "notes".

• the Students table holds all the needed information on the students,

• Each student belong to a certain
  "grades" at a given moment

• each grades contains several
  "courses" (ex. English, maths...) at a given moment
• each course has 0+ notes (ex.
  Reading, Listening for english,...)