我想设计一个数据库,描述如下：
每个产品在一个时间点只有一个状态.但是,产品的状态可能会在其生命周期内发生变化.我如何设计产品和状态之间的关系,可以很容易地查询当前特定状态的所有产品？另外,有谁可以请给我一些关于设计数据库的深入细节,这些数据库与上述问题的持续时间有关？谢谢你的帮助
Link to Time Series Data Model
Link to IDEF1X Notation适用于那些不熟悉关系建模标准的人.
>归一化到5NF;没有重复的列;没有更新异常,没有空.
>当产品的状态发生变化时,只需使用当前的DateTime将一行插入到ProductStatus中.无需触摸前一行(这是真的,并保持为真).没有虚拟值报告工具(除了您的应用程序)必须解释.
> DateTime是产品放置在该状态中的实际DateTime;如果你愿意的话,“从”. “To”很容易导出：它是Product的下一个(DateTime>“From”)行的DateTime;在它不存在的地方,该值是当前的DateTime(使用ISNULL).
第一个模型完成; (ProductId,DateTime)足以为主键提供唯一性.但是,由于您要求某些查询条件的速度,我们可以在物理级别增强模型,并提供：
>一个索引(我们已经有了PK索引,所以我们将在添加第二个索引之前首先增强它)以支持覆盖的查询(基于{ProductId | DateTime | Status}的任何排列的索引可以由索引提供,没有不得不去数据行).这将Status :: ProductStatus关系从Non-Identifying(折线)更改为Identifying类型(实线).
>根据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,双方
>涵盖查询选项中的索引,第二列中的日期时间
> StatusCode是索引的,涵盖查询选项中的第3列
>由于索引中的StatusCode是DESCending,因此只需要一次提取即可满足内部查询
>对于一个查询,行是同时需要的;它们很接近(由于Clstered Index);由于行的大小,几乎总是在同一页面上.
这是普通的sql,一个子查询,使用sql引擎的强大功能,即Relational set处理.这是一种正确的方法,没有什么比这更快,任何其他方法都会更慢.任何报告工具只需点击几下即可生成此代码,无需输入.
ProductStatus中的两个日期
DateTimeFrom和DateTimeTo等列是严重错误.让我们按重要性排序.
>这是一个严重的标准化错误. “DateTimeTo”很容易从下一行的单个DateTime派生出来;因此它是多余的,一个重复的列.
>精度没有进入：它可以通过DataType(DATE,DATETIME,SMALLDATETIME)轻松解决.无论是显示少于秒,微秒还是纳秒,都是商业决策;它与存储的数据无关.
>实现DateTo列是100％重复(下一行的DateTime).这需要两倍的磁盘空间.对于大桌子来说,这将是非常不必要的浪费.
>鉴于它是一个短行,每次访问时,您需要两倍的逻辑和物理I / O来读取表.
>两倍的缓存空间(换句话说,只有一半的行适合任何给定的缓存空间).
>通过引入重复列,您已经引入了错误的可能性(现在可以通过两种方式导出值：从重复的DateTimeTo列或下一行的DateTimeFrom).
>这也是一个更新异常.当您更新任何DateTimeFrom已更新时,必须获取前一行的DateTimeTo(因为它已关闭而没什么大不了的)和更新(大不了,因为它是一个可以避免的附加动词).
>“Shorter”和“编码快捷方式”无关,sql是一种繁琐的数据操作语言,但sql就是我们所拥有的(Just Deal It It).任何无法对子查询进行编码的人都不应该编码.任何复制列以减轻次要编码“难度”的人实际上都不应该建模数据库.
请注意,如果维持最高阶规则(标准化),则会消除整组低阶问题.
从集合的角度思考
>在编写简单的sql时遇到“困难”或遇到“痛苦”的任何人在执行其工作职能时都会瘫痪.通常,开发人员不考虑集合,而关系数据库是面向集合的模型.
>对于上面的查询,我们需要Current 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>在获取下一行方面的思考是面向行的,而不是面向集合的处理.使用面向集合的数据库时会造成严重影响.让Optimiser为您做所有想法.检查你的SHOWPLAN,这可以很好地优化.
>无法集中思考,因此仅限于编写单级查询,这不是一个合理的理由：在数据库中实现大量复制和更新异常;浪费在线资源和磁盘空间;保证一半的性能.学习如何编写简单的sql子查询以获取易于派生的数据要便宜得多.