数据库是否可以过于关系化？

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

您举的姓和名的例子很有趣。这给图片增添了什么？将名字放在单独的表中有什么价值？如果您将所有名字为“Marc”的人都列出，那么您会得到什么？这有什么意义吗？如果您将所有姓氏为“Smith”的人都列出，他们会不会都属于同一个家族、氏族、种族或国籍？他们都住在同一个城市吗？可能不是！

如果名字“Smith”意味着你有一头棕色的头发、一双蓝色的眼睛并且通常穿着牛仔风格的衣服，那么这就说得通了。这里可能的类比是：如果该物品是电视类型的产品，那么它可能有一个遥控器，需要电源线等。

如果名称大小为 1MB，使用 id 是有意义的。这将节省大量重复存储。但是使用普通名称并没有太大区别，反而会导致更多的复杂性。不仅数据库更复杂，而且连接到数据库的代码也更复杂，而且由于没有任何好处，你最好避免它。

由于与名字或姓氏没有任何有意义或有用的联系，因此请不要将其放在单独的表中。

您在评论中提到，当 20000 个人有相同的名字时，它可以节省 240KB。大多数情况下，这并不是很大的节省。当然，在某些情况下这样做确实有意义，但在现代世界中，磁盘空间很大，节省 1MB 的数据完全无关紧要。

当您提到保存数据时，我想知道这是否会降低性能。我不知道，但如果我们谈论的是数字，这是 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"