I have questions regarding the structure of the database for a frequency system. I thought of two structures and when doing a search I saw that the two exist and are used in different systems.
1 - The first one I have a frequencia
table where I have the columns id_aluno
and id_reuniao
.
In this structure I keep only members present, and no gifts will not be recorded.
2 - The second one I have a frequencia
table where I have the columns id_aluno
, id_reuniao
and type
.
In this structure I keep both gifts and absentees, and this is defined by the field tipo
where 0 = ausente
and 1 = presente
.
In a future report I will list all members and mark the days they were present. This report will be for both structures.
It will have the following template:
Membro Dia Dia Dia Dia
5 8 12 30
1 - João P A A P
1 - Maria P P P P
The days columns are meeting days in a report where I choose data inicial
and data final
so it does not follow a pattern. You may have a month with just one meeting or countless.
I will make a select
to list all members and for each member a select
of the interval meeting days.
Assuming the first structure I test if the student exists on the day of the meeting. If yes, mark it as a gift, and do not mark it as Absent.
Assuming the second structure I will make a select
of all members and in each member a select
testing the tipo
field.
My question is: which of the following structures will be more agile and easier to generate reports? Or if they have some structure more suitable for this type of system