What is the most optimised way to store this data?

Question

I am recording student results for various courses. Students attend various facilities and so they have various academic calendars. In other words, a student can have results in either:

• Yearly Results (One mark at the end of the year
• Semester Results (Two terms, mark at end of each and then the year)
• Trimester Results (Three terms, mark at the end of each and then the year)
• Terms Results (Four terms, mark at the end of each and then the year)

So, should I store the data as a csv string and explode it then make any calculations to the data, etc:

year_results str
year_end tinyint

or just store it as I need it/as it is and have a bunch of null values for the types not required

semester_1 tinyint
semester_2 tinyint
trimester_1 tinyint
trimester_2 tinyint
trimester_3 tinyint
term_1 tinyint
term_2 tinyint
term_3 tinyint
term_4 tinyint
year_end tinyint

Advantages / disadvantages of both?

Answer 1

I think I would have the following:

facilities(facility_id*,no_of_terms)

courses(course_id*,facility_id)

enrolment(student_id*,course_id*)

grades(student*,course_id*,term*,grade)

* = (component of) PRIMARY KEY