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

Instead of having a separate table for each course, just have one table for all the courses with a course id and a student id as primary key.

Answer 3

The first thing i'd do with that is denormalize it, take about 10 of the tables and replace them with 1 table and a subject column. Querying and extending will be a nightmare as it stands.

Answer 4

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,...)

Answer 5

It sounds like what you want to do is reporting, otherwise known as Online Analytical Processing (OLAP). Reporting queries tend to touch a ton of data, making the optomizations for them completely different from your standard database. Data tends to be massively denormalized. This makes it important that you keep your transactional data seperate from your reporting data. If you try to do reporting on the same data that is used for letting teachers enter grades, students view small sets of grades, etc, the database operations will drag because it has been suboptimally designed for either OLAP or OLTP.

A standard way to do this is to have one database used to store the denormalized data, which is used by your website/transactional systems. You then build a data warehouse using one of the OLAP tools, and use an ETL Process at some regular interval (like once a day) to load new transactional data into the reporting database.

...

Now, this is all assuming that this is a fiarly large project, that you have significant time on your hands, and that you want to get really deep into data warehouse concepts like star schemas and the like.