问题描述
我需要存储一组实体，其中有几个专门的版本.它们有一些共同的属性，但专门的属性包含特定于该实体的属性.
I need to store a set of entities, of which there are several specialized versions. They have some common properties, but the specialized ones contain properties specific for that entity.
数据存储是一个关系型 DBMS，这里不做讨论:-) 具体来说，它是 Microsoft SQL Server 2005.
The data store is a relational DBMS, and this is not for discussion :-) Specifically, it is the Microsoft SQL Server 2005.
我可以轻松地为通用属性创建一个表，然后为每个专用版本创建一个表.但是，很可能以后必须将新实体添加到解决方案中，而且我不想同时维护对象模型和数据库架构.
I could easily create a table for the common properties and then a table for each of the specialized versions. However, it is likely that new entities will have to be added to the solution later and I don't want to maintain both an object model and a database schema.
另一个想法是创建一个表
reading(<common properties>, extended_properties)
并使 extended_properties 字段成为扩展属性的某种序列化.我在想 JSON 或 XML.我很可能会使用 ORM 框架，但我还没有决定.无论哪种方式，来自 reading 的专门实体的对象表示都可以公开一个字典 {extended_property_name, value}，其中包含来自 extended_properties 字段.
and have the extended_propertiesfield be some kind of serialization of the extended properties. I was thinking either JSON or XML. I will most likely be using an ORM framework, but I haven't decided yet. Either way, the object representation of a specialized entity from the reading could expose a dictionary {extended_property_name, value} containing the parsed key/value pairs from the extended_properties field.
从此http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx 我收集了 XML 字段，结合这些字段的模式，在 DBMS 中给出了类型化 XML 的概念.此外，涉及 extended_properties 字段中的 XML 内容的查询也可以考虑这些.
From this http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx I gather that XML fields, combined with schemas for these, give the notion of typed XML inside the DBMS. Also, queries involving the XML contents in the extended_propertiesfield can take these into account, too.
反馈我的解决方案建议，主要是关于阅读表和扩展属性的序列化.
Feedback on my solution suggestions, primarily the one with the reading table and serialization of the extended properties.
此外，我意识到这是关系 DBMS 与基于键/值的存储相比的局限性之一.但是，肯定必须有一些建模技术来适应这一点.
Also, I realize this is one of the limitations of relational DBMS' compared to key/value based stores. However, there surely must be some modelling techniques to accommodate this.
非常感谢任何反馈！
推荐答案
Anders，不要放弃任何完整性或硬度，例如类型安全.
Anders, do not give up any integrity or hardness, eg type safety.
(回复来了).
@安德斯.不，完全没有，子类型很好(问题是您使用哪种形式以及有哪些缺点/优点).不要放弃任何力量或完整性或类型安全或检查或 DRI.您选择的表单将需要额外的检查和一些代码(取决于您的平台).
@Anders. No, not at all, subtyping is fine (the question is which form you use and what are the dis/advantages). Do not give up any strength or Integrity or type safety or checks or DRI. The form you choose will demand additional Checks and maybe a bit of code (depends on your platform).
这个话题经常出现，但求知者的视野总是狭隘；我不断从不变的集合中做出相同的陈述(一个子集).这个想法是评估所有选项.所以我正在写一个文档.不幸的是，它需要更长的时间.可能有 4 页.不准备发帖了.但是图表已经完成，我认为你在球上，你可以马上使用它.
This subject is coming up frequently, but the seeker always has a narrow perspective; I keep making the same statements (a subset) from an unchanging set. The idea is to evaluate all the options. So I am writing a doc. Unfortunately it is taking longer. Maybe 4 pages. Not ready to post. But the diagrams are finished, I think you are on the ball, and you can use it right away.
警告:仅限经验丰富的项目施工工程师
道路不适合大篷车或 Eek 系数高的读者
链接到 ▶Document Under Construction 中的四种替代数据模型◀.为地板上的混乱道歉；我会尽快清理.
Link to ▶Four Alternative Data Models◀ in Document Under Construction. Apologies for the mess on the floor; I will clean up soon.
▶IDEF1X 符号链接◀ 适用于不熟悉关系数据库建模标准的任何人.
▶Link to IDEF1X Notation◀ for anyone who is unfamiliar with the Standard for modelling Relational databases.
它们都是关系型的，完全完整.
They are all Relational, with full integrity.
6NF 选项.Relational today (SQL) 不提供对 6NF 的支持；它没有禁止它，它只是不提供 5NF➔6NF 结构.因此你需要建立一个小的目录，有些人称之为元数据".实际上，它只是标准 SQL 目录(sys 表)的扩展.所需的控制级别在每个选项中建模.
The 6NF options. Relational today (SQL) does not provide support for 6NF; it does not disallow it, it just does not provide the 5NF➔6NF structures. Therefore you need to build a small catalogue, what some people call "metadata". Really, it is just an extension of the standard SQL catalogue (sys tables). The level of control required is modelled in each option.
基本上正确完成 EAV，具有完全控制和完整性(类型安全、声明性引用完整性等)，而不是通常的混乱.
Essentially EAV done properly, with full control and integrity (type safety, Declarative Referential Integrity, etc) rather than the mess it usually is.
您可能对这些相关问题/答案感兴趣(特别是查看数据模型):
You may be interested in these related question/answers (in particular, look at the Data Models):
... 这样，我们可以轻松获取与给定的特定类型实例关联的评论"行.这是这样做的方式，还是我以后会后悔那个决定?我们还缺少其他任何模式吗?
不确定你的意思.注释、注释、地址最终会在许多表中使用(列驻留)，因此正确的方法是对它们进行规范化；提供一张评论表；从任何需要它的表中引用.这是一个通用的评论表.它在 Product(超类型)中使用，因为您声明了 any Product.它可以很容易地用于某些 Product 子类型，而不能用于其他子类型；在这种情况下，FK 将在所述产品子类型中.
Not sure what you mean. Comments, Notes, Addresses, end up being used (columns resident in) in many tables, so the correct method is to Normalise them; provide One Table for Comment; that is referenced from any table that requires it. Here is a generic Comment table. It is used in Product (the supertype) because you stated any Product. It can just as easily be used in some of the Product subtypes, and not others; in which case the FK will be in said Product Subtypes.
您的产品 5NF/子类型示例中的 ProductType 表的用途是什么?它是否包含与每个专用产品(例如 ProductCPU)对应的行?我假设它表明基础产品是哪个专业.
(Small critical mistake in the diagram, corrected.)
(Do evaluate how ProductType changes, exactly what role it plays, for each of the four Data Models.)
泛化-专业化"都是笨拙的面向对象术语；无需越界，也无需了解 Relational 30 年来的能力.如果你稍微了解一下 Relational，你就会拥有全部的力量；否则，您只能使用非常有限的面向对象方法来处理所有事情(Ambler 和 Fowler 需要回答很多问题).请阅读这篇文章，从 12 月 11 日开始.关系数据库模型实体，而不是对象；不是课程.
"Generalisation-specialisation" is all mumbo jumbo, OO terminology; without crossing the line and learning what Relational has been capable of for 30 years. If you learn a little about Relational, you will have the full power; otherwise you are limited to the very limited OO approach to everything (Ambler and Fowler have a lot to answer for). Please read this post, from 11 Dec 10 onwards. Relational databases model Entities, not objects; not classes.
例如，在添加新产品时，您需要提供可以添加的产品类型的下拉选项.根据这个选择，可以推导出数据放在哪些表中.对吗?我很抱歉谈论应用程序代码，但我只需要正确看待它
是的.以及接下来要提供什么页面(带字段)，供用户输入数据.
Yes. And what page (with fields) to provide next, for the user to enter data.
谈论将使用 Rdb 的应用程序代码没问题，他们像夫妻(不是丈夫和奴隶)一样走到一起.
No problem talking about the app code that will use the Rdb, they go together like husband and wife (not husband and slave).
对于您的 OO 类，在完成 Rdb 建模后，将类树映射到 Rdb，独立于将使用它的任何应用程序.不是反过来.并且不依赖于一个应用程序.
For your OO classes, map the Class tree to the Rdb, once you have finished modelling the Rdb, independent of any app that will use it. Not the other way around. And not dependent on one app.
忘记持久化"，它有很多问题(丢失更新；损坏的数据完整性；有问题的调试；大量争用等).Rdb 的所有更新都应该在事务中，符合 ACID，可用 30 年，但 Fowler 和 Ambler 还没有读到它.通常这意味着预先存储一个过程.
Forget about "persisting", it has many problems (Lost Updates; damaged data integrity; problematic debugging; massive contention; etc). All updates to the Rdb should be in Transactions, with ACID compliance, available for 30 years, but Fowler and Ambler have not read about it yet. Usually that means one stored proc pre xact.
判别式是我们之前建立的类型表的 FK.它表示基类型遵循哪个 子类型.但是判别表具体包含哪些内容呢?
从数据模型中看不清楚吗?ProductType CHAR(1) 或 (2).名称 Char(30).
Is that not clear from the data model ? ProducType CHAR(1) or (2). Name Char(30).
可能是一个显示友好的文本，说明用于 UI 目的的类型，
是的，其中包括控制、约束等，在编码或报告时消除歧义.
Yes, among other things, such as the control, contraint, etc, elimination of ambiguity when coding or reporting.
但它是否也包含包含特定类型的确切表名?
没有.这将有点过于物理，无法放入数据中.原则上不允许.
No. That would be a little too physical to be placed in data. Disallowed on principle.
但这不是必须的.
假设我对 ID = 1 的产品感兴趣.它有一个判别式，表明它是一个 ProductCPU.您将如何从您的应用代码中检索此 ProductCPU?
如果您采用提供的模型，并将其(所有表)正确地实现为类，这将很容易，等等.您请求的示例将不使用视图(用于列表和更通用的用途).伪代码是:
That will be easy if you take the provided model, and implement it (all the tables) as classes, correctly, etc. The example you request will not use Views (which are for lists, and more generic use). The pseudo-code would be:
给定ProductId(子类型未知，因此您不应坐在特定于子类型的窗口中)，仅加载Product超类型基于判别器Product.ProductType，设置指标等，并加载适用的子类型，ProductCPU之一；产品记忆；产品磁盘；ProductTape;等
given theProductId(Subtype unknown, therefore your should not be sitting a a Subtype-specific window), load theProductsupertype onlybased on the DiscriminatorProduct.ProductType, set indicators, etc, and load the applicable subtype, one ofProductCPU; ProductMemory; ProductDisk; ProductTape; etc.
我见过(但不同意)面向给定 ProductId 一次性加载所有子类型的 OO 方法:一个子类型有效；其余无效.代码仍然必须将自身约束为基于 Product.ProductType 的 Product 的有效类.
I have seen (and do not agree with) OO methods that load all subtypes for the given ProductId at once: one subtype is valid; and the rest are invalid. The code still has to constrain itself to the valid class for the Product based on Product.ProductType.
或者，例如.在上下文所在的位置，用户坐在特定于子类型的窗口中，例如.ProductCPU，使用该类设置，并请求 ProductId xxx.然后使用 ProductCPU 视图.如果返回零行，则不存在.
Alternately, eg. where the context is, the user is sitting in a Subtype-specific window, eg. ProductCPU, with that class set up, and requests ProductId xxx. Then use the ProductCPU View. If it returns zero rows, it does not exist.
可能有ProductDiskxxx，但没有ProductCPUxxx.您如何处理这个问题，无论您是否指出有 Product`xxx 但它不是 CPU，这取决于应用要求.
There may be aProductDiskxxx, but not aProductCPUxxx. How you handle that, whether you indicate there is a Product`xxx but it isn't a CPU, or not, that depends on the app requirements.
对于应用填充网格的列表，不考虑ProductId，使用视图(每个视图)加载每个网格.那个SQL是基于join的，不需要引用ProductType.
For lists, where the app fills in a grid, without regard to the ProductId, use the views (one each) to load each grid. That SQL is based on the join, and does not need to refer to ProductType.
这篇关于可以支持特殊属性的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！