Indo de um projeto para outro, infelizmente, somos confrontados com a falta de padrões uniformes para o design de banco de dados, apesar do SQL existir há várias décadas. Suspeito que o motivo seja em parte porque a maioria dos desenvolvedores não entende a arquitetura do banco de dados. Ao longo dos anos de meu trabalho na contratação de desenvolvedores, só conheci várias vezes aqueles que podiam normalizar corretamente o banco de dados. Honestamente, isso pode ser uma tarefa difícil, mas muitos dos desenvolvedores que entrevistei, mesmo fluentes em SQL, não tinham habilidades de design de banco de dados.

Este artigo não é sobre normalização de banco de dados. Se você quiser aprender isso, aqui eu contei brevemente o básico.

Se você possui um banco de dados em funcionamento, precisa responder à pergunta: “quais padrões podem ser aplicados para facilitar o uso desse banco de dados?”. Se esses padrões forem amplamente utilizados, será fácil usar o banco de dados, porque você não precisará estudar e lembrar de novos conjuntos de padrões sempre que começar a trabalhar com um novo banco de dados.

Nomeação ou sublinhado do CamelCase?

Eu sempre encontro bancos de dados nos quais as tabelas são nomeadas no estilo CustomerOrders ou customer_orders . Qual é o melhor para usar? Talvez você queira aplicar um padrão já estabelecido, mas se estiver criando um novo banco de dados, recomendo usar sublinhados para aumentar a acessibilidade. A frase "abaixo do valor" tem um significado diferente em comparação com "subestimar", mas com um sublinhado, o primeiro sempre será under_value e o segundo será undervalue . E ao usar o CamelCase, obtemos Undervalue e UnderValue , que são idênticos em termos de SQL que não UnderValue maiúsculas de minúsculas. Além disso, se você tiver problemas de visão e estiver constantemente experimentando fones de ouvido e pinos para enfatizar as palavras, é muito mais fácil ler o sublinhado.

Finalmente, é difícil ler o CamelCase para aqueles para quem o inglês não é nativo.
Para resumir, esta não é uma recomendação estrita, mas uma preferência pessoal.

Plural ou singular em nomes de tabelas?

Os especialistas em teoria de banco de dados vêm discutindo há muito tempo se as tabelas devem ser singulares (cliente) ou plurais (clientes). Deixe-me cortar esse nó górdio sem aprofundar a teoria, simplesmente com a ajuda do pragmatismo: nomes de tabelas plurais têm menos probabilidade de entrar em conflito com palavras-chave reservadas.

Você tem usuários - users ? SQL tem a palavra-chave do user . Você precisa de uma tabela de restrições? constraint é uma palavra reservada. A palavra audit
reservado, mas você precisa de uma tabela de audit ? Basta usar a forma plural de substantivos e, em seguida, a maioria das palavras reservadas não o incomodará no SQL. Até o PostgreSQL, que possui um excelente analisador de SQL, tropeçou na tabela de user .

Basta usar o plural, e a probabilidade de conflito será muito menor.

Não nomeie a coluna com o ID como "id"

Eu mesmo pequei ao longo dos anos. Certa vez, trabalhei com um cliente em Paris e o DBA reclamou de mim quando dei à coluna id o nome id . Eu pensei que ele era apenas um pedante. De fato, o nome da coluna customers.id é único e customers.customer_id é uma repetição de informações.

E depois tive que depurar isso:

 SELECT thread.* FROM email thread JOIN email selected ON selected.id = thread.id JOIN character recipient ON recipient.id = thread.recipient_id JOIN station_area sa ON sa.id = recipient.id JOIN station st ON st.id = sa.id JOIN star origin ON origin.id = thread.id JOIN star destination ON destination.id = st.id LEFT JOIN route ON ( route.from_id = origin.id AND route.to_id = destination.id ) WHERE selected.id = ? AND ( thread.sender_id = ? OR ( thread.recipient_id = ? AND ( origin.id = destination.id OR ( route.distance IS NOT NULL AND now() >= thread.datesent + ( route.distance * interval '30 seconds' ) )))) ORDER BY datesent ASC, thread.parent_id ASC 

Percebeu o problema? Se o SQL usasse nomes de IDs completos, como email_id , star_id ou station_id , os erros surgiriam imediatamente quando eu escrevesse esse código , e não mais tarde, quando tentasse entender o que havia feito de errado.

