How do you store multiple rows of data for a key and also maintain good normalization practices?

Question

Imagine in one data table, you had a person and their exam scores for different categories (math/science/english) and for different exam numbers (1, 2). Like so:

Person	Math	Science
Bob	88	76
Bob	90	99
Joe	48	46
Joe	70	69

Would it be better to normalize this table by expanding column-wise (so, we have a math1 and science1 column and a math2 and science 2 column, or just adding a row_id column?

Answer 1

What you are describing is a many-to-many (m:m) relationship, while dancing around implementing it. Your m:m is between entities Persons and Subject (categories), each of which becomes a table. You then resolve the m:m by creating a third table - call it exams.

create table persons ( person_id integer generated always as identity 
                     , name  text 
                     -- other attributes strictly about each person
                     , constraint persons_pk
                                  primary key (person_id) 
                     ) ; 

create table subjects ( subject_id integer generated always as identity 
                      , name  text 
                      , description text
                      -- other attributes strictly about each subject 
                      , constraint subjects_pk
                                   primary key (subject_id) 
                      ) ; 
                     
create table exams ( person_id   integer  
                   , subject_id  integer
                   , exam_date   date   
                   , score       integer
                   -- other attributes strictly about each exam
                   , constraint exams_pk
                                primary key (person_id, subject_id,  exam_date) 
                   , constraint exams2persons_fk
                                foreign key (person_id)
                                references persons(person_id)
                   , constraint exams2subject_fk
                                foreign key (subject_id)
                                references subjects(subject_id)                                
                   ) ; 

Note: the above is still a very simplified example but should provide guidance for resolving this type design issue. See fiddle for worked example.