-1

So, if you can imagine a person was simultaneously enrolled in 100 different courses and had just received final grades for all courses, would it be better practice to store that information like so (wide columns):

personID Math Science English
1 90 88 98
2 91 98 90

(and ...97 other columns)

Or like this:

personID Grade Subject
1 90 Math
1 88 Science
1 98 English

This brings the columns down significantly (from 100 to 3).

philipxy
  • 14,867
  • 6
  • 39
  • 83
HermSang
  • 107
  • 3
  • 8
  • 3
    You need to study at least the basics of [data normalization](https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/). – PM 77-1 Dec 30 '20 at 20:29
  • See here: https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization – devlin carnate Dec 30 '20 at 20:43

3 Answers3

1

creating 100+ columns on any table is always bad idea. there is limit on no of columns in any database. read this to get better idea. as you can think of following way.

StudentsMarkDetails

StudentsMarkDetailsID personID SubjectId MarksObtained ExamID
1 1 3 64 1
2 2 4 36 1
3 1 4 36 2

SubjectMaster

SubjectId SubjectName
1 Maths
2 Science
3 English
Dgan
  • 10,077
  • 1
  • 29
  • 51
  • Thank you -- but quick follow-up. What would the primary key look like in StudentsMarkDetails? Assume a person takes many more subject exams, so the personID column would repeat twice or more, would you just add a new index column that auto-increments, or what would the primary key be? – HermSang Dec 30 '20 at 21:03
  • you can add ExamID in StudentsMarkDetails. so in that case StudentsMarkDetailsID would be primary key with auto-increment. check updated one. – Dgan Dec 30 '20 at 21:21
1

Definitely option two - few fixed number of columns.

The reasons are many. Here’s a few:

  • maintenance: if you have 100 columns now, tomorrow you’ll have 101. Adding a column requires a schema change, which is painful
  • access: to get a value, you need to code the column name. This a form of maintenance problem in the code that accesses it, be it queries or app code
  • queries: basic queries become impossible. Write the query that returns the average grade for a student and the problem will be immediately obvious

Here are two solid rules I made for myself that I never break:

  1. Prefer more rows over more columns.
  2. Prefer more columns over more tables.
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Also take a look at @Ganesh_Devlekar's answer's example. You can break your data down into three tables: (1) Persons - List of people, (2) Subjects - list of available subjects, (2) Grades - list of grades per subject per person. The Grades table has a reference to a PersonID and a GradeID in each row. – Adrian J. Moreno Dec 30 '20 at 20:57
  • @adrian An FK to a Subject table is of course needed, but this answers the question as stated. This site is not a consultancy service - answers don’t need, and in fact should not have, all the details of a final grand solution. – Bohemian Dec 30 '20 at 21:03
0

It is better to store the data with one row per person and per course. Why? Here are some reasons:

  • Different people may have different courses.
  • Most databases have a limit on the number of columns in a table.
  • You may want to store additional information per score, such as the date/time it was entered.
  • It is easy to add new courses or to change existing scores, if necessary.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786