How to reference multiple foreign keys in a MYSQL field

Question

I have a problem that I can not find a solution, I am making a database for a school system, in this system there is the table of materials, which has an ID and a NAME, and teachers who have an ID a NAME and the reference to the materials that they minister.

CREATE TABLE db_materias(
id int(4) not null auto_increment,
name varchar(20) not null
primary key(id)
);

CREATE TABLE db_professores(
id int(4) not null auto_increment,
name varchar(20) not null
materias int(4)
primary key(id)

foreign key(materias) references db_materias(id)
);

The problem is, there are occasions when the same teacher ministers multiple subjects, I can make the teacher have a subject but I can not get several, at first I thought about creating an array to store it, but I found that MYSQL has no arrays, I also thought about creating several columns for subjects but I believe that is not the most efficient way to solve. I can’t find anywhere a way to resolve this kind of situation, I’m starting to learn about Databases, I have no experience with the topic. I appreciate the help, thank you.

Answer 1

To make this kind of relationship I would use another table that would be taught subjects teacher with the fields can be null so you can make the relationship with the materials

CREATE TABLE db_materias_professor(
id int(4) not null auto_increment,
professor int(4) not null
materia_a int(4)
materia_b int(4)
materia_c int(4)
materia_d int(4)
primary key(id)

foreign key(professor) references db_professores(id)
foreign key(materia_a) references db_materias(id)
foreign key(materia_b) references db_materias(id)
foreign key(materia_c) references db_materias(id)
foreign key(materia_d) references db_materias(id)
);

and change the reference of the db_teachers table of

foreign key(materias) references db_materias(id) 

for

foreign key(materias) references db_materias_professor(id)