新产品属性的实体属性值 (EAV) 与 XML 列
我有一个现有的成熟架构，我们需要向其中添加一些新的产品属性。例如，我们有 Products.Flavor，现在需要添加新属性，例如重量、香味等。我不是继续扩大产品表，而是考虑其他几个选项。首先是一个新的 Attributes 表，它实际上是任意属性的属性包，以及一个 ProductsAttributes 表，用于存储特定产品属性的映射（和值）。正如我所理解的，这就是实体-属性-值 (EAV) 模式。另一个选项是向 Products 表添加一个名为 Attributes 的新列，该列的类型为 XML。在这里，我们可以任意向任何产品实例添加属性，而无需添加新表。
每种方法的优点/缺点是什么？我正在使用 SQL Server 2008 和 ASP.NET 4.0。
I have an existing, mature schema to which we need to add some new Product attributes. For example, we have Products.Flavor, and now need to add new attributes such as Weight, Fragrance, etc. Rather than continue to widen the Products table, I am considering a couple of other options. First is a new Attributes table, which will effectively be a property bag for arbitary attributes, and a ProductsAttributes table to store the mappings (and values) for a particular product's attributes. This is the Entity-Attribute-Value (EAV) pattern, as I've come to understand it. The other option is to add a new column to the Products table called Attributes, which is of type XML. Here, we can arbitrarily add attributes to any product instance without adding new tables.
What are the pros/cons to each approach? I'm using SQL Server 2008 and ASP.NET 4.0.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这是（恕我直言）经典的数据库设计问题之一。称之为“属性蠕变”，也许，因为一旦开始，总会有另一个属性或属性要添加。他们的关键决定是，是否使用数据库提供的基本工具（表和列）来结构化和格式化数据，将数据存储在数据库中，或者以其他方式存储数据（XML 和名称/值对）是最常见的替代品）。简而言之，如果您以 DBMS 系统支持的形式以外的形式存储数据，那么您将失去 DBMS 系统管理、维护和使用该数据的能力。如果您只需要将其存储为“blob 数据”（将其全部转储，将其全部抽出），那么这并不是什么大问题，但是一旦您开始必须按此数据进行查找、排序或过滤，它就会得到非常丑非常快。
话虽如此，我确实对名称/值对和 XML 有强烈的看法，但遗憾的是，没有一个是积极的。如果您确实必须以这种方式存储数据，并且是的，这可能是一个完全有效的业务/设计决策，那么我建议您仔细研究如何您需要在数据库中存储的数据将来会被使用和访问。根据每种方法的使用方式权衡其优缺点，然后选择最容易管理和维护的方法。 （不要选择最容易实现的，你支持它的时间将比你编写它的时间长得多。）
（它很长，但是 “RLH”文章是名称/值对疯狂的典型例子。）
（哦，如果你是使用它，请查看 SQL Server 2008 的“稀疏列”选项，这听起来不像您所需要的，但您永远不知道。）
This is (imho) one of the classic database design issues. Call it "attribute creep", perhaps, as once you start, there will always be another attribute or property to add. They key decision is, do you store the data within the database using the basic tools provided by the database (tables and columns) to structure and format the data, or do you store the data in some other fashion (XML and name/value pairs being the most common alternates). Simply put, if you store the data in a form other than that supported by the DBMS system, then you lose the power of the DBMS system to manage, maintain, and work with that data. This is not much of a problem if you only need to store it as "blob data" (dump it all in, pump it all out), but once you start have to seek, sort, or filter by this data, it can get very ugly very fast.
With that said, I do have strong opinions on name/value pairs and XML, but alas, none are positive. If you do have to store your data this way, and yes it can be an entirely valid business/design decision, then I would recommend looking long and hard on how the data you need to store in the database will be used and accessed in the future. Weight the pros and cons of each methodology in light of how it will be used, and pick the once that's easiest to manage and maintain. (Don't pick the one that's easiest to implement, you'll be supporting it for a lot longer than you'll be writing it.)
(It's long, but the "RLH" essay is a classic example of name/value pairs run amok.)
(Oh, and if you're using it, look into SQL Server 2008's "Sparse Columns" option. Doesn't sound like what you need, but you never know.)