与时间属性有关的设计数据库

Z时代
2024-01-10
分类：问答

我想设计一个数据库，描述如下：每个产品在一个时间点只有一个状态。但是，产品的状态可能会在其生命周期内发生变化。如何设计产品和状态之间的关系，以便在当前时间轻松查询所有具有特定状态的产品？另外，有人可以给我一些与设计时间有关的设计数据库的详细信息吗？谢谢你的帮助

回答：

这是一个满足您指定要求的模型。

对于不熟悉关系建模标准的人，请

。

归一化为5NF; 没有重复的栏；没有更新异常，没有Null。

当产品的状态更改时，只需将具有当前DateTime的行插入ProductStatus中。无需触摸先前的行（这是正确的，并且保持真实）。无需解释报告工具（您的应用程序除外）的虚拟值。

DateTime是产品置于该状态的实际日期时间；如果您愿意，则使用“发件人”。“ To”很容易得出：它是产品的下一行（DateTime>“ From”）的DateTime；如果不存在，则该值为当前的DateTime（使用ISNULL）。

第一个模型已经完成；（ProductId，DateTime）足以为主键提供唯一性。但是，由于您要求在某些查询条件下提高速度，因此我们可以在物理级别上增强模型，并提供：

索引（我们已经有了PK索引，因此我们将首先对其进行增强，然后再添加第二个索引）以支持涵盖的查询（基于{ProductId | DateTime | Status}的任何排列的查询都可以由索引提供，而无需转到数据行）。这会将Status :: ProductStatus关系从“非标识”（虚线）更改为“标识类型”（实线）。

选择PK安排的依据是，大多数查询都是基于Product⇢DateTime⇢Status的时间序列。

提供第二个索引以提高基于状态的查询速度。

在替代安排中，这是相反的；即，我们主要希望所有产品的当前状态。

在ProductStatus的所有格式中，辅助索引（不是PK）中的DateTime列为DESCending；最近是第一次。

我已提供您要求的讨论。当然，您需要尝试使用合理大小的数据集并做出自己的决定。如果这里有您不明白的地方，请询问，我会继续进行。

回答：

报告当前状态为2的所有产品

SELECT  ProductId,
        Description
    FROM  Product       p,
          ProductStatus ps
    WHERE p.ProductId = ps.ProductId  -- Join
    AND   StatusCode  = 2             -- Request
    AND   DateTime    = (             -- Current Status on the left ...
        SELECT MAX(DateTime)          -- Current Status row for outer Product
            FROM  ProductStatus ps_inner
            WHERE p.ProductId = ps_inner.ProductId
            )

ProductId 被索引，领导上校，双方

DateTime 在索引中，涵盖查询选项中的第二列

StatusCode 被索引，涵盖查询选项中的第三列

由于StatusCode索引中的数字是DESCending，因此只需要一次访存就可以满足内部查询

对于一个查询，同时需要这些行；它们靠得很近（由于分类索引）；由于行尺寸短，几乎总是在同一页上。

这是普通的SQL，是一个子查询，它利用SQL引擎的强大功能进行关系集处理。这是

，没有更快的方法，而其他任何方法都会更慢。任何报告工具都只需单击几下，无需键入即可生成此代码。

DateTimeFrom和DateTimeTo等列是严重错误。让我们按重要性排序。

这是一个严重的标准化错误。“ DateTimeTo”很容易从下一行的单个DateTime派生；因此是多余的，重复的列。
- 精度并没有提高：可以通过DataType（DATE，DATETIME，SMALLDATETIME）轻松解决。您显示的秒数，毫秒或纳秒数是一个商业决定；它与存储的数据无关。
- 实现DateTo列与下一行的DateTime是100％重复的。这将占用。对于一张大桌子，那将是不必要的浪费。

鉴于该行很短，因此每次访问都需要来读取表。

和（或者换一种说法，只有一半多的行会适应任何给定的缓存空间）。

通过引入重复的列，您已经引入了发生错误的可能性（现在可以通过两种方式派生该值：从重复的DateTimeTo列或下一行的DateTimeFrom）。

这也是一个。当您更新任何DateTimeFrom为Update时，必须获取前一行的DateTimeTo（因为关闭时没什么大不了的）和Updated（因为这是可以避免的附加动词，所以很重要）。

“ Shorter”和“编码快捷方式”无关紧要，SQL是一种繁琐的数据操作语言，但是 SQL就是我们所拥有的 （只需处理）。不能编写子查询的任何人实际上都不应进行编码。为简化次要编码“难点”而复制列的任何人实际上都不应为数据库建模。

请注意，如果保留了最高阶规则（归一化），则将消除整个较低阶问题集。

在编写简单的SQL时遇到“困难”或“痛苦”的任何人都将无法执行其工作功能。通常，开发人员考虑集合，而关系数据库是 面向集合的模型 。

对于上面的查询，我们需要当前日期时间；由于ProductStatus是按时间顺序排列的一组产品状态，因此我们只需要属于该产品集的最新状态或MAX（DateTime）。

现在让我们从 集合的 角度来看一些所谓的“难点” 。有关每个产品处于特定状态的持续时间的报告：DateTimeFrom是一个可用列，并定义了水平截止线，一个子集（我们可以排除较早的行）；DateTimeTo是产品状态子集的最早集合。
SELECT ProductId,
Description,
[DateFrom] = DateTime,
[DateTo] = (
SELECT MIN(DateTime) – earliest in subset
FROM ProductStatus ps_inner
WHERE p.ProductId = ps_inner.ProductId – our Product
AND ps_inner.DateTime > ps.DateTime – defines subset, cutoff
)
FROM Product p,
ProductStatus ps
WHERE p.ProductId = ps.ProductId
AND StatusCode = 2 – Request