Search code examples
mysqllaraveldata-storage

What is the most optimised way to store this data?

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?

Solution

  • 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