MySQL design one-to-many?

Question

I have a database that deals with students. So I have table Students and Classes. Now every Student can go to as many classes as s/he wants; how would I model this in a MySQL database schema?

Students table:

student_id
student_name
student_age
...

Classes table:

class_id
class_name
class_profesor
...

Basically I don't know how to design a table where one student could register him- or herself for as many classes as s/he wants?

Should I make another table for this?
Is this a one-to-many relation?

Answer 1

You need a many-to-many or mapping table, e.g.:

class_student
-------------
class_id
student_id

Primary key for this table should be: (class_id, student_id).
Create an index on:
    (class_id, student_id), and
    (student_id, class_id).
Take a gander at this other SO question and answers for more detail.

---

Once you've got that set up, you can query for enrollees to a given class with:

SELECT c.class_name, s.student_name, ...
FROM   class_student cs
       LEFT JOIN class c   ON c.class_id = cs.class_id
       LEFT JOIN student s ON s.student_id = cs.student_id
WHERE  cs.class_id = '<some_class_id_here>'

Answer 2

You can maintain one more table 'Goes_to' which will store only student_id and class_id.This will normalize your data as well as you can write different types of queries based on your requirement.

Answer 3

Agree with Bernie, this is a Many to Many relationship. In addition to what Bernie explained, you can add extra fields to 'class_student' table, like for example student's average grade on that class, or how many times he didn't went to that class.