Save several values in the field of the mysql table

Question

I wanted to find a way to save, for example, the codes of some doctors who participated in a consultation of an animal.

I would like to keep more than one veterinarian in the consultation. (as many as is necessary)

create table if not exists consulta(
codConsulta character(8) not null primary key,
crv character(5) ,
codAnimal character(8) not null,
diagnostico text not null,
constraint fk_crv foreign key (crv) references veterinario(crv),
constraint fk_codAnimal foreign key (codAnimal) references animal(codAnimal))engine=INNODB default charset = utf8;

Answer 1

The best way would be to have three tables (consultation, veterinary and veterinary consultation ). The veterinary table_query would be n to n keeping the codes of consultation and veterinary

N to N (explanation)

https://fmhelp.filemaker.com/help/fmp/pt/index.html#page/Fmp_help/Many-to-Many-relationships.html

I made an example working on http://sqlfiddle.com/#! 9/b94c3c/5

CREATE TABLE IF NOT EXISTS `veterinario` (
  `id` int(6) unsigned NOT NULL,
  `nome` varchar(80) NOT NULL,  
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `veterinario` (`id`, `nome`) VALUES
  ('1', 'Carlos Manga'),
  ('2', 'Leonardo Pires'),  
  ('3', 'João Duarte');
  
CREATE TABLE IF NOT EXISTS `cliente` (
  `id` int(6) unsigned NOT NULL,
  `nome` varchar(80) NOT NULL,  
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `cliente` (`id`, `nome`) VALUES
  ('1', 'Fernando'),
  ('2', 'Maria Inês'),  
  ('3', 'Carmem');
  
CREATE TABLE IF NOT EXISTS `consulta` (
  `id_consulta` int(6) unsigned NOT NULL,
  `id_cliente` int(6) NOT NULL,
  `dt_consulta` date NOT NULL,
  PRIMARY KEY (`id_consulta`)
) DEFAULT CHARSET=utf8;

INSERT INTO `consulta` (`id_consulta`, `id_cliente`, `dt_consulta`) VALUES
  ('1', '1','2020-05-05'),
  ('2', '1','2020-05-13'),  
  ('3', '3','2020-05-27'),
  ('4', '2','2020-06-02'),
  ('5', '4','2020-06-30'),  
  ('6', '5','2020-07-16');
  
CREATE TABLE IF NOT EXISTS `veterinario_consulta` (
  `id_consulta` int(6) unsigned NOT NULL,
  `id_veterinario` varchar(80) NOT NULL,  
  PRIMARY KEY (`id_consulta`,`id_veterinario`)
) DEFAULT CHARSET=utf8;

INSERT INTO `veterinario_consulta` (`id_consulta`, `id_veterinario`) VALUES
  ('1', '2'),
  ('1', '3'),  
  ('2', '1'),    
  ('3', '2'),
  ('3', '3'),  
  ('4', '2'),
  ('5', '1'),  
  ('3', '1');

Example query

select c.id_consulta, l.nome cliente, v.nome veterinario
  from consulta c,
       veterinario_consulta vc,
       cliente l,
       veterinario v
 where c.id_consulta    = vc.id_consulta
  and  v.id             = vc.id_veterinario
  and  c.id_cliente     = l.id

You can return veterinarians in a row also using group_concat

https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

select c.id_consulta, l.nome cliente, group_concat(v.nome) veterinario
  from consulta c,
       veterinario_consulta vc,
       cliente l,
       veterinario v
 where c.id_consulta    = vc.id_consulta
  and  v.id             = vc.id_veterinario
  and  c.id_cliente     = l.id
  group by c.id_consulta, l.nome