Faça um favor a si mesmo e use os nomes completos para o ID. Mais tarde obrigado.

Nomes de colunas

Dê às colunas os nomes descritivos possíveis. Digamos que a coluna de temperature não tenha nada a ver com isso:

 SELECT name, 'too cold' FROM areas WHERE temperature < 32; 

Eu moro na França, e para nós uma temperatura de 32 graus será "muito fria". Portanto, é melhor nomear a coluna fahrenheit .

 SELECT name, 'too cold' FROM areas WHERE fahrenheit < 32; 

Agora tudo está completamente claro.

Se você tiver restrições de chave estrangeira, atribua o mesmo nome às colunas nos dois lados da restrição sempre que possível. Aqui está um SQL razoável e perfeitamente pensado:

 SELECT * FROM some_table s JOIN some_other_table o ON o.owner = s.person_id; 

Este código está realmente certo. Mas quando você olha para a definição da tabela, verá que some_other_table.owner possui uma restrição de chave estrangeira com companies.company_id . Então, basicamente, esse SQL está errado. Era necessário usar nomes idênticos:

 SELECT * FROM some_table s JOIN some_other_table o ON o.company_id = s.person_id; 

Agora, fica claro imediatamente que temos um erro, basta verificar uma linha de código e não consultar a definição da tabela.

No entanto, quero observar que isso nem sempre pode ser feito. Se você tiver uma tabela com um armazém de origem e um destino, poderá comparar o source_id com o destination_id com o warehouse_id . Nesse caso, é melhor fornecer os nomes source_warehouse_id e destination_warehouse_id .

Observe também que no exemplo acima, o owner descreverá a finalidade melhor que company_id . Se isso lhe parecer confuso, você poderá nomear a coluna owning_company_id . Em seguida, o nome informará o objetivo da coluna.

Evitar valores nulos

Esse conselho é conhecido por muitos desenvolvedores de banco de dados experientes, mas, infelizmente, eles não falam sobre isso com frequência: por uma boa razão, não permita valores NULL no banco de dados.
Este é um tópico importante, mas bastante complicado. Primeiro, discutimos a teoria, depois seu efeito na arquitetura do banco de dados e, em conclusão, analisaremos um exemplo prático de problemas graves causados ​​pela presença de valores NULL.

Tipos de bancos de dados

O banco de dados pode conter dados de diferentes tipos : INTEGER, JSON, DATETIME, etc. O tipo está associado à coluna e qualquer valor adicionado a ela deve corresponder a esse tipo.

