A few weeks ago I created a database to use in my Android app. It turns out that these two weeks I have been thinking about a problem that I had, and no matter how many solutions I came up with, they gave me errors and I no longer know what to do.
I explain:
My database is to record what each student is studying, so that student will have to record the centro
place where the study is carried out (eg: Colegio Mayor), the estudio
(example: ESO), those periodos
of that study (the trimesters), the asignaturas
and the deberes
. There are also eventos
, for example, excursions.
The problem is that the asignaturas
, the deberes
and the eventos
have groups of hours and those hours are all in the table horario
, because they cannot be repeated (a person cannot be in two different places at the same time). A clear example:
The
evento
1 has thehorarios
1, 2, 3, 4 and 5.The
asignatura
4 has thehorarios
8, 12, 13, 24 and 26.The
deber
3 has thehorarios
6 and 9.
Having this, I decided that the best way to solve it was by introducing the id
of the tables in the table horario
, so I could easily link to the record of the table to which that belongs horario
, but I cannot apply it because otherwise, in the table horario
I would have the key primary idHorario
plus the foreign keys of the tables Asignaturas
, Eventos
and Deberes
, and I really don't know if this way is very optimal.
As the first option is not very optimal, I thought of another way and decided that the schedule table should have a new field called Grupo
that would be like an id (it is not an id) that would link to the other tables, but I have problems because it does not let me link it since it needs to be indexed and not being a key (it's just a field) I can't create the relationship. I give an example:
The
horarios
1, 2, 3, 4 and 5 have in the fieldGrupo
the codeE-1
that belongs to theEvento
withid 1
.The
horarios
8, 12, 13, 24 and 26 have in the fieldGrupo
the codeA-4
> > that belongs to theAsignatura
withid 4
.The
horarios
6 and 9 have in the fieldGrupo
the codeD-3
that belongs > to theDeber
withid 3
.
I get the field code by Grupo
taking the first letter of the table (if they are Eventos
I take the E, if they are Asignaturas
I take the A...) plus that id
of the table record (D-3 = id 3 of the table Deberes
).
This is even worse than putting all the foreign keys in the schedule table, because I have to insert one Asignatura
with the a field first Grupo
to null
be able to get the code from Grupo
and then create the records from horarios
and then link them... Nothing, rubbish.
To make it clear, I leave you a map of what I have implemented:
SAMPLE SCHEDULE RECORD: 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 if the first way, putting all the foreign keys in the time table, is acceptable or if there is (or do you see) another possible solution. I can't think of anything anymore and I need another vision of the problem.
The rules between the tables are:
Those
horarios
related toAsignaturas
,Eventos
andDeberes
must be visible so as not to overlaphorarios
.It must be possible to create groups of
horarios
, that is,evento
1 hashorarios
1, 2, 3, 4 and 5.
I hope you can give me a hand.
Thanks.
It turned out that in SQLite Android it does not allow null foreign keys to exist, yes or yes, you have to add a data and since the data type of the foreign key is
int
it cannot be added asnull
. So this solution is not the most optimal.However, after spending several days running into problems, I managed to find the solution, since it was to implement a simple hierarchy that was born from the table
Horario
, resulting in three tables that would store the id of the schedule and the id of each table. Just as seen in the picture.The table
Horario_Evento
would be related to the tableEvento
, as well asHorario_Asignatura
withAsignatura
andHorario_Deber
withDeber
.Putting the keys in the Schedule table is the correct solution in my opinion. You just have to keep in mind that those keys admit NULL.