Como Usar Campos Multivalorados

Um erro muito comum de modelagem de banco de dados ocorre ao se usar campos multivalorados.

Para exemplificar, vamos usar um sistema de cadastro onde as pessoas informam as linguagens de programação que conhecem.

Muitas pessoas criariam uma tabela no banco de dados com esta estrutura:


Tabela pessoas

ID	Nome	Linguagens
1	Gisele	PHP,Java,Shell Script,Ruby
2	Maria	C,C++,Shell Script,Lua
3	Ana		PHP,Ruby,Lua

Essa estrutura não é aconselhável. Imagine se precisar buscar pelas pessoas que conhecem PHP. Você terá de fazer uma consulta assim (para MySQL):

SELECT id, pessoa FROM pessoas WHERE linguagens LIKE '%PHP%';

Aparentemente não é complicado, mas, para o processador, é pior fazer um Like que fazer uma comparação de igualdade.

Situação pior é a ação de editar o campo das linguagens. É necessário editar a string.

A solução é simples: criar relacionamento entre tabelas.

Criaremos trÊs tabelas: uma conterá os nomes das pessoas; outra, os nomes das linguagens; e, finalmente, a tabela que relaicionará as duas anteriores, associando a cada pessoa uma ou mais linguagens.

Tabela pessoas

ID Nome
1  Ana
2  Gisele
3  Maria

Tabela linguagens

ID Nome
1  PHP
2  C
3  Java
4  Ruby

Tabela pessoas_linguagens

ID_PESSOA ID_LINGUAGEM
1         1
1         2
2         2
2         3
3         1
3         2
3         3

Esta última tabela é a responsável por relacionar cada pessoa às linguagens que conhece. Note que o ID da pessoa pode aparecer em quantos registros forem necessários, ou seja, nessa tabela não há chave primária.

Segundo a tabela, a pessoa 1 (Ana) conhece as linguagens 1 e 2 (PHP e C). A pessoa 2 (Gisele) conhece as linguagens 2 e 3 (C e Java). A pessoa 3 (Maria) conhece as linguagens 1, 2 e 3 (PHP, C e Java).

Com uma estrutura assim, é bem mais fácil editar valores, removê-los ou buscar pessoas que detêm determinado conhecimento.

Darei um exemplo, para MySQL, de como fazer um SELECT para mostrar as linguagens que cada pessoa conhece.

Usaremos esta estrutura de tabelas:

### Tabela com os nomes das pessoas
CREATE TABLE pessoas(
 id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
 nome VARCHAR(20) NOT NULL,
 PRIMARY KEY (id)
) TYPE InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 
### Tabela com AS linguagens
CREATE TABLE linguagens(
 id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
 nome VARCHAR(20) NOT NULL,
 PRIMARY KEY (id)
) TYPE InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 
### Tabela para relacionar AS duas anteriores
CREATE TABLE pessoas_linguagens(
 id_pessoa SMALLINT(5) UNSIGNED NOT NULL,
 id_linguagem SMALLINT(5) UNSIGNED NOT NULL
) TYPE InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 
 
INSERT INTO pessoas VALUES
(1, 'João'),
(2, 'Maria'),
(3, 'José'),
(4, 'Gisele'),
(5, 'Ana');
 
 
INSERT INTO linguagens VALUES
(1, 'PHP'),
(2, 'Java'),
(3, 'Lua'),
(4, 'C'),
(5, 'C++'),
(6, 'Shell Script'),
(7, 'Ruby');
 
 
INSERT INTO pessoas_linguagens VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 6),
(2, 1),
(2, 3),
(2, 6),
(2, 7),
(3, 1),
(3, 2),
(3, 4),
(3, 5);

A consulta SQL:

SELECT p.nome AS pessoa , l.nome AS linguagem FROM pessoas AS p JOIN pessoas_linguagens AS pl ON pl.id_pessoa = p.id JOIN linguagens AS l ON l.id = pl.id_linguagem ORDER BY p.nome ASC;

Saída:

Da para melhorar essa estrutura, fazendo, por exemplo, integridade referencial (chave estrangeira). Mas não abordarei esse tema neste post.

No meu Curso ULTIMATE PHP eu falo mais sobre banco de dados e Normalização. No capítulo final, onde montaremos uma aplicação prática, mostro como usar essa modelagem, com um exemplo passo-a-passo, usando MySQL e PDO. Clique aqui e conheça o Curso ULTIMATE PHP.

No meu Guia Gratuito de 15 Dicas e Boas Práticas de PHP falo sobre bancos de dados e outros pontos fundamentais para criar scripts PHP eficientes e com bom desempenho. Clique aqui para baixar o guia gratuito agora mesmo.

Essa e inúmeras outras dicas e técnicas eu abordo com mais detalhes no meu Curso ULTIMATE PHP. Estude PHP desde o básico, de forma 100% prática. Aprenda como se tornar um excelente programador, reconhecido e valorizado. Clique Aqui e Conheça o Curso Agora Mesmo!

 

Aprenda Ainda Mais

15 Dicas, Boas Práticas e Fundamentos do PHP

