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