Search code examples
database-normalization

If you have a data object with large features (100+), is it better to expand this data by adding columns or by storing it in row format?

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).

Solution

  • 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