哪一个使用？数据库中的EAV或Blob？ [英] Which one to use? EAV or Blobs in the database?

问题描述

我正在努力修改我们应用程序的数据系统。基本上，它的设计使人们可以添加他们想要的所有自定义字段，只有几个常量/永远在那里的字段。

我们目前的设计给了我们很多维护问题。我们所做的是动态（在运行时）为每个字段向数据库添加一列。我们必须有一个元表和其他cruft来维护所有这些动态列。

现在我们正在看EAV，但似乎并没有好多了。基本上，我们有很多不同类型的字段，所以会有一个StringValues，IntegerValues等表，这使得事情变得更糟。

我想知道如果在数据库中使用JSON或XML blob可能是一个更好的解决方案，特别是因为在大多数用例中，当我们从这些表中检索到任何东西时，我们需要整行。问题是我们需要能够为这些数据创建报告。没有解决方案真的使自定义查询看起来很容易。搜索这样一个blob数据库肯定会是报告运行时的表现噩梦。

每个行都需要与其关联的约15到100个（可能更多的）属性/列。

我们正在使用SQL Server 2008，我们与数据库的应用程序接口是一个C＃Web应用程序（所以ASP.Net）。

你觉得怎么样？使用EAV或Blob或其他完全？ （也是的，我知道一个免费的数据库，如MongoDB在这里会很棒，但我不能说服我的老板使用它）

解决方案

xml数据类型怎么样？针对此类型，可以进行高级查询。

我们已经使用了很好的xml类型。我们在代码级使用linq来解析大部分的大部分代码。我们的模式有些固定，所以这可能不是您的选择。

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)

解决方案

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.

这篇关于哪一个使用？数据库中的EAV或Blob？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！