Link several tables to another to obtain groups of records

A few weeks ago I created a database to use it in my Android app. It turns out that these two weeks I've been thinking about a problem I had, and no matter how many solutions I got, they gave me errors and I do not know what to do anymore.

I'll explain:

My database is to register what each student is studying, so that said student will have to register the centro where the study is done (eg: Colegio Mayor), the estudio (example: ESO), the periodos of that study (the quarters), the asignaturas and the deberes . There are also eventos , such as excursions.

The problem is that the asignaturas , the deberes and% eventos have groups of hours and those hours are all in the table horario , because they can not be repeated (a person can not be in two places different at the same time). A clear example:

  

The evento 1 has the horarios 1, 2, 3, 4 and 5.

     

The asignatura 4 has the horarios 8, 12, 13, 24 and 26.

     

The deber 3 has the horarios 6 and 9.

Having this, I decided that the best way to solve it was by entering id of the tables in the horario table, so I could easily link to the record of the table to which that horario belongs, but not what I can apply because if not, in the table horario would have the primary key idHorario plus the foreign keys of the tables Asignaturas , Eventos and Deberes , and really do not know if this form is very optimal. / p>

When the first option was not optimal, I thought in another way and decided that the timetable table had a new field called Grupo that was like an id (it is not an id) that would link with the other tables, but I have problems because it does not let me link it because it needs to be indexed and since it is not a key (it's just a field) I can not create the relationship. I give an example:

  

The horarios 1, 2, 3, 4 and 5 have in the field Grupo the code    E-1 that belongs to Evento with id 1 .

     

The horarios 8, 12, 13, 24 and 26 have in the field Grupo the code A-4 > > that belongs to Asignatura with id 4 .

     

The horarios 6 and 9 have in the field Grupo the code D-3 that belongs > to Deber with id 3 .

The code of the field Grupo I get it by taking the first letter of the table (if Eventos I take the E, if they are Asignaturas I take the A ...) plus the id of the record of the table (D-3 = id 3 of the table Deberes ).

This is even worse than putting all the foreign keys in the table schedules, because I have to insert before a Asignatura with the field Grupo to null to be able to get the code of Grupo and then create the Records of horarios and then link them ... Nothing, a garbage.

To make it clear to you I leave a map of what I have implemented:

EXAMPLE OF SCHEDULE REGISTRATION: Schedule (id = 1, group = E-1, date = 2016-06-03, start_time = 09:00:00, end_time = 10:00:00) Schedule (id = 2, group = E-1, date = 2016-06-03, start_time = 10:00:00, end_time = 11:00:00) Schedule (id = 3, group = E-1, date = 2016-06-04, start_time = 09:00:00, end_time = 10:00:00)

What I am asking is whether the first form, that of putting all the foreign keys in the timetable table, is acceptable or if there is (or you see) another possible solution. I can not think of anything anymore and I need another vision of the problem.

The rules between the tables are:

• The horarios related to Asignaturas , Eventos and Deberes must be visible so as not to overlap horarios .

• It must be possible to create groups of horarios , that is, the evento 1 has the horarios 1, 2, 3, 4 and 5.

I hope you can help me out.

Thank you.