Table structure - Link one student with multiple c

Question

I am currently facing an issue with table that I designed(please see below). One student can belong to multiple classes. I started storing the multiple values in one column later to learn this is a big no no. For example, class_id faces issues with loosing in type and becoming string when storing values delimited by a comma. I have read to make articles and it indicates to make two tables one for class and another for students but unsure how to insert data that will essentially link a student to multiple classes. What would be the best approach?

Current approach:

------------        ----------      ------------    -------------
student_fname       student_id      class_name      class_id
------------        ----------      ------------    -------------
james               Vre94b3JpXO     math,science    5697,5768
jim                 JzqQ2zRVNm1     art, music      7604,7528
jenny               xgqv9P42eYL     physical-ed     6422    
kyle                QLNM0Wbyqk0     computer,jrotoc 6315,8797
kimberly            P2egAddWN0Q     culinary-arts   8069
kayla               EGNDjWAreAy     science, art    5768,7604 
noah                bPeOyMMONGr     math, music     5697,7528
nataly              9Op53GGmqk5     jrotc           8797

Suggested approach:

------------        ----------
class name          class id
------------        ----------
math                5697
science             5768
computer            6315
physical-ed         6422
music               7528
art                 7604
jrotc               8797
culinary-arts       8069

------------        ----------
student fname       student id
------------        ----------
james               Vre94b3JpXO
jim                 JzqQ2zRVNm1
jenny               xgqv9P42eYL
kyle                QLNM0Wbyqk0
kimberly            P2egAddWN0Q
kayla               EGNDjWAreAy
noah                bPeOyMMONGr
nataly              9Op53GGmqk5

Answer 1

The solution is to introduce a third table that serves as a junction between the two domain tables. This table would hold foreign key references to the primary keys of the other tables (plus any data specific to the relation, such as enrollment date etc).

Class table:
class name          class_id (primary key)
------------        ----------
math                5697
science             5768

Student table:
student fname       student_id (primary key)
------------        ----------
james               Vre94b3JpXO
jim                 JzqQ2zRVNm1

Enrollment table:
student_id (fk to stud.)  class_id (fk to class)
------------              ----------
Vre94b3JpXO               5697
JzqQ2zRVNm1               5697
JzqQ2zRVNm1               5768

In the last table you would use a compound or composite primary key to ensure uniqueness. (The difference is that a composite pk would include other columns as well - like date or term, which would allow a student to take the same class on different occasions for example).

To query data you would join the tables on the keys:

select * 
from student s
join enrollment e on s.student_id = e.student_id
join class c on c.class_id = e.class_id

If you want to learn more about this, search for database normalization and normal forms in the context of relational databases

And here's a small demo.

Answer 2

jpw's Solution is correct. You have to live with some changes though.

If you read student table with class table, you get (of course) some rows. So if you have parts of your code that need one row for one student, you can use

select s.*, group_concat(c.name) as classes 
from student s
join enrollment e on s.student_id = e.student_id
join class c on c.class_id = e.class_id
group by s.student_id

I hope that shows how to go on.

Answer 3

create "student" TABLE with all attributes a "student" have
create "class" TABLE with all attributes a "class" have
create class_student TABLE with this attributes:

id,class_id,student_id

and insert in this table.