Rami ZK Rami ZK - 1 year ago 57
SQL Question

How to put more than one relational id in one column mysql?

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download