关键概念:规范化

逐渐成为一种垂死的艺术,这是数据库优化的关键

尽管大多数这些“关键概念”文章都是相对较高的水平和理论上的,但这篇文章旨在具有高度的针对性。 之前,我写过关于现代开发人员中核心数据库管理技能似乎正在逐渐消失的文章。 因此,本文是我尝试解决的问题。

让我们来看一个例子,因为这始终是学习事物的最佳方法。 我们有下表。 单击右下角的“查看原始图像”,以查看其固定器上部所有内容的全部内容。

有问题的表

这是已申请请假的公司员工的列表。

一览无余。 这正是用户期望看到的。 但是,这不是我们应该如何存储数据的方式。 原因有很多。 其中一部分只是效率。 所有这些都是非常明确和罗word的。 它可以缩短。 请注意,保存的实际结构与从中得到的查询和数据集输出之间存在巨大差异。 我们只在这里做前者。 后者对读者来说是极为重要的练习; 知道如何正确地联接表对于使用数据库绝对是基础。

我们该如何解决?

我们要做的第一件事是在此表上添加一个递增的主键。 这将使表上的所有操作更加明确和准确。 它还可以(显着)提高查找任何一项的性能,使我们的查询更简单等。

现在,我们可以继续进行实际标准化。

规范化是所谓“规范形式”的累积过程。 这些遵循一系列编号规则。 为了满足每个规则,它还必须满足以前的规则。 潜入便更容易理解。

第一范式:1NF-所有数据必须是原子的

第一个范式,又名1NF,表示所有数据都必须是原子的。

让我们再次抓住桌子。

每一项数据都是一项。 第6行此处包含禁止。 杰夫和苏珊都批准了此人的假期。 这似乎只是一种风格,但事实并非如此。 如果可以/需要多个人批准此事,那么这里的数据根本就没有正确的格式。 如果批准休假项目实际上是一对多关系,则实际上需要在单独的表中。 我们可以很容易地说明一个例子:

这似乎是一个琐碎的表,但是通过将它与请假表结合起来,我们可以获得更清晰的画面。 如果您想知道为什么不将批准日期也放在这里,那么您绝对正确。 我将其留在旧表中,因为我想稍后进行演示。 实际上,日期也将100%属于此处。

请注意,“批准者”列在我们的表中消失了,我们不需要(实际上,也不能)从请假表中引用它。 我们不需要证明这一点,只需将其上方的表可视化,只剩下一列即可。 惊人。

旁:表关系

值得一提,讨论表之间的交互方式。 对此有趣的一点是,有两个与数据库有关的数字。 一,无限。 或更准确地说,一个或多个。

本质上,在构造互连的表时,需要考虑这种关系的性质。 它们分为三种类型。 一对一,一对多和多对多。

一对一关系是最简单的。 一对一意味着该表实际上可能位于同一表中,而不会损害结构,它只会添加其字段。 一对一的示例是标准的“查找表”,其中我们采用定期出现的文本字符串(例如status或category),并将其替换为category_id。 这是一对一的关系,只是一种更简洁,更简洁的数据存储方式。

一对多关系更为复杂。 客户可能有很多订单。 我们通过在订单表上放置“ customer_id”来解决这个问题。 然后,我们可以加入这种关系。 请注意,我们不会更改实际的客户记录。

最后的关系是多对多。 继续前面的示例,一个订单可能有很多产品。 您不能在产品上下订单,它可能属于数千个订单。 而且您不能将产品订购,因为它可以有任意数量的产品。 解决方案是第三张桌子。 该表存储客户和订单之间的关系。 它包含一个ID字段,然后是order_id和product_id。 这种模式可以将任意数量的订单连接到任意数量的产品。 该表正确地称为关联表 。 或经常错误地称为透视表 。 主要由Laravel用户使用。

第二范式2NF-没有非素数属性不依赖于表的任何候选键的适当子集。

耶稣基督。 好的,这是不用术语就更容易理解的事情之一。 让我们再看看我们的桌子。

我们具有用于状态,部门和人员姓名等内容的文本字符串。

数据库从根本上存储两件事:(显然)数据和数据之间的关系。 在这种情况下,很容易将此处的数据视为“遗留物”。 但是实际上这里的数据可以拆分。 这里的核心实际数据是用户,状态和部门。 那么,请假表本身就是这些事物之间的关系。

理想情况下,所有这些信息都可以分别放入部门表,用户表和状态表中。 我们还可以将相同的用户表而不是“ Susan”重新用于批准者。

该表已满足“第一范式”,因为每个条目都有一个项目。

我们大大降低了复杂性,并消除了一些名称的歧义。 由于拼写仅存储在一个地方,因此不再发生拼写错误等。 从Department更改为Dept并没有意义,只是节省了一些空间,以便您一次可以看到更多内容。

这里的目标是使用键而不是数据。

第三范式3NF-R的每个非素数属性非传递地依赖于R的每个键。