Mas o que é um tipo? Este é um nome, um conjunto de valores válidos e um conjunto de operações válidas. Eles nos ajudam a evitar comportamentos indesejados. Por exemplo, o que acontece em Java se você tentar comparar uma sequência e um número?

 CustomerAccount.java:5: error: bad operand types for binary operator '>' if ( current > threshold ) { ^ first type: String second type: int 

Mesmo se você não perceber que current > threshold compara tipos incomparáveis, o compilador capturará isso para você.

Ironicamente, os bancos de dados que armazenam seus dados - e são sua última linha de defesa contra a corrupção de dados - funcionam muito bem com tipos! Apenas nojento. Por exemplo, se sua tabela de customers tiver uma chave substituta, você poderá fazer o seguinte:

 SELECT name, birthdate FROM customers WHERE customer_id > weight; 

Obviamente, isso não faz sentido e, na realidade, você receberá um erro de compilação. Muitas linguagens de programação facilitam a captura de erros desse tipo, mas com bancos de dados, o oposto é verdadeiro.

Essa é uma situação normal no mundo dos bancos de dados, provavelmente porque o primeiro padrão SQL foi lançado em 1992 . Os computadores estavam lentos naqueles anos e tudo o que complicou a implementação sem dúvida diminuiu a velocidade dos bancos de dados.

E então valores NULL aparecem em cena. O padrão SQL os implementou corretamente em apenas um local, nos IS NOT NULL IS NULL e IS NOT NULL . Como o valor NULL é desconhecido por definição, você não pode ter operadores projetados para ele. E então existem IS NULL e IS NOT NULL vez de = NULL e != NULL . E qualquer comparação de valores NULL leva ao aparecimento de um novo valor NULL.

Se isso lhe parecer estranho, será muito mais fácil se você escrever "desconhecido" em vez de NULL:

Comparar valores desconhecidos NULL resulta em valores desconhecidos NULL .

Sim, agora eu vejo!

O que significa um valor nulo?

Armado com as migalhas da teoria, consideramos suas conseqüências práticas.

Você precisa pagar um bônus de US $ 500 a todos os funcionários cujo salário no ano for superior a US $ 50 mil. Você escreve este código:

 SELECT employee_number, name FROM employees WHERE salary > 50000; 

E você acabou de ser demitido, porque seu chefe ganhou mais de US $ 50 mil, mas o salário dele não está no banco de dados (na coluna Salário dos employees.salary é NULL) e o operador de comparação não pode comparar NULL com 50.000.

Por que existe NULL nesta coluna? Talvez o salário seja confidencial. Talvez a informação ainda não tenha chegado. Talvez este seja um consultor e não seja pago. Talvez ele tenha um salário por hora, não um salário. Há muitas razões pelas quais os dados podem estar ausentes.

A presença ou ausência de informações na coluna sugere que depende de outra coisa, e não da desnormalização da chave primária e do banco de dados. Portanto, colunas nas quais pode haver valores NULL são boas candidatas para a criação de novas tabelas. Nesse caso, você pode ter tabelas , _ , __ , etc. Você ainda é demitido por combinar salários cegamente e seu chefe não ter um. Mas então sua base começa a fornecer informações suficientes para sugerir que o problema é mais do que uma questão salarial.

E sim, foi um exemplo estúpido, mas foi a gota d'água.

Valores NULL levam a situações logicamente impossíveis

Pode parecer que sou pedante em relação a valores NULL. No entanto, vejamos outro exemplo que está muito mais próximo da realidade.

Alguns anos atrás, trabalhei em Londres para um registrador de domínio e tentei entender por que uma consulta SQL de 80 linhas retorna dados incorretos. Nessa situação, as informações definitivamente deveriam ter sido retornadas, mas isso não aconteceu. Tenho vergonha de admitir, mas levei um dia para entender que o motivo era uma combinação de condições:

• Eu usei OUTER JOIN.
  
• Eles poderiam facilmente gerar valores NULL.
  
• Valores NULL podem fazer com que o SQL dê uma resposta incorreta.
  

Muitos desenvolvedores não conhecem o último aspecto, então vamos ver um exemplo do livro Database In Depth . Um diagrama simples de duas tabelas:

suppliers

parts


É difícil encontrar um exemplo mais simples.

Este código retorna p1 .

 SELECT part_id FROM parts; 

O que esse código fará?

 SELECT part_id FROM parts WHERE city = city; 

Ele não retornará nada, porque você não pode comparar um valor NULL, mesmo com outro NULL ou o mesmo NULL. Parece estranho porque a cidade em cada linha deve ser a mesma, mesmo que não a conheçamos, certo? Então, o que retornará o seguinte código? Tente entender isso antes de ler mais.

 SELECT s.supplier_id, p.part_id FROM suppliers s, parts p WHERE p.city <> s.city OR p.city <> 'Paris'; 

Não recebemos uma string em resposta, porque não podemos comparar a cidade NULL ( p.city ) e, portanto, nenhuma das ramificações da WHERE levará a true .

No entanto, sabemos que a cidade desconhecida é Paris ou não Paris. Se for Paris, a primeira condição será verdadeira ( <> 'London' ). Se não for Paris, a segunda condição será verdadeira ( <> 'Paris' ). Portanto, a WHERE deve ser true , mas não é e, como resultado, o SQL gera um resultado logicamente impossível.

Foi um bug que encontrei em Londres. Toda vez que você escreve SQL que pode gerar ou conter valores NULL, corre o risco de obter um resultado falso. Isso acontece com pouca frequência, mas é muito difícil de identificar.

Sumário

• Use __ vez de CamelCase .
  
• Os nomes das tabelas devem estar no plural.
  
• Dê nomes estendidos para campos com identificadores ( item_id vez de id ).
  
• Evite nomes de coluna ambíguos.
  
• Se possível, nomeie as colunas com chaves estrangeiras da mesma maneira que as colunas às quais elas se referem.
  
• Sempre que possível, adicione NOT NULL a todas as definições de coluna.
  
• Sempre que possível, evite escrever SQL que possa gerar valores NULL.
  

Embora não seja perfeito, este guia de design de banco de dados facilitará sua vida.