Doubt in creation of Table

Question

Doubt in creation of Table

Navigation

#1 by (0 votes)

1

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 data of the partners:

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

Inserting Data into 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 half of 2016. The tinyint 1 means that the enrollee has renewed in the period in question. And the tinyint 0, means that it did not renew.

I must accompany 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 by creating multiple tables Socio_Periodo_16_2 , Socio_Periodo_17_1 , Socio_Periodo_17_2 and etc ... And doing 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 quietly, but I do not know ... I'm finding a lot of "pig" to create 1 table per period. My knowledge of SQL will end here because I started to study on account of having 2 weeks.

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

Thank you in advance

mysql sql

asked by anonymous 23.03.2017 / 00:27

1 answer

Convert Array made in PHP to XML Creating Python objects from the database

score 0 · Answer 1

My suggestion is that you make a MANY-TO-MANY relationship. (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 facility. 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'