我有一个关于对象视图中的自定义字段的体系结构问题。假设您有一个用户对象，其中包含一些基本信息，例如名字、姓氏……，所有客户都可以使用这些信息。
现在，我们经常收到客户的问题，要求添加几个适合其域的自定义字段。我们现在的解决方案是存储键值对的 xml 数据列。到目前为止这还不错，但现在我们必须找到一个更具架构性的解决方案。
例如，现在，客户需要一个下拉菜单，可以在其中选择自定义字段的值。我们仍然可以将选定的值存储在 xml 数据列中，但是我们在哪里存储所有这些下拉值...
我知道在 sharepoint 中您还可以添加自定义字段，例如下拉列表，我想知道如何最好地处理这个问题。我想避免为客户创建自定义表，或者有一个包含 90 列的表（10 列基本列，然后为每个客户 10 列），...
你明白了，它应该是通用的并且能够处理各种问题将来。
我正在考虑的是一个表 UserConfiguration，其中每条记录都有一个客户的外键（我们数据库中的渠道），然后是一个字段名称列、一个字段类型列和一个值列。列值应该是 xml 类型列，因为对于下拉列表，我们需要添加多个值。此外，每个值都可以附加额外的数据（不仅仅是名称）。另一个问题是如何存储所选值。我不喜欢在我的数据库中使用 xml 的外键（在某处读到 Azure 无法很好地处理这一切）。您是否只存储值的名称（如果该值从 xml 中消失怎么办？）？
关于此类问题的任何文档、链接也很棒。我正在尝试找到一种设计模式来处理数据库中的此类问题。
I have an architectural question concerning custom fields in a view for an object. Let's say you have a User Object with some basic information like firstname, lastname, ... that can be used by all customers.
Now, often we get a question from a customer to add couple of custom fields typical for their domain. Our solution now is an xml data column where key value pairs are stored. This has been ok so far, but now we'll have to find a more architectural solution.
For instance, now, a customer wants a dropdown where it can select the value for its custom field. We could still store the selected value in the xml data column, but where do we store all those dropdown values...
I know that in sharepoint you can also add custom fields like dropdowns and I was wondering how to deal with this best. I want to avoid creating custom tables for customers, or having a table with 90 columns (10 basic and then 10 for each customer), ...
You get the idea, it should be generic and be able to deal with all sorts of problems in the future.
What I was thinking about is a Table UserConfiguration where each record has a Foreign Key to the Customer (Channel in our database), then a column FieldName, a column FieldType and a column Values. The column values should be an xml type column, because for a dropdown, we'll need to add multiple values. Also, each value can have extra data attached to it (not just a name). The other problem then is how to store the selected value. I don't like the idea of having foreign keys to xml in my database (read somewhere that Azure can't handle this all to well). Do you just store the name of the value (what if the value were to disappear out of the xml?)?
Any documentation, links on this kind of problems would also be great. I'm trying to find a design pattern that deals with this kind of problem in the database.
发布评论
评论（4）
我想分两部分回答你的问题：
1) 在数据库服务器中实现自定义字段
2) 将自定义字段限制为值的枚举
尽管 问题由@Simon引用，也许您正在寻找一些关于问题是什么以及为什么我们还没有解决这个问题的讨论。
正如 其他问题，没有完美的解决方案。
但这些好处/功能仍然需要在某个地方实现，因此应用程序通常要负责数据完整性和类型安全。
对于此类情况，人们创建了对象关系映射工具，尽管如此，Jeff阿特伍德 说，即使使用 ORM 也会产生比它解决的问题更多的问题。然而，您提到它“应该是通用的，并且能够处理未来的各种问题”——这让我认为 ORM 可能是您最好的选择。
所以，总结一下我的答案，这是一个已知的问题，有已知的解决方案，但没有一个是完全令人满意的（因为它太难了）。选择你的毒药。
回答（我认为是）你的问题的第二部分：
正如链接问题中提到的，您可以在中实现 Entity-Attribute-Value您的数据库用于自定义字段，然后添加一个额外的表来保存每个实体的合法值。那么，EAV 表的属性/值是属性值表的外键。
例如，
当然，这个解决方案并不完美或完整——它只是为了说明这个想法。例如，它使用 varchar，并且缺少
type列。另外，谁来决定每个属性的可能值是什么？用户可以随时更改这些吗？I want to answer your question in two parts:
1) Implementing custom fields in a database server
2) Restricting custom fields to an enumeration of values
Although common solutions to 1) are discussed in the question referenced by @Simon, maybe you are looking for a bit of discussion on what the problem is and why it hasn't been solved for us already.
As discussed in the other question, there's no perfect solution.
But these benefits/features still need to be implemented somewhere, and so often the application becomes responsible for data integrity and type safety.
For situations like these, people have created Object-Relation Mapping tools, although, as Jeff Atwood says, even using an ORM could create more problems than it solved. However, you mentioned that it 'should be generic and be able to deal with all sorts of problems in the future' -- this makes me think an ORM might be your best bet.
So, to sum up my answer, this is a known problem with known solutions, none of which are completely satisfactory (because it's so hard). Pick your poison.
To answer the second part of (what I think is) your question:
As mentioned in the linked question, you could implement Entity-Attribute-Value in your database for custom fields, and then add an extra table to hold the legal values for each entity. Then, the attribute/value of the EAV table is a foreign key into the attribute-value table.
For example,
Of course, this solution isn't perfect or complete -- it's only supposed to illustrate the idea. For instance, it uses varchars, and lacks a
typecolumn. Also, who gets to decide what the possible values for each attribute are? Can these be changed at any time by the user?我正在为客户做类似的事情。我创建了一个 JSON FieldType，它保存复杂对象的整个 JSON 流和一个包含 C# 模型类的 FQTN (FullQualifiedTypeName) 的字符串。
通过使用自定义新建、编辑和显示表单，我们确保自定义对象以正确的方式呈现，以获得最佳用户体验。
为了将字段从复杂的 C# 模型提升到 SharePoint 列表，我们构建了类似于 Microsoft 在 InfoPath 中所做的事情。用户可以从复杂 C# 类型中选择属性或元数据，这些类型将自动提升到托管 SharePoint 列表。
JSON 的一大优点是它比 XML 更小并且更容易在网络世界中使用。 （JavaScript...）
I'm doing something similar for a customer. I've create a JSON FieldType which holds the entire JSON stream of a complex object and a String containing the FQTN (FullQualifiedTypeName) of my C# model class.
By using custom New-, Edit- and Display-Forms we'd ensured that our custom objects are rendered the correct way for best user experience.
To promote fields from the complex C# model to the SharePoint list, we've build something like Microsoft did in InfoPath. Users are able to select Properties or MetaData from the Complex C# type, which will be automatically promoted to the hosting SharePoint list.
The big advantage of JSON is, that its smaller than XML and easier to work with in the web world. (JavaScript...)
当您让用户创建数据模型时，我建议您查看文档数据库或“NoSQL”，因为您正是想要这样来存储无模式数据结构。
另外，sharePoint 以您提到的方式存储元数据（10 列用于文本，5 列用于日期等）
也就是说，在我当前的项目中（锁定在 SharePoint 中，因此框架 3.5 + SQL Server 以及遵循的所有约束）我们使用有点类似结构如下：
我们的公式使用诸如 Increment:
INC([attribute1][attribute2], 6)之类的函数，这将为第 999 个实例生成类似 000999 的结果表单的属性 1 和属性 2 的组合值，存储为：其他“公式”（也称为任何重要的内容），例如条形码，存储为单个元数据值。在实际的实现中，我们会有这样的东西：
上面的值是一个只读属性，它决定我们是否应该从文本或日期获取值以及是否需要一些额外的转换。请注意，这里的数据库只是一个存储，我们在应用程序中保留所有领域的复杂性。
例如，我们还让客户决定哪个属性是表单标题，因此如果
firstname是表单标题，他们将设置一个跨越整个应用程序的内存参数，类似于Params.InMemory.TitleAttributeId = <用户定义的id>。我希望这能让您对类似场景的生产实现有一些了解。
When you let the users create the data models, I would recommend looking at an document database or 'NoSQL' since you want exactly that, to store schemaless data structures.
Also, sharePoint stores metadata the way you mentioned (10 columns for text, 5 for dates etc)
That said, in my current project (locked in SharePoint, so Framework 3.5 + SQL Server and all the constraints that follow) we use a somewhat similar structure as below:
Our formulas employ functions such as Increment:
INC([attribute1][attribute2], 6)and this would produce something like 000999 for the 999th instance of the combined values for attribute 1 and attribute 2 for a form, this is stored as:Other 'formulas' (aka anything non-trivial) such as barcodes are stored as single metadata values. In the actual implementation, we would have something like this:
Value above is a readonly property that decides whether we should get the value from Text or Date and if some additional transform is required. Note that the database here is merely a storage, we hold all the domain complexity in the application.
We also let our customer decide which attribute is the form title for example, so if
firstnameis the form title, they'll set an in-memory param that spans the entire application to be something likeParams.InMemory.TitleAttributeId = <user-defined-id>.I hope this gives you some insight on a production impl of a similar scenario.
这实际上更像是评论而不是答案，但我需要比 SO 允许的评论更多的空间，所以这里是：
我认为您的 UserConfiguration 表方法很好，并且建议仅抽象“类型”和“值”更多的设计部分：
由于您的应用程序需要验证用户输入，因此每个“类型”概念都将有一个关联的评估逻辑。显然，您可以抽象为数据的内容越多，就越容易保持代码的小型化。枚举列表是一个好的开始，但是如果您的“验证器”逻辑可以扩展来处理文本字符串和布尔逻辑表达式的模式匹配（例如描述/强制输入值的约束），那么您几乎可以表达任何“类型”您的应用程序可能需要按照（相对）简单的“原子”来处理输入，您可以自然地将其映射到数据库表。
存储用户指定的值时，您可以存储“原始”数据（例如 JSON 格式）和关联“类型”的外键，也可以添加一个查找/缓存系统，将整数分配给系统遇到的每个新值（例如，可以通过检查“原始”数据的哈希值来检查“新颖性”）。如果您期望大量数据重复（当然，在多选菜单的情况下），后一种方法显然可以更好地扩展。
This is really more of a comment than an answer, but I need more space than SO will allow for comments, so here 'tis:
I think your UserConfiguration table approach is good, and would suggest only abstracting the "type" and "value" pieces of your design a bit more:
Since your application will need to validate user input, each notion of "type" will have an associated piece of evaluation logic. Obviously the more of this you can abstract into data the easier it will be to keep your code small. Enumerated lists are a good start, but if your "validator" logic can be extended to handle pattern matching for text strings and Boolean logical expressions (e.g. to describe/enforce constraints on input values), then you can express pretty much any "type" of input that your application may need to handle in terms of (relatively) simple "atoms" that you can map naturally to DB tables.
When storing a user-specified value, you can either store the "raw" data (e.g. in JSON) and a foreign key to the associated "type", or you can add an lookup/cache system that assigns an integer to each new value that is encountered by the system ("novelty" can be checked by checking a hash of the "raw" data, for example). The latter approach obviously scales better if you're expecting lots of data duplication (which of course you would in the case of a multiple-choice menu).