ER 继承建模
供应农场可以有运输文件。如果存在,它可以是以下两种类型之一:内部或外部。两个文档共享一些共同的数据,但具有不同的专业领域。
我想以面向对象的方式对此进行建模,如下所示: 替代文本 http://www.arsmaior.com/tmp/mod1.png
文档表,两个doc_*_id其中一个为null,另一个是与表对应的外键。
这与公共数据冗余的其他模式相反: 替代文本 http://www.arsmaior.com/tmp/mod2.png
我我正在尝试发现这两种方法的优缺点。
在这两种情况下,我如何选择了解所有内部文档?我们有一种互斥的外键,JOIN 并不是那么简单。
第一种方法完全是垃圾吗?
A supply farm can have a transportation document. If present it can be one of two types: internal or external. Both documents share some common data, but have different specialized fields.
I though of modeling this in a OO-ish fashion like this:
alt text http://www.arsmaior.com/tmp/mod1.png
In the document table, one of the two doc_*_id is null, the other is the foreign key with the corresponding table.
That is opposed to the other schema where the common data is redundant:
alt text http://www.arsmaior.com/tmp/mod2.png
I'm trying to discover pros&cons of both approaches.
How do I SELECT to know all the internal docs in both cases? We have a sort of mutually exclusive foreign keys, the JOINs are not so trivial.
Is the first approach completely junky?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
经典 ER 建模不包括外键,您的问题的要点围绕外键如何工作。我认为你真正做的是关系建模,即使你使用的是 ER 图。
在关系建模方面,还有第三种方法来建模继承。也就是说,专用表使用与通用表相同的 ID。那么doc_internal表的ID字段既是doc_internal表的主键,也是引用supply_farm表的外键。 doc_external 表也是如此。
Supply_farm 表中的 ID 字段既是 Supply_farm 表的主键,也是引用 doc_internal 或 doc_external 表的外键(具体取决于)。连接神奇地将正确的数据组合在一起。
设置此功能需要一些编程,但这是非常值得的。
有关更多详细信息,我建议您谷歌“泛化专业化关系建模”。网络上有一些关于这个主题的优秀文章。
Classical ER modeling doesn't include foreign keys, and the gist of your question revolves around how the foreign keys are going to work. I think that what you are really doing is relational modeling, even though you are using ER diagrams.
In terms of relational modeling, there is a third way to model inheritance. That is to use the same ID for the specialized tables as is used for the generalized table. Then the ID field of the doc_internal table is both the primary key for the doc_internal table and also a foreign key referencing the supply_farm table. Ditto for the doc_external table.
The ID field in the supply_farm table is both the primary key of the supply_farm table and also a foreign key that references either the doc_internal or the doc_external table, depending. The joins magically get the right data together.
It takes a little programming to set this up, but it's well worth it.
For more details I suggest you google "generalization specialization relational modeling". There are some excellent articles on this subject out there on the web.
这两种方法都是正确的,它们的使用完全取决于用例、您想要存储的数据类型和数量以及您主要想要触发的查询类型。当继承层次结构复杂时,您还可以考虑将这两种策略结合起来。
我认为首选第一种方法的一个用例是当您想要搜索所有文档时,例如,基于描述或任何公共字段。
此文档(尽管特定于 Hibernate)可以提供对不同继承建模策略的更多见解。
Both approaches are correct and their usage will totally depend on the use cases, the kind and volume of data you want to store and the type of queries you want to mostly fire. You can also think of combining these two strategies when the inheritance hierarchies are complex.
One use case where the first approach would be preferred I think is when you want to search through all the documents, for example, based on description or any common field.
This document (although specific to hibernate) can provide a little more insight on different inheritance modelling strategies.
如果我理解正确,那么供应场对应于 0 或 1 个文档,它始终是内部文档或外部文档(绝不会同时是两者)。
如果是这样,那么为什么不只使用单个表,如下所示:
If I have understood this correctly, then supply farm corresponds to either 0 or 1 documents, which is always either an internal or external document (never both).
If so, then why not just use a single table, like so: