0

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?

hem
  • 1,012
  • 6
  • 11
SupaMonkey
  • 876
  • 2
  • 9
  • 25
  • Make a database with proper relationship among the entities. – Sagar Gautam Aug 06 '19 at 11:13
  • Thanks, but I'm not looking for a 'complete answer on how to structure my entire database' - im specifically referring to a single table on how best to store grades from a size and speed point of view – SupaMonkey Aug 06 '19 at 11:48

1 Answers1

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

Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • thanks for the complete answer, but I'm not specifically looking for schema - but essentially/rather, the benefits of storing in a single field vs various (while having some null). From a db size / db & query speed / load / operations perspective. – SupaMonkey Aug 06 '19 at 11:54
  • I think I tacitly answered that! – Strawberry Aug 06 '19 at 11:55
  • Thanks - I will accept your answer, but I found some answers that are more in line with what I was looking for below. My use scenario is one-to-one so I think I will go with my option (2) I mentioned above: https://dba.stackexchange.com/questions/15335/are-many-null-columns-harmful-in-mysql-innodb https://stackoverflow.com/questions/229179/null-in-mysql-performance-storage https://stackoverflow.com/questions/13773558/php-mysql-storing-multiple-values-in-database-column https://stackoverflow.com/questions/9774715/mysql-multiple-tables-or-one-table-with-many-columns – SupaMonkey Aug 06 '19 at 12:05
  • I wouldn't - but hey, it's your party. – Strawberry Aug 06 '19 at 12:34
  • Just for clarity for future visitors, what I ended up with was term_1_grade, term_2_grade, term_3_grade, term_4_grade, year_end_grade, academic_calendar_type (to determine whether its a semester/trimester or term based grade set). Since there is a single fixed set of values per course (one-to-one), I didnt feel the need to make a separate table with a grade per row. – SupaMonkey Aug 06 '19 at 12:36