Doubt in Table creation

Question

Good Night, I am creating a database for an association, where it is necessary to do the renewal every semester. I created a registration table with the members data:

create table Cadastrados(
Nome varchar(50),
Matricula varchar(9) unique,
primary key (Matricula),
)default charset = utf8;

Entering data in the Registered table:

insert into Cadastrados values
('Julio', '13.2.7777'),
('Mila', '14.2.5555'),
('Carlos Junior', '12.1.1010');

So far so good, my "problem" starts now:

create table Socio_Periodo_16_1 (
Matricula_Cadastrado varchar(9) not null,
Socio_16_1 tinyint(1) not null,
foreign key (Matricula_Cadastrado) references Cadastrados (Matricula)
)default charset = utf8;

insert into socio_periodo_16_1 values
('13.2.7777', '1'),
('14.2.5555', '1'),
('12.1.1010', '0');

16_1 means 1st semester 2016. Tinyint 1 means the registrant has renewed in the period in question. And tinyint 0 means that it has not renewed.

I must follow if the registered has renewed or not for several periods (16.1, 16.2, 17.1, 17.2, 18.1, 18.2...). The way I found it was creating several tables Socio_Periodo_16_2, Socio_Periodo_17_1, Socio_Periodo_17_2 and etc... And making a select of type:

select cadastrados.Nome, cadastrados.Matricula, socio_periodo_16_1.Socio_16_1, socio_periodo_16_2.Socio_16_2, socio_periodo_17_1.Socio_17_1
from cadastrados
inner join socio_periodo_16_1 on socio_periodo_16_1.Matricula_Cadastrado = cadastrados.Matricula
inner join socio_periodo_16_2 on socio_periodo_16_2.Matricula_Cadastrado = cadastrados.Matricula
inner join socio_periodo_17_1 on socio_periodo_17_1.Matricula_Cadastrado = cadastrados.Matricula
order by Nome;

The code ran smoothly, but I don’t know...I’m finding it very "pig" to create 1 table per period. My knowledge in SQL end up here, because I started studying on my own about 2 weeks ago.

So I come here to ask, is there anything more practical I can do to optimize this code?

Thanks in advance

Answer 1

My suggestion is that you make a relationship MANY-FOR-MANY. (Here is a conceptual example. Attention to **SociosPeriodo**

Conceptually speaking you need to create:

create table Cadastrados(
Nome varchar(50),
Matricula varchar(9) unique,
primary key (Matricula),
)default charset = utf8;

create table Periodo(
Nome varchar(50),
Id int unique,
primary key (Id),
)default charset = utf8;

create table SociosPeriodo (
Matricula varchar(9) not null,
PeriodoId int not null,
foreign key (Matricula) references Cadastrados (Matricula)
foreign key (PeriodoId) references Periodo (Id)
)default charset = utf8;

Then to consult this, you will have much more ease. For example:

Select * from Cadastrados C
Inner Join SociosPeriodo S on S.Matricula = C.Matricula
Inner Join Periodo P on S.PeriodoId = P.Id
Where P.nome = 'NomeSeuPeriodo'