How to relate tables

Question

Good afternoon.

I have three tables: tbl_students, tbl_financial responsibility, tbl_responsavel_pedagogico.

The idea is that a student has a financial manager and a teacher.

But there are three situations:

A) A student can be responsible for himself, both financially and pedagogically.

B) A single person may be responsible for a student, both financially and pedagogically.

C) A parent may be responsible for more than one student (in the case of a parent and several children).

So I’m not able to assemble the structure in a way that is coherent. 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 financial and pedagogical responsible. For the same person plays the role of the two responsible.

The way I thought (creating tbl_students with foreign keys of tbl_responsavel_financial tables and tbl_responsavel_pedagogico, is not good). So 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 that I created.

How I could create the tables and relationships, but attending the three situations, without repeating information and that is coherent?

Answer 1

Just create a table unifying student, financial and pedagogical records with some column that refers to what type of registration there simply add a column of financial responsible for her relationship to itself, something like this:

Clear and other information from your table.

For example: Let’s start treating the records as a person not to confuse So we would have 3 people as an example, where 1 would be responsible for himself in both cases 2 would be responsible for person 1 and himself and 3 would be responsible for person 1 and 2. And student 4 would not be responsible for.

then you would have in the field like, the kind of person that is that record, if necessary in your case.

ID | TYPE | NAME | RESP FIN | RESP PEDA |

1 | Student | Person 1 | 1 | 1 |

2 | Student | Person 2 | 1 | 2 |

3 | Student | Person 3 | 3 | 1 |

4 | Student | Person 3 | null | null |