时间数据库建模和规范化 [英] temporal database modeling and normalisation

问题描述

应将时间数据库的日期存储在一个或两个表中吗？如果不违反规范化的规定吗？

  PERSON1 DATE11 DATE21 INFO11 INFO21 DEPRECATED 
 PERSON2 DATE21 DATE22 INFO21 INFO22 CURRENT 
 PERSON1 DATE31 DATE32 INFO31 INFO32 CURRENT 
  

DATE1和DATE2列表示INFO1和INFO2对于DATE1到DATE2之间的时间段。如果DATE<今天，事实已被弃用，不应在用户界面中再次显示，但为了历史目的不应删除。例如INFO11和INFO21现在已被弃用。

我应该拆分这个表吗？是否应该将状态（不推荐使用或当前）存储在表格中？

为了进一步澄清问题，弃用是业务使用的术语，如果您更喜欢不当前，问题不是语义，它不是关于sql查询，我只是想知道哪个设计违反或最适合规范化规则（我知道规范化并不总是这样的方式，这不是我的问题）。 / p>

解决方案

我想知道哪个设计违反规范化规则

取决于你想要遵循哪一套规范化规则。

第一个也是最可能违反正常表格的，而在日期的书中，它违反了第一个NF，是持有当前信息的行的结束日期（抽象出未来信息的可能性）：如果使该属性为空，则违反1NF。

违反BCNF可能会因为您选择密钥而发生（因为在非时间数据库设计中也是如此 - 时间方面在这里没有区别）。 Wrt键的选择：如果您使用单独的开始和结束日期（和SQL类型的叶子，您没有其他选择），那么很可能你应该声明两个键：一个包括开始日期，一个包括结束日期。

另一个设计问题是多个数据列。这个问题在时间数据和关系模型中有很大的讨论：如果INFO1和INFO2可以彼此独立地改变，那么最好分解你的表来保存一个属性，以避免爆炸行数，否则可能会发生，如果您必须创建一个新的完整行每次行中的单个属性更改。在这种情况下，您所设计的设计构成了违反第六章正常形式的规定，在时间数据和关系模型中定义了（正常形式）。

Should dates for a temporal database stored in one or 2 tables ? If one doesn't this violate normalisation ?

PERSON1 DATE11 DATE21 INFO11 INFO21 DEPRECATED
PERSON2 DATE21 DATE22 INFO21 INFO22 CURRENT
PERSON1 DATE31 DATE32 INFO31 INFO32 CURRENT

DATE1 and DATE2 Columns indicate that INFO1 and INFO2 are true for the period between DATE1 and DATE2. If DATE < TODAY, the facts are deprecated and shouldn't show any more in the user interface but they shouldn't be deleted for historical purpose. For example INFO11 and INFO21 are now deprecated.

Should I split this table ? Should I store the state (deprecated or current) in the table ?

To clarify the question further more, Deprecated is the term used by the Business, if you prefer "not current", the problem is not semantic, it's not about sql queries either, I just want to know which design violates or best suits Normalisation rules (I know normalisation is not always the way to go, that is not my question either).

解决方案

"I want to know which design violates Normalisation rules"

Depends on which set of normalisation rules you want to go by.

The first and most likely violation of normal forms, and in Date's book it is a violation of first NF, is your end-dates in the rows that hold "current" information (making abstraction of the possibility of future-dated information) : you violate 1NF if you make that attribute nullable.

Violations of BCNF may obviously occur as a consequence of your choice of keys (as it is the case in nontemporal database designs too - the temporal aspect makes no difference here). Wrt "choice of keys" : if you use separate start- and end-dates (and SQL kind of leaves you no other choice), then most likely you should declare TWO keys : one that includes the start date, and one that includes the end-date.

Another design issue is the multiple data columns. This issue is discussed quite at large in "Temporal Data and the Relational Model" : if INFO1 and INFO2 can change independently of one another, it might be better to decompose your tables to hold just one attribute, in order to avoid an "explosion of rows count" that might otherwise occur if you have to create a new complete row every time one single attribute in the row changes. In that case, your design as you gave it constitutes a violation of SIXTH normal form, as (that normal form is) defined in "Temporal Data and the Relational Model".

这篇关于时间数据库建模和规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！