Может ли база данных быть слишком реляционной?

Question

У меня есть вопрос по поводу дизайна базы данных. Я спрашиваю здесь, потому что я энтузиаст / любитель, а не "профессионал", поэтому я не хочу публиковать его в обмене стеками "Администраторы баз данных", специально предназначенном для профессионалов. Я надеюсь, что это правильное место.

Я хотел бы создать систему для отслеживания встреч между заключенными и сотрудниками исправительных учреждений. Пытаясь спроектировать реляционную базу данных, я придумал следующие пять таблиц:

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`)
);

со следующими отношениями:

Эта установка помешала бы мне хранить все имена и фамилии дважды (один раз для офицеров и один раз для заключенных), но я не уверен, как получить имя / фамилию конкретного офицера И имя / фамилию конкретного заключенного для данной встречи. В идеале я хотел бы использовать инструкцию SELECT, чтобы получить запись, которая выглядит как одна из строк ниже:

Я могу использовать внутренние объединения, чтобы получить имя и фамилию любого из офицеров:

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"

или заключенный:

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"

но я не могу понять, возможно ли получить ОБА для данного столкновения, используя только одну инструкцию SELECT.

Конечно, я мог бы сделать это с помощью хранимой процедуры / функции и пары SELECT, но мне интересно узнать, есть ли более простой способ сделать это с помощью одного SELECT. Или это просто плохой способ создать базу данных?

Спасибо за вашу помощь.

Answer 1

Ваш пример с именами и фамилиями интересен. Что это добавляет к картине? Какое значение имеет положить имена в отдельной таблице? Если вы берете всех людей с именем "Марк", что у вас тогда? Это что-нибудь значит? Что если вы возьмете всех людей с фамилией "Смит" - они все принадлежат к одной семье, клану, расе или национальности? Все ли они живут в одном городе? Возможно нет!

Если имя "Смит" означает, что у вас каштановые волосы, голубые глаза и, как правило, стиль ковбойской одежды, это имело бы смысл. Здесь возможна аналогия: если изделие относится к типу телевизора, оно, вероятно, имеет пульт дистанционного управления, требуется кабель питания и т.д.

Если имена имеют размер около 1 МБ, имеет смысл использовать идентификатор. Это сэкономит много дубликатов. Но с обычными именами это не имеет большого значения, в то время как это приводит к большей сложности. Мало того, что база данных более сложна, но и код, соединяющий базу данных, также более сложен, и, поскольку нет никакой выгоды, вам лучше избегать этого.

Поскольку нет ничего значащего или полезного, связанного с именем или фамилией, не помещайте это в отдельную таблицу.

Вы упомянули в комментарии, что это может сэкономить 240 КБ, если 20 000 человек имеют одинаковые имена. Это большую часть времени не огромная экономия. Конечно, бывают ситуации, когда это имеет смысл, но в современном мире с большим количеством дискового пространства экономия 1 МБ данных совершенно неактуальна.

Когда вы упоминаете сохранение данных, мне интересно, будет ли это стоить производительности или нет. Понятия не имею, но если мы говорим числами, а это Facebook и мы запрашиваем миллионы имен в день, мне интересно, что будет более эффективным, потому что это означает дополнительное объединение. Это должно быть проверено, и на основании этого теста вы решите.

Если у вас слишком много запросов или очень ограниченное пространство, и вы обнаружили, что использование дополнительной таблицы экономит время - сделайте это!

Answer 2

В ответ на ваш вопрос да или нет, да. Некоторые структуры лучше денормализовать до соответствующей степени. Что уместно? Это зависит, конечно.
В вашем конкретном примере ответ, предложенный AEonEX, является правильным не из-за денормализации, а потому, что эти имена должным образом являются атрибутами названных ими сущностей. Может быть целесообразно экспортировать состояние из адреса, потому что это состояние реально, существует, и все ссылки на него на самом деле ссылаются на одну и ту же реальную вещь. Имя не так уж и много.

Answer 3

Нет необходимости в таблицах firstnames и lastnames . Вместо того, чтобы хранить firstname и lastname в officers и prisoners вместо firstnames_id и 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"