如何最好地表示同时出现在主记录和明细记录中的属性？ [英] How to best represent an attribute that appears in both master and detail records?

问题描述

我有一个简单的主从关系，其中两个实体共享一个属性（恰好是时间戳，并且不是键的一部分。）详细记录（按其他属性）从上到下进行排序。业务规则是：（a）如果有明细记录，则将最上面的时间戳记应用于主记录； （b）否则，船长无论如何均须有时间戳记； （c）（很少）允许船长的时间戳不同于最顶层详细记录的时间戳。
最常见的情况是（a），我想避免在主数据和最详细信息中重复时间戳。您将如何设计这种情况？
非常感谢

I have a simple master-detail relation in which both entities share an attribute (which happens to be a timestamp, and is not part of the key.) The detail records are ranked (by some other attribute) from top to bottom. The business rule is: (a) If there are detail records, apply the timestamp of the top one to the master; (b) otherwise, the master must have a timestamp regardless; and (c) (rarely) permit the master to have a timestamp that differs from the that of the top detail records. The most common case is (a) and I'd like to avoid duplicating the timestamp in both the master and the top detail. How would you design for this situation? Many thanks

推荐答案

一个简单的设计就是拥有像这样的表

A straightforward design is to have tables like

master: [m] is a master & ...
master_dated: master [m] has differing date [d]
detail: "master [m] detail line [l] was on date [d] & ...

然后您可以重新组织它们，例如，用左连接代替前两个。如果您现在只有一个主从表，那么大概是从内部/自然联接中选择的，即细节和细节的聚集。

You can then reorganize these. Eg replace the first 2 by a left join of them. Something like those ought to be the design you come up with in the first place. If you now have a single master-detail table then presumably it's a select from an inner/natural join of the latter, detail & an aggregation of detail.

master-detail:
        (   master [m] has differing date [d]
        OR  master [m] has no differing date
        AND [d] = SELECT MAX(d) FROM detail WHERE [m]=m
        )
    AND [m] is a master & ...
    AND master [m] detail line [l] was on date [d] & ...

PS

PS规范化将表替换为的投影自然地回到它。从您的单个表迁移到其他表会减少某些冗余，但这不是规范化的，从单个表不是其他表的自然连接就可以看出。

PS Normalization replaces a table by projections of it that natural join back to it. Moving from your single table to these others reduces certain redundancy but it isn't normalization--evident from the single not being a natural join of the others.

这篇关于如何最好地表示同时出现在主记录和明细记录中的属性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！