如何在一列mysql中放置多个关系id？

Question

suppose that I have two tables first of them is :

   Course table  
   ----------------
   |id | name     |
   |0  | pr course|
   |1  | science  |
   ----------------

and the other is :

Teacher table 
----------------------
|id | name | Courses |
|0  | mark | 0 ,1    |
|1 | john | 0        |
----------------------

I want to put in the course column the id's of courses which the teacher gives to use them in join statement . what is the best method to do something like that . I hope that i was clear enough and thank you.

Answer 1

The way you are designing your database breaks the First Normal Form which says that every attribute of your database should be atomic. Typically, the Coursescolumn is not atomic because it is a list.

You need to create a new table like GivenBy that look like

   GivenBy table  
   ------------------------
   |course_id | teacher_id|
   ------------------------

In this table, the Primary Key is composed of the 2 columns (course_id and teacher_id). Each row stores one relation between a teacher and a course. Finally, you should have Foreign Keys referencing the id's of Course and Teacher