Good afternoon.
I have three tables: tbl_alunos, tbl_responsible_financial, tbl_responsavel_pedagogico.
The idea is for a student to have a financial officer and a teacher.
But there are three situations:
A) A student can be responsible for himself, both financially and pedagogically.
B) A single person can be responsible for a student, both in the financial and in the pedagogical part.
C) A parent can account for more than one student (in the case of one parent and multiple children).
So I can not build the structure so that it is consistent. For example:
-
In situation A, I end up having to create the data in the student table and repeat the same data in the other tables. For the student is responsible for himself.
-
In situation B, I have to create the same data in the tables of responsible financial and pedagogical. Because the same person plays the role of both.
In the way that I thought (creating the tbl_alumns with foreign keys of the tbl_responsible_financial and tbl_responsevel_pedagogic tables, it is not good). Thus we assume that there may be a student with 0, 1 or more responsible.
But considering that there is no student without responsibility, it would completely change the relationships I created.
How could I create tables and relationships, but taking care of the three situations, without repeating information and being consistent?
