使用哪一个？数据库中的 EAV 或 Blob？
我目前正在努力重新设计我们应用程序的数据系统。基本上，它的设计目的是让人们可以添加他们想要的所有自定义字段，而只有一些常量/始终存在的字段。
我们当前的设计给我们带来了很多维护问题。我们所做的是动态地（在运行时）为每个字段向数据库添加一列。我们必须有一个元表和其他东西来维护所有这些动态列。
现在我们在看EAV，但看起来也好不了多少。基本上，我们有许多不同类型的字段，因此会有一个 StringValues、IntegerValues 等表......这使事情变得更糟。
我想知道在数据库中使用 JSON 或 XML blob 是否可能是更好的解决方案，特别是因为在大多数用例中，当我们从这些表中检索任何内容时，我们需要整行。问题是我们还需要能够为这些数据创建报告。没有任何解决方案真正使自定义查询看起来很容易。当报告运行时，在这样的 blob 数据库中进行搜索肯定会成为性能噩梦。
每个“行”需要有大约 15 到 100 个（可能更多）与其关联的属性/列。
我们使用 SQL Server 2008，与数据库连接的应用程序是 C# Web 应用程序（即 ASP.Net）。
你怎么认为？使用 EAV 或 blob 或完全使用其他东西？ （另外，是的，我知道像 MongoDB 这样的无模式数据库在这里会很棒，但我无法说服我的老板使用它）
I am currently working to rework the data system of our application. Basically, it is designed so that people can add all the custom fields they want, with only a few constant/always-there fields.
Our current design is giving us plenty of maintenance problems. What we do is dynamically(at runtime) add a column to the database for each field. We have to have a meta table and other cruft to maintain all of these dynamic columns.
Now we are looking at EAV, but it doesn't seem much better. Basically, we have many different types of fields, so there would be a StringValues, IntegerValues, etc table... which makes things that much worse.
I am wondering if using JSON or XML blobs in the database may be a better solution, specifically because in most use cases, when we retrieve anything out of these tables, we need the entire row. The problems is that we need to be able to create reports for this data as well.. No solution really makes custom queries look easy. And searching across such a blob database will surely be a performance nightmare when reports are ran.
Each "row" needs to have anywhere from about 15 to 100(possibly more) attributes/columns associated with it.
We are using SQL Server 2008 and our application interfacing with the database is a C# web application(so, ASP.Net).
what do you think? Use EAV or blobs or something else entirely? (Also, yes, I know a schema free database like MongoDB would be awesome here, but I can't convince my boss to use it)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
xml 数据类型怎么样？可以针对此类型进行高级查询。
我们使用 xml 类型并取得了良好的成功。我们在代码级别使用 linq 解析值来完成大部分繁重的工作。我们的架构有些固定，因此这可能不适合您。
What about the xml datatype? Advanced querying is possible against this type.
We've used the xml type with good success. We do most of our heavy lifting at the code level using linq to parse out values. Our schema is somewhat fixed, so that may not be an option for you.
SQL Server 的一项有趣功能是
sql_variant类型。它在 .NET 中得到完全支持并且非常易于使用。优点是您不需要创建 StringValue、IntValue 等列，只需创建一个可以包含所有简单类型的 Value 列。恕我直言，这种非常具体的类型有利于 EAV 选项。
但它有一些缺点（排序、不同的选择等）。因此，如果您想使用它，请确保您阅读了所有文档并了解其限制。
One interesting feature of SQL server is the
sql_varianttype. It's fully supported in .NET and quite easy to use. The advantages is you don't need to create StringValue, IntValue, etc... columns, just one Value column that can contain all the simple types.This very specific type favors the EAV option, IMHO.
It has some drawbacks though (sorting, distinct selects, etc...). So if you want to use it, make sure you read all the documentation and understand its limit.
使用 DataColumn0001、DataColumn0002 等连续名称创建一个包含已知列和“X”稀疏列的表。当有新列的定义时，只需重命名列并开始插入数据。稀疏列的最大优点是它是可索引的。
更多信息请访问此链接。
Create a table with your known columns and "X" sparse columns using a sequential name such as DataColumn0001, DataColumn0002, etc. When there is a definition for a new column just rename a column and start inserting data. The great advantage to the sparse column is it is indexable.
More info at this link.
您正在做的事情是愚蠢的，数据库不支持您的数据类型。您应该使用满足您需求的介质，其中包括 NoSQL 数据库，例如 RavenDB、MongoDB、DocumentDB、CouchBase 或 RDMBS 中的 Postgres 等。
您本质上是在以一种其设计目的之外的方式使用该工具，并且它专门试图限制您取得成功。 NoSQL 数据库解决方案经常使用 JSON 作为底层存储，因为 JSON 本质上是无模式的。想要添加属性吗？当然可以，想要添加整个子集合吗？当然继续。 NoSQL 数据库在一定程度上是专门为了消除 RDBMS 的严格模式要求而创建的。
2015 编辑：Postgres 现在原生支持 JSON。对于 RDBMS，这是一个可行的选择。我的答案仍然是正确的，您需要使用正确的工具来解决问题。这是一个多角持久性世界。
What you're doing is STUPID with a database that doesn't support your data type. You should work with a medium that meets your needs which include NoSQL databases such as RavenDB, MongoDB, DocumentDB, CouchBase or Postgres in RDMBS to name several.
You are inherently using the tool in a capacity it was neither designed for, and one it specifically attempts to limit you from achieving success. NoSQL database solutions frequently use JSON as an underlying storage because JSON is inherently schemaless. Want to add a property? Sure go ahead, want to add a whole sub collection? Sure go ahead. NoSQL databases were in part, created specifically to remove rigid schema requirements of RDBMS.
2015 Edit: Postgres now natively supports JSON. This is a viable option for RDBMS. My answer is still correct that you need to use the correct tool for the problem. It is a polygot persistence world.