Conheça Dicas FUNDAMENTAIS para programar em PHP de forma Profissional

Não se considere Programador PHP sem antes ler este guia e adotar estas práticas!

Baixe gratuitamente este guia com 15 Dicas de PHP

The following two tabs change content below.
Graduado em Ciência da Computação, pela Universidade Federal do Paraná (UFPR), é desenvolvedor de software desde 2008, com foco em Desenvolvimento Web com PHP.
  • Mauro

    Muito bom seu artigo, hoje em dia estou trabalhando dessa forma. Mas gostaria de saber como eu iria pegar os checkbox selecionados e inserir na tabela de pessoas_linguagens, usando um for, foreach?? Eu consigo passar o codigo da pessoa e da linguagem, so não to sabendo tratar isso e inserir no banco… poderia me da um exemplo? Obrigado

  • @Mauro

    Você pode usar um foreach para percorrer o array com as opções.
    Veja um exemplo simples:


    <?php

    if ( $_SERVER['REQUEST_METHOD'] == 'POST' )
    {
    $sql = "INSERT INTO pessoas_linguagens(id_pessoa, id_linguagem) VALUES";
    $total = count( $_POST['langs'] );
    $i = 0;
    foreach ( $_POST['langs'] as $k => $v )
    {
    $sql .= "(id_pessoa, " . $k . ")" . ( $i == $total - 1 ? "" : ", " );
    $i++;
    }

    echo $sql;
    exit();
    }

    ?>

    <form method="post" action="">

    <input type="checkbox" name="langs[]" value="1" /> PHP
    <br />
    <input type="checkbox" name="langs[]" value="2" /> Java
    <br />
    <input type="checkbox" name="langs[]" value="3" /> C++
    <br />
    <input type="checkbox" name="langs[]" value="4" /> AWK
    <br />

    <input type="submit" value="enviar" />

    </html>

    “id_pessoa” é obtido usando a função que retorna o último ID inserido no banco de dados. Ou seja, cadastre a pessoa, pegue o id gerado e insira as opções selecionadas

  • Mauro

    Muito obrigado por responder Beraldo. Seguinte, a lógica que estou fazendo é um pouco diferente da sua, mais vou tentar aproveitar o que você fez e adaptar a minha lógica. Caso não consiga, voltarei aqui e mostro como to fazendo pra ver se você entende e ve se é viável fazer da forma que to fazendo ou não. Sei que você é bem mais experiente que eu, ta em um nível bem mais elevado, por isso to tentando uma ajuda nessa parte. Mais obrigado por responder.

  • Mauro

    @Beraldo
    Beraldo, muito obrigado. deu certo consegui fazer gravar no banco. Valeu mesmo ai a ajuda!

  • manolegal

    Olá Beraldo.
    Muito bom seus exemplos. Tenho em meu sistema algo identico ao q vc descreveu. Tenho 03 tabelas nos mesmos moldes. Gostaria de saber como “concatenar”as linguagens do ultimo exemplo.
    Ao inves de imprimir:
    Nome Linguagem
    Joao PHP
    Joao Lua
    Joao Java
    Gostara q imprimisse:
    Joao PHP, Lua, Java

    Espero ter passado a idéia. Valeu.

  • Wilson

    Olá Beraldo,
    Também estou trabalhando dessa forma, mas estou com um problema na hora em que eu mostro os checkbox, como posso fazer na hora de atualizar, se um checkbox for desmarcado para deletar ele do banco e atualizar a tabela. Será que você pode me passar uma ideia de como posso fazer?
    Obrigado

  • Wilson, uma maneira é apagar os registros e cadastrar novamente. É o que dá menos trabalho.

    Outra forma seria salvar numa sessao/cookie as opções atuais. depois da edição, verifica-se se há algo a menos, comparando com os valores do cookie. se sim, remove o registro do bd.

    sinceramente, eu faria a primeira opção num sistema simples. =P

  • Wilson

    Olá Beraldo,
    Eu achava que excluindo e cadastrando novamente ficaria um processo demorado, mas já que é uma das suas opções vou fazer dessa maneira.
    Valeu a ajuda.
    Obrigado.

  • Felicio

    Olá Beraldo
    Valeu ai pelo Post mas a minha duvida e similar ao de manolegal
    como fazer para ele imprimir as linguagens que joao gosta numa linha

    joao java,PHP,Lua

    obrigado desde ja

    • Basta dar echo nos valores sem inserir a quebra de linha (isso depende do elemento HTML onde você está exibindo os dados, pois pode ser um parágrafo, uma célula de tabela etc)

  • Bruno

    Mano, se eu quisesse adicionar mais pessoas usando uma checkbox para designar quais linguagens ela “sabe”. Como adicionaria isso na tabela de relacionamento entre elas? Abraço

    • Olá. Nomeie os checkboxes desta forma:

      PHP
      C++
      Ruby

      Assim, $_POST[‘linguagem’] será um array, que pode ser percorrido com um foreach, por exemplo. Aí basta montar a query para inserir na base de dados

  • Roberto Mansur

    Material bem explicado, valeu!

  • Pingback: imitation hermes birkin 35 women handbags()