Introdução
Como costuma ser o caso, um arquiteto de banco de dados precisa desenvolver um banco de dados para uma solução específica.
Numa sexta-feira à noite, no trem voltando do trabalho, pensei em como criaria um serviço de contratação de funcionários para diferentes empresas. Afinal, nenhum dos serviços existentes permite que você entenda rapidamente o quão adequado um candidato é para você. Não é possível criar filtros complexos que incluem ou excluem um conjunto de habilidades, projetos ou posições específicas. O máximo que os serviços geralmente oferecem são filtros por empresa e em parte por habilidade.
Neste artigo, permitirei diluir ligeiramente a apresentação estrita do material, misturando informações técnicas com exemplos não técnicos da vida.
Para começar, vamos analisar a criação de um banco de dados em MS SQL Server para o serviço de busca de empregos.
Este material pode ser transferido para outro SGBD como MySQL ou PostgreSQL.
Noções básicas de regras de design
Para projetar um esquema de banco de dados, você precisa se lembrar de 7 regras formais e do próprio conceito de normalização e desnormalização. Eles são a base de todas as regras de design.
Vamos descrever em mais detalhes 7 regras formais:
- relação um-a-um:
1.1) com vínculo obrigatório:
pode-se exemplificar o cidadão e o passaporte: qualquer cidadão deve ter passaporte; um passaporte para cada cidadão
Esta relação pode ser concretizada de duas formas:
1.1.1) numa entidade (tabela): Fig.1. Entidade de cidadão Aqui, a tabela de cidadão é uma entidade de cidadão e o atributo PassportData (campo) contém todos os dados de passaporte de um cidadão e não pode estar vazio (NÃO NULO). 2) em duas entidades diferentes (tabelas): Fig. 2. Relacionamento entre as entidades Citizen e PassportData
Aqui, a tabela Citizen é a entidade de cidadão e a tabela PassportData é a entidade de dados do passaporte do cidadão (o próprio passaporte). A entidade cidadão contém um atributo PassportID (campo) que se refere à chave primária da tabela PassportData. Por sua vez, a entidade de dados do passaporte contém o atributo (campo) CitizenID, que se refere à chave primária CitizenID da tabela Citizen. O campo PassportID da tabela Citizen não pode estar vazio (NOT NULL). Também é importante manter a integridade do campo CitizenID da tabela PassportData para garantir um relacionamento um para um. Em outras palavras, o campo PassportID da tabela Citizen e o campo CitizenID da tabela PassportData devem se referir aos mesmos registros como se fosse uma entidade (tabela) apresentada na cláusula 1.1.1.
1.2) com um link opcional:
um exemplo seria uma pessoa com ou sem passaporte para um determinado país. No primeiro caso, ele será um cidadão do país em questão, e no segundo - não.
Este relacionamento pode ser implementado de duas maneiras:
1.2.1) em uma entidade (tabela): Fig.3. Entidade de pessoa A tabela de pessoa é uma entidade de pessoa, e o atributo PassportData (campo) contém todos os dados de seu passaporte e pode estar vazio (NULL). 2) em duas entidades (tabelas): Fig. 4. Relacionamento entre as entidades Person e PassportData
A tabela Person é a entidade da pessoa e a tabela PassportData é a entidade de dados do passaporte da pessoa (o próprio passaporte). A entidade humana contém um atributo PassportID (campo) que se refere à chave primária da tabela PassportData. Por sua vez, a identidade dos dados do passaporte contém o atributo (campo) PersonID, que se refere à chave primária PersonID da tabela Person. O campo PassportID da tabela Person pode ser NULL. Também é importante manter a integridade do campo PersonID da tabela PassportData. Isso é necessário para garantir a comunicação um a um. O campo PassportID da tabela Person e o campo PersonID da tabela PassportData devem se referir aos mesmos registros como se fossem a mesma entidade (tabela) mostrada na cláusula 1.2.1. Ou, esses campos devem ser nulos, ou seja, conter NULL.
- :
2.1) :
. .
:
2.1.1) ():
.5. Parent
Parent , () ChildList . (NOT NULL). ChildList (NoSQL) XML, JSON .
2.1.2) ():
.6. Parent Child
Parent , Child — . Child ParentID, ParentID Parent. ParentID Child (NOT NULL).
2.2) :
, .
:
2.2.1) ():
.7. Person
Parent , () ChildList . (NULL). ChildList (NoSQL) XML, JSON .
2.2.2) ():
.8. Person Child
Parent , Child — . Child ParentID, ParentID Parent. ParentID Child (NULL).
2.2.3) , () () :
.9. Person
() Person () ParentID, PersonID Person (NULL).
« » .
- :
. , «» «», , . , , .
- :
: , . , .
, NoSQL, , . , 3 ():
.10. Person RealEstate
Person RealEstate . () () PersonRealEstate. () PersonID RealEstateID PersonID Person RealEstateID RealEstate . , PersonRealEstate (PersonID; RealEstateID) PersonRealEstate.
. , . , 1.1.2 1.2.2.
Relacionamentos um para muitos e muitos para um podem ser implementados por meio de mais de duas entidades. Para isso, são adicionados os atributos necessários, que se referem às chaves primárias das entidades correspondentes necessárias. Esta implementação é semelhante aos exemplos descritos acima nos parágrafos 1.1.2 e 1.2.2.
Onde estão as sete regras formais?
Aqui estão eles:
- Cláusula 1 (Cláusula 1.1 e Cláusula 1.2) - a primeira e a segunda regras formais
- Cláusula 2 (Cláusula 2.1 e Cláusula 2.2) - a terceira e a quarta regras formais
- cláusula 3 (semelhante à cláusula 2) - a quinta e a sexta regras formais
- item 4 - sétima regra formal
No texto acima, essas sete regras formais são agrupadas em quatro blocos funcionais.
Ao falar sobre normalização , você precisa entender sua essência. A normalização leva a uma diminuição na repetibilidade do armazenamento de informações e, portanto, a uma diminuição na possibilidade de anomalias nos dados. No entanto, a normalização ao dividir entidades leva a construções de consulta mais complexas para manipulação de dados (inserções, modificações, seleção e exclusão).
O processo de normalização reversa é chamado de desnormalização . Esta é uma simplificação da construção de consultas de acesso a dados devido à agregação e aninhamento de entidades (por exemplo, conforme mostrado acima nos parágrafos 2.1.1 e 2.2.1 usando dados estruturados incompletamente (NoSQL)).
Esse é o ponto principal das regras de design de banco de dados.
Tem certeza de que entende o relacionamento em sete regras formais? Você entendeu e não reconheceu? Afinal, conhecer e compreender são dois conceitos completamente diferentes.
Vou explicar com mais detalhes. Pergunte a si mesmo: você consegue esboçar um modelo de banco de dados, ainda que ampliado por entidades, para qualquer área temática e para qualquer sistema de informação em algumas horas? (sutilezas e detalhes podem ser concluídos perguntando a analistas e representantes de clientes). Se a pergunta o surpreende e você acha que isso é impossível, então você conhece as sete regras formais, mas não as entende.
Por alguma razão, muitas fontes ignoram o fato de que essas relações não foram inventadas, mas reveladas. Eles existem inicialmente no mundo real, tanto entre assuntos e entre assuntos e objetos.
Além disso, essa relação pode mudar, passando deum para um para um para muitos , muitos para um ou muitos para muitos . A obrigação de comunicação pode mudar ou permanecer inalterada.
Deixe-me contar a vocês um caso em que, a partir do conhecimento das sete regras formais, cheguei à compreensão dessas relações.
Certa vez, fiquei constrangido pelo fato de na universidade conhecer essas sete regras formais, mas na prática industrial (a universidade envia alunos a várias empresas para ganhar experiência profissional) durante muito tempo construí modelos de bases para diferentes áreas disciplinares. Pensei nisso e percebi que não entendia essa relação.
Observar as pessoas me ajudou, e a essência do relacionamento foi revelada em um sonho. Vou recontar esse sonho de uma forma simplificada: apenas o que permite a você entender melhor essas sete regras formais - sem detalhar tudo o mais.
O sonho era uma família com pai, mãe e filhos. O pai morre em um acidente de carro, a mãe começa a beber e os filhos são levados para o orfanato. Essas crianças ficam sem os pais por muito tempo. Então, algumas crianças têm tutores, existem vários deles também.
Você rastreou que tipo de relação existia entre os sujeitos e como essas relações mudaram?
Vamos olhar mais de perto.
- Quando a família estava completa, com vários filhos, o relacionamento entre pais e filhos era de muitos para muitos .
- , . , , , , .
- .
- — .
- , : , ().
O relacionamento entre marido e mulher é um para um com vínculo obrigatório no casamento formal ou um para um com vínculo opcional antes do registro. Só pode haver uma esposa, assim como só pode haver um marido. Pelo menos na Rússia. Mas em outro país, a poligamia é possível, e então o relacionamento entre marido e esposas será um para muitos , e entre esposas e esposos - muitos para um .
Esperançosamente, agora você está muito mais perto de compreender essas sete regras formais.
Vale a pena praticar constantemente: observar pessoas e identificar relações existentes tanto entre sujeitos quanto entre sujeitos e objetos. O texto acima descreveu o cidadão e seu passaporte como uma relação um-para-um com um link obrigatório... Ao mesmo tempo, um exemplo de uma pessoa e seu passaporte é um relacionamento um-para-um com um link opcional .
Tendo compreendido as sete regras formais, você pode projetar facilmente um modelo de banco de dados de qualquer complexidade para qualquer sistema de informação.
Você também verá que há muitas maneiras de implementar um relacionamento, e o relacionamento em si pode mudar. Um modelo de banco de dados (esquema) é um instantâneo dos relacionamentos entre entidades em um determinado momento. É por isso que é importante determinar as próprias entidades - imagens de objetos do mundo real ou área de assunto, e sua relação entre si, levando em consideração as mudanças no futuro.
Um modelo de banco de dados bem projetado, levando em consideração as mudanças nas relações na realidade ou no domínio, não precisará mudar por anos ou mesmo décadas. Isso é especialmente importante para data warehouses, onde as alterações envolvem salvar novamente grandes quantidades de dados (de vários gigabytes a muitos terabytes).
É importante lembrar que as tabelas em um modelo relacional são relacionamentos de entidades e as linhas (tuplas) são instâncias desses relacionamentos. Mas, para facilitar, as tabelas são frequentemente entendidas como entidades e as linhas da tabela são instâncias de entidades. Seu relacionamento é expresso por meio de relacionamentos na forma de chaves estrangeiras.
Projetando um esquema de banco de dados para encontrar candidatos a empregos
Depois de cobrir os fundamentos das regras de design de banco de dados na primeira parte, vamos criar um esquema de banco de dados para encontrar candidatos a emprego.
Para começar, vamos definir o que é importante para os funcionários da empresa que procuram candidatos:
- para RH:
1.1) a empresa onde o candidato trabalhava
1.2) os cargos anteriormente ocupados pelo candidato nessas empresas
1.3) aptidões e aptidões que o candidato utilizou no seu trabalho;
bem como a duração do trabalho do candidato em cada empresa em cada posição e a duração do uso de cada habilidade e habilidade
- para especialista técnico:
2.1) cargos ocupados pelo candidato nessas empresas;
2.2) aptidões e aptidões que o candidato utilizou no seu trabalho;
2.3) projetos em que o candidato participou;
bem como a duração do trabalho do candidato em cada cargo e em cada projeto, a duração do uso de cada habilidade e habilidade
Primeiro, vamos identificar as entidades necessárias:
- Empregado
- Companhia
- Posição (posição)
- Projeto
- Habilidade
- Empresas e funcionários são como muitos para muitos , já que um funcionário pode trabalhar em várias empresas e muitas pessoas trabalham para a empresa.
- Cargos e funcionários estão relacionados de forma semelhante: vários funcionários podem ocupar um cargo em uma ou várias empresas.
- , , . , — .
- : .
- , .
- .
Visto que é muito importante registrar há quanto tempo um funcionário trabalhou em determinado cargo em determinada empresa, bem como em cada projeto, o diagrama de nosso banco de dados pode ser o seguinte: Fig. 11. Esquema de banco de dados para encontrar candidatos a empregos Aqui, a tabela JobHistory atua como a entidade do histórico de empregos de cada candidato. Ou seja, é um currículo que apresenta uma relação muitos-para-muitos entre as entidades funcionário, empresa, cargo e projeto. Projetos e habilidades se relacionam entre si como muitos para muitos e, portanto, comunicam-se uns com os outros por meio da entidade ProjectSkill (tabela).
Quando se compreende a relação entre sujeitos e entre sujeitos e objetos - as já mencionadas sete regras formais - este ou outro esquema semelhante pode ser implementado “no joelho”: num pedaço de papel, em menos de uma hora. E isso também levando em consideração o cansaço após uma jornada frutífera de trabalho.
Aqui era possível simplificar o esquema de adição de dados se as "habilidades" estivessem embutidas na entidade "projetos" por meio de dados incompletamente estruturados (NoSQL) na forma de XML, JSON, ou simplesmente listar os nomes das habilidades separados por ponto e vírgula. Mas isso tornaria mais difícil amostrar por habilidade e filtrar por habilidade.
Um modelo semelhante está no coração do banco de dados do projeto Geecko .
Como você pode ver, não há nada complicado no projeto de sistemas de informação em termos de projeto de banco de dados. Este é apenas um reflexo de objetos e sujeitos da realidade, transferidos para as "entidades" do esquema do banco de dados. A relação entre essas entidades é fixada em um determinado momento, levando em consideração as mudanças futuras.
O que exatamente tiramos da realidade e colocamos na essência do esquema, e quais relações construímos no modelo, vai depender do que queremos do sistema de informação em geral, aqui e no futuro. Em outras palavras, quais dados queremos receber no momento atual e depois de um determinado momento no futuro.
Um pouco de letra
Depois de colocar o modelo em operação, pare por um momento e pense: um novo mundinho acaba de ser criado. Ele tem suas próprias entidades do mundo real e seus próprios relacionamentos. Sim, este é um mundo digital, mas agora desenvolverá seu próprio caminho. Ele se comunicará (integrará) com outros sistemas (mundos), também criados de acordo com suas próprias regras. Os dados fluirão nesses sistemas como o sangue em um organismo vivo.
E antes de ir para a cama, pense no fato de que as sete regras formais sempre existiram e que elas nos cercam por toda parte. Nem mais nem menos, sempre sete. Todos os relacionamentos na vida real podem ser decompostos nessas sete regras formais. E quando você pensa ou sonha, como as entidades se relacionam - não de acordo com as mesmas sete regras formais?
Em geral, tenho certeza de que essa relação (sete regras formais) foi revelada por um psicoterapeuta muito bom, possivelmente uma mulher. Foi há muito tempo, muito antes de o próprio conceito de tecnologia da informação aparecer. E o mais interessante é que esses relacionamentos vivem fora do banco de dados e da TI - esta os utiliza apenas para modelar sistemas de informação.
Mas mudamos um pouco o assunto. Eu apenas exorto você a abordar este processo com sua alma no momento de criar um novo sistema. E então acredite em mim, o momento da criação acontecerá. O sistema projetado desta forma será o mais vivo de todos os seres vivos do mundo digital.
Posfácio
Os diagramas para os exemplos foram implementados usando a Ferramenta de Diagrama de Banco de Dados para SQL Server . No entanto, o DBeaver possui funcionalidade semelhante .