Reputation: 664
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
Upvotes: 0
Views: 6319
Reputation: 44931
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.
Upvotes: 7
Reputation: 2235
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.
Upvotes: 0
Reputation: 105
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.
Upvotes: 0