CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-designrelational-database
Ziad EL Naggar
Ziad EL Naggar

Reputation: 51

best performance database design to store students exams result

previously i thought that it's easy to store exams data in simple table like this :

id
subject_id
student_id
mark
date

but i noticed that it is very stupid idea cuz it makes database very huge let's assume that we have school with 5000 students and there are 12 subjects for each students per month ... it means that our exams table will contain 5000*12*10 = 600000 row per year ,, ok what about if we decided to know student history from joining date to current date .. i think it's very stupid and i think also there are optimum solutions to make another design to fit my needs .... i need to store exams data in another lighter way thank's in advance :)

Upvotes: 2

Views: 3744

Answers (1)

Dan Bracuk
Dan Bracuk

Reputation: 20794

Here is another way.

Table Student stores name, etc

Table Subject has subject names such as Reading, Writing and Arithmetic, and maybe a MinimumPassingMark.

Table Exam has ExamId, ExamDate, and SubjectID. It may also have PercentageOfOverallGrade

Table StudentExam has StudentID, ExamId, and mark.

Subjects and exams have a one to many relationship (one subject can have many exams, each exam has one subject. Students and exams have a many to many relationship.

So if you want to know if Johnny can read, it's as simple as.

select case 
when sum(mark) * PercentageOfOverallGrade >= then 'pass' else 'no' end result
from Student s join StudentExam se on s.StudentId = se.StudentId
join Subject su on se.SubjectId = su.SubjectId
where s.name = 'Johnny'
and su.name = 'Reading'

Upvotes: 1

Related Questions