好的,以上显然是个玩笑吗? 不,从字面上看这是规则。 实际上,这比您想象的要有意义,并且非常类似于上一步,只是下一步。

向上滚动一点,看看桌子。 很干净

但是看看那个部门领域。 瞧,那实际上不属于休假。 属于用户! 这就是所谓的“传递依赖”。 该密钥属于用户中的某项,然后属于部门。 它通过用户属于一个单独的表。

通过将其放在此处的表中,我们可以产生歧义的可能性-如果将部门设置为休假项目的一件事,而将用户数据设置为其他数据,这意味着什么呢? 痛苦和痛苦是答案。 病痛。 我们可以从那里得到它,仅从用户那里使用它,现在我们满足了2NF的要求。 还请注意,我们正在缩减很多桌子。 读取所有数字可能不太好,但至少是一致的。

怎么办? 完成了吗

现在,我们有了一个非常干净的系统! 我们完成了,对吗?

不完全的。 老实说,我不确定这是否适合NF系统,因为措辞太神秘了,但是我们可以并且应该为此做些事情。 如果您在上方看,将会看到一些可能造成歧义和错误的地方。 他们并不总是很容易看到。

请注意,例如,“持续时间”字段和“结束”字段在它们存储的内容上是重叠的。 如果您知道起点和终点,则可以确定持续时间。 如果您知道开始时间和持续时间,则可以确定结束时间。 通过同时存储这两个数据,您将获得无法匹配的数据。

您永远不应存储可以计算或创建的数据。 如果要存储名字姓氏 ,那么也要存储全名 ,这很麻烦。 在很大程度上这是一个问题,因为它可能导致错误和不一致,而且它们并不总是很明显。 例如,项目3的持续时间并不是真正的7天。 实际上是八天。 它只是被误解了。 同时放置持续时间结束会留下不匹配的数据。 哪个是对的?

我们可以去除工期列并消除这种歧义。 但这并不是唯一的歧义。

如果我们的状态为2- 已批准,但没有批准者条目怎么办? 如果我们有一个批准者条目但未更新状态会怎样? 也许……也许我们实际上根本不需要状态列及其关联的表? 在这种情况下,最好将“ approved on”列的存在作为决定是否批准某件事的决定性因素。 请注意,这就是为什么我将其单独放置而不是像应有的那样将其放入批准表的原因。

具有可能潜在冲突的冗余数据的这种趋势是软件设计中最难克服的事情之一。 很直观 如果您有收据,请存储总数! 但是这样做的话,您存储的数据可能会得到更好的计算,而不是仅仅存储在数据库中。 总数和计数通常是通过这种方式完成的。

在您的领域中进行思考并弄清楚您是否确实需要存储此数据,或者是否真的可以从其他地方计算出数据,总是值得的。

非正规化–等待,什么? 你为什么要倒退?

非规范化是撤消所有辛勤工作的过程。 更具体地说,是存储数据的过程,否则该数据将被分离并计算为表格。

这有充分的理由。 例如,在Reddit之类的东西上, 可以通过将“ upvote”表作为聚合函数进行计数,然后减去downvote的计数来计算出每次投票的票数。 但是这样做的过程将是可怕的。

以论坛为例。 您可能有一个线程表。 该线程可能链接到帖子。 当显示主题列表时,您还希望显示帖子数和最新帖子的日期。 这样做的规范化方法是拥有一个线程表,然后通过该thread_id加入posts表,对线程id进行GROUP_BY并计数帖子,同时还选择最近的帖子发布日期。 但是,这样做会使线程选择查询从一个非常简单,快速的查询变为一个非常复杂且极其昂贵的查询。 只是简单地加入添加注释的过程,并利用这个机会来增加comment_count字段和last_updated字段并将其命名为一天,这要容易得多。

类似地,尽管上面我们讨论了由于将订单中的产品价格总和存储在订单表中而导致的数据重复和潜在冲突,但这通常是完全合理的事情。 例如,更容易显示或报告订单。

像正确的规范化一样 ,适当的非规范化对于性能至关重要。 数据完整性的轻微风险通常是微不足道的,可管理的,并且为性能和可维护性优势付出了宝贵的牺牲。

接下来是什么?

数据库设计有很多东西。 例如,这些都没有涵盖索引的关键主题。 它没有涵盖外键,可为空的字段,某些引擎的适用性,格式和字段长度的正确选择。 它没有涵盖任何有用的约定或如何处理拐点(例如,复数还是单数)。 它根本没有讨论如何处理使用联接,子查询和聚合函数重组所有数据所需的SQL。

但这是一个开始。 尽管存在诸如NoSQL数据库之类的工具之类的选项,并且与上述内容无关,但出于某种原因,诸如Postgres或MySQL之类的RDBMS仍然是数据持久性的标准。 正确使用此技术对于提供性能和数据完整性的应用程序至关重要,无论其顶层是什么。