Federico Ribero Federico Ribero - 3 months ago 10
SQL Question

Multiples foreign key for 1 column

I have this problem:

I have a "school" database and need to reference the Teachers table with the Subject table.

Teachers will instruct more than one subject. And here is the problem.

Teachers table:


  • id_Teacher <- PK

  • id_Subject

  • id_Course

  • name

  • surname

  • address



etc.

Subject table:


  • id_Subject <- PK

  • name



I always worked with 1 value on foreign key. ex:

SELECT * FROM Teachers WHERE id_Subject = 1


but now I don't really know what I have to do.

Answer

What I think you're trying to describe is called a "many to many" relationship. It requires an intermediary table to connect the entity tables, and in many cases the intermediary table becomes a business entity in and of itself.

Consider for example:

Teachers
----------
ID
Name
etc.

Subjects
----------
ID
Name
etc.

TeacherSubjects
----------
ID
TeacherID (FK to Teachers)
SubjectID (FK to Subjects)

The relationship between the Teachers and the Subjects itself becomes a place to potentially store data. Thinking about the subject domain (a school), the TeacherSubjects table sounds like it might be a Classes table waiting to happen. Where you could put information about a given instance of a class, which is a business entity that has a teacher and a subject.

Comments