Creation Table Mysql

Asked

Viewed 385 times

0

I recently started to study a little , and decided to create a table of registered here in the club that I am part.

Create database Clube
default character set utf8
default collate utf8_general_ci;

use Clube;

create table Cadastrados(
Nome varchar(50),
Sexo enum('M', 'F'),
Matricula varchar(9),
Curso enum('Arquitetura e Urbanismo', 'Engenharia Ambiental', 'Engenharia Civil', 'Engenharia de Controle e Automação', 'Engenharia de Minas', 'Engenharia de Produção', 'Engenharia Geológica', 'Engenharia Mecânica', 'Engenharia Metalúrgica', 'Outro'),
Endereco varchar(50),
CPF varchar(15),
RG varchar(15),
Aniversario date,
Email varchar(30),
Telefone varchar(20),
Socio enum('SIM', 'NÃO'),
Atleta enum('SIM', 'NÃO'),
Treinador enum('SIM', 'NÃO'),
primary key (Matricula)
)default charset = utf8;

So far so good. I managed to create the table, I used the insert into to add values and test the fields, but now that my doubts begin:

In the column "Athlete", one registered can have up to 5 modalities. How I would make it work?

At first I thought about creating another table and using the Enum command to solve the problem.

Create table Atletas(
Modalidade1 enum('Atletismo', 'Basquete', 'Futebol de Campo', 'Futsal', 'Futebol Americano', 'Handebol', 'Natação'),
Modalidade2 enum('Atletismo', 'Basquete', 'Futebol de Campo', 'Futsal', 'Futebol Americano', 'Handebol', 'Natação'),
Modalidade3 enum('Atletismo', 'Basquete', 'Futebol de Campo', 'Futsal', 'Futebol Americano', 'Handebol', 'Natação'),
Modalidade4 enum('Atletismo', 'Basquete', 'Futebol de Campo', 'Futsal', 'Futebol Americano', 'Handebol', 'Natação'),
Modalidade5 enum('Atletismo', 'Basquete', 'Futebol de Campo', 'Futsal', 'Futebol Americano', 'Handebol', 'Natação')
)default charset = utf8;

And then try to connect the two tables.

But I’m finding this medium very "pig". Until because when I add something with the insert into, I can’t modify it after, nor even put the default value as empty.

mysql

  • In my opinion you would have to create a table with the modalities, and another being the relationship enters the modality table and your registered athlete

  • You could change athlete for modality use as foreign key, and connect in another table, and in the other table will have a foreign key of the registration id in question along with the modality that it uses. I don’t know if it’s clear, if you have any questions just ask ^^

1 answer

0

If you put the modality information in another table you can have 0.. n modalities, using foreign key to get registered modalities, the scheme would look like this

I recommend doing something similar also with Courses, not to be limited to Enum courses

  • I understood the link of the Athlete table and the table of Registrants. I mean, I think I tended to write the code. create table Atleta(
matricula_A varchar(9),
Modalidade int(10),
foreign key (matricula_A) references Cadastrados (Matricula)
); But I floated in this link Athlete - Sport.

  • The idea would be to have the Modality table with all the existing modalities, and the Athlete table with the link, athlete practices such modality, making an Athlete Score with the registration you can know how many modalities he practices, and bringing the mode id you can know which modalities it practices

  • Would the Athlete table be something like that? create table Atleta(
Matricula varchar(9),
Modalidade int,
foreign key (matricula) references Cadastrados(matricula),
)default charset = utf8; create table Modalidades(
Id int,
Nome varchar(45),
primary key (Id)
)default charset = utf8;

  • Read about Data Modeling and Normal Forms

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.