CODEWITHSUNDEEP
database-design
delete
delete

Reputation:

Question about a database design for a school

What would a good database design be for a school's grade schema.

Basically, I have the following:

enter image description here

The problem lies when trying to get the average of the grades. I can't get say the average of all grade in all subject in the first trimester.

Any suggestion on a good DB design for a grading system?

Upvotes: 0

Views: 993

Answers (2)

AmaDaden
AmaDaden

Reputation: 1204

Having a field in the data base that contains the average grade is bad database design. This data is redundant (since you already have all the data you need to calculate this value elsewhere) and should not be stored. A major database design rule is never store what you can calculate. This results in you needed to update two things when you update one. In this case you would need to update the averages table when you update the grades that are used to calculate them.

However you might want to make a view that calculates the average for you. A view is basically a table that is populated by a select query. To go about creating a view first create a select query that returns the averages you want along with what ever data you want to use to look up this average (most likely IDSubject or IDStudent). Then look up how to create a view in your database, It should be able to create a view from a Select query.

Upvotes: 1

Axel Fontaine
Axel Fontaine

Reputation: 35189

Add a class table between subject, grade and score.

  • The class table references a subject, a grade and a trimester.
  • The score table references class instead of subject.

You should then have no problem aggregating and averaging scores per trimester and per grade.

Upvotes: 0

Related Questions