Um banco de dados pode ser muito relacional?

Question

Eu tenho uma pergunta sobre design de banco de dados. Eu estou perguntando aqui porque eu sou um entusiasta / hobby e não um "profissional", então eu não quero postá-lo na troca de pilha "Database Administrators" especificamente voltada para profissionais. Espero que este seja o lugar certo.

Eu gostaria de construir um sistema para rastrear encontros entre prisioneiros e agentes penitenciários. Ao tentar criar um banco de dados relacional, eu criei as cinco tabelas a seguir:

CREATE TABLE 'encounters' (
  'id' INT NOT NULL,
  'encounterdate' DATETIME NULL,
  'officers_id' INT NOT NULL,
  'prisoners_id' INT NOT NULL,
  PRIMARY KEY ('id')
);

CREATE TABLE 'officers' (
  'id' INT NOT NULL,
  'badgenumber' VARCHAR(10) NULL,
  'lastnames_id' INT NOT NULL,
  'firstnames_id' INT NOT NULL,
  PRIMARY KEY ('id')
);

CREATE TABLE 'prisoners' (
  'id' INT NOT NULL,
  'regnumber' VARCHAR(10) NULL,
  'lastnames_id' INT NOT NULL,
  'firstnames_id' INT NOT NULL,
  PRIMARY KEY ('id')
);

CREATE TABLE 'firstnames' (
  'id' INT NOT NULL,
  'name' VARCHAR(45) NULL,
  PRIMARY KEY ('id')
);

CREATE TABLE 'lastnames' (
  'id' INT NOT NULL,
  'name' VARCHAR(45) NULL,
  PRIMARY KEY ('id')
);

com os seguintes relacionamentos:

Essaconfiguraçãomeimpediriadearmazenartodososnomesesobrenomesduasvezes(umavezparaosoficiaiseumavezparaosprisioneiros),masnãoseicomorecuperaronome/sobrenomedeumoficialespecíficoEoprimeiroprisioneiroespecífico/sobrenomeparaumdeterminadoencontro.Idealmente,eugostariadeusarumainstruçãoSELECTparaobterumregistroqueseparececomumadaslinhasabaixo:

Eu posso usar joins internos para obter nomes e sobrenomes para um oficial:

SELECT
  encounters.encounterdate,
  officers.badgenumber,
  prisoners.regnumber,
  fnames.name,
  lnames.name
FROM
  encounters
  INNER JOIN prisoners ON encounters.prisoners_id = prisoners.id
  INNER JOIN officers ON encounters.officers_id = officers.id
  INNER JOIN fnames on officers.firstnames_id = fnames.id
  INNER JOIN lnames on officers.lastnames_id = lnames.id
WHERE
  officers.badgenumber = "b503"

ou um prisioneiro:

SELECT
  encounters.encounterdate,
  officers.badgenumber,
  prisoners.regnumber,
  fnames.name,
  lnames.name
FROM
  encounters
  INNER JOIN prisoners ON encounters.prisoners_id = prisoners.id
  INNER JOIN officers ON encounters.officers_id = officers.id
  INNER JOIN fnames on prisoners.firstnames_id = fnames.id
  INNER JOIN lnames on prisoners.lastnames_id = lnames.id
WHERE
  officers.badgenumber = "b503"

mas não consigo descobrir se é possível obter ambos para um determinado encontro usando apenas uma instrução SELECT.

É claro que eu poderia fazer isso com um procedimento / função armazenado e alguns SELECTs, mas estou interessado em saber se existe uma maneira mais simples de fazer isso com um SELECT. Ou isso é apenas uma maneira ruim de criar um banco de dados?

Obrigado pela sua ajuda.

    

Answer 1

Seu exemplo com firstnames e lastnames é interessante. O que isso adiciona à imagem? Qual o valor que tem para colocar firstnames em uma tabela separada? Se você pegar todas as pessoas com o nome "Marc", o que você tem então? Isso significa alguma coisa? E se você pegar todas as pessoas com sobrenome "Smith" - todas elas pertencem a uma família ou clã ou raça ou nacionalidade? Todos moram na mesma cidade? Provavelmente não!

Se ter o nome "Smith" significa que você tem cabelos castanhos, olhos azuis e geralmente se veste de estilo cowboy, faria sentido. Uma analogia possível aqui é: se o item é um produto do tipo TV, provavelmente tem um controle remoto, precisa de um cabo de alimentação, etc.

Se os nomes tivessem 1 MB de tamanho, faria sentido usar um id. Isso economizaria muito armazenamento duplicado. Mas com nomes normais, não faz muita diferença, enquanto resulta em mais complexidade. O banco de dados não é apenas mais complexo, mas o código que se conecta ao banco de dados também é mais complexo e, como não há nenhum benefício, é melhor evitá-lo.

Como não há nada significativo ou útil conectado ao primeiro nome ou sobrenome, não o coloque em uma tabela separada.

Você menciona em um comentário que pode salvar 240KB quando 20k pessoas tiverem o mesmo nome. Isso na maioria das vezes não é uma grande defesa. É claro que há situações em que isso faz sentido, mas no mundo moderno com muito espaço em disco, economizar 1 MB de dados é totalmente irrelevante.

Quando você menciona os dados salvos, eu me pergunto se vai custar desempenho ou não. Eu não tenho idéia, mas se estamos falando de números e isso é o Facebook e nós consultamos milhões de nomes por dia, eu me pergunto o que será mais eficiente, porque isso significa uma junção extra. Isso deve ser testado e, com base nesse teste, você decide.

Se você tiver muitas consultas ou espaço muito limitado, verá que usar uma tabela extra economiza o dia - faça isso!

    

Answer 2

Em resposta à sua pergunta sim ou não, sim. Algumas estruturas são melhor desnormalizadas em um grau apropriado. O que é apropriado? Depende, claro.
No seu exemplo em particular, a resposta proposta pelo AEonEX está correta não por causa da desnormalização, mas porque esses nomes são propriamente atributos das entidades que eles nomeiam. Pode ser apropriado exportar um estado de um endereço porque o estado é real, tem existência e todas as referências a ele referem-se realmente à mesma coisa real. Um nome, não tanto.

    

Answer 3

Não há necessidade de tabelas firstnames e lastnames . Em vez disso, armazene os firstname e lastname em officers e prisoners em vez de firstnames_id e lastnames_id .

CREATE TABLE 'encounters' (
  'id' INT NOT NULL,
  'encounterdate' DATETIME NULL,
  'officers_id' INT NOT NULL,
  'prisoners_id' INT NOT NULL,
  PRIMARY KEY ('id')
);

CREATE TABLE 'officers' (
  'id' INT NOT NULL,
  'badgenumber' VARCHAR(10) NULL,
  'lastnames' VARCHAR(45) NULL,
  'firstnames' VARCHAR(45) NULL,
  PRIMARY KEY ('id')
);

CREATE TABLE 'prisoners' (
  'id' INT NOT NULL,
  'regnumber' VARCHAR(10) NULL,
  'lastnames' VARCHAR(45) NULL,
  'firstnames' VARCHAR(45) NULL,
  PRIMARY KEY ('id')
);


SELECT
  encounters.encounterdate,
  officers.badgenumber,
  prisoners.regnumber,
  officers.firstnames,
  officers.lastnames,
  prisoners.firstnames,
  prisoners.lastnames,

FROM
  encounters
  INNER JOIN prisoners ON encounters.prisoners_id = prisoners.id
  INNER JOIN officers ON encounters.officers_id = officers.id
WHERE
  officers.badgenumber = "b503"