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

Question

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

Answer 1

这是一个满足您陈述的要求的模型。

Link to Time Series Data Model

Link to IDEF1X Notation 适用于不熟悉关系建模标准的人员。

标准化为5NF；没有重复的栏；没有更新异常，没有Null。

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

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

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

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

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

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

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

在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已索引，领先col，双方

索引中的

DateTime，涵盖查询选项

中的第二列

StatusCode已索引，涵盖查询选项

中的第三列

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

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

这是普通的SQL，是一个子查询，它利用SQL引擎的强大功能进行关系集处理。这是 一种正确的方法，没有更快的方法，而其他任何方法都将更慢。任何报告工具都只需单击几下，无需键入即可生成此代码。

ProductStatus中的两个日期

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

这是一个严重的规范化错误。 “DateTimeTo”很容易从下一行的单个DateTime派生；因此是多余的，重复的列。

精度并没有提高:可以通过DataType(DATE，DATETIME，SMALLDATETIME)轻松解决。您显示的秒数，毫秒或纳秒是一个商业决定；它与存储的数据无关。

实现DateTo列与下一行的DateTime是100％重复的。这需要两倍于磁盘空间。对于一张大桌子，那将是不必要的浪费。

鉴于该行很短，因此每次访问时，您需要两倍的逻辑和物理I / O 才能读取该表。

和是缓存空间的两倍(或者换句话说，任何给定的缓存空间只能容纳一半的行)。

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

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

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

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

从集合的角度思考

在编写简单的SQL时遇到“困难”或遇到“痛苦”的任何人都会在执行其工作功能时受到损害。通常，开发人员在集合方面是而不是思维，而关系数据库是面向集合的模型。

对于上面的查询，我们需要当前的DateTime。由于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

关于获取下一行的思考是面向行的，而不是面向集合的处理。使用面向集合的数据库时发生崩溃。让优化器为您做所有这些思考。检查您的SHOWPLAN，优化效果最佳。

无法在集合中进行思考，因此仅限于仅编写单级查询，对于以下情况而言，这不是合理的理由:在数据库中实现大量复制和更新异常；浪费在线资源和磁盘空间；保证一半的性能。学习如何编写简单的SQL子查询以获取容易获得的数据要便宜得多。