在数据库中保存客户可配置数据的方法有哪些？
我正在寻找一些关于在关系数据库（在我的例子中为 SQL Server 2000）中保存客户可配置数据的方法的想法。
例如，假设您有一个标准订单输入应用程序，您的客户可以在其中输入他们想要购买的产品。除了对您和客户都重要的字段（itemID、成本等）之外，您还希望允许客户输入仅与他们相关的信息并为他们保留该信息（以便以后在报告或发票或其他内容上检索） 。您还希望这些“客户字段”的标签由客户配置。因此，一个客户可能有一个名为“发票编号”的字段，另一个客户可能有两个名为“发票#”和“发票日期”的字段等......
我可以想出几种方法来做到这一点。您可以有一个 customerfields 表，其中包含与每个交易相关的合理数量的 varchar 字段，然后是另一个表 clientcustomerfields，其中包含有关客户使用多少字段、字段名称等的元数据。或者，您可以使用 XML 来持久保存客户数据，因此您不必担心填写 X # 字段，您仍然需要一些表来描述客户元数据（可能通过 XSD）。
是否有做此类事情的标准方法？
I'm looking for some ideas on methods for persisting customer configurable data in a relational database (SQL Server 2000 in my case).
For example let's say you have a standard order entry application where your customer enters a product they want to buy. In addition to the fields that are important to both you and the customer (itemID, cost etc), you want to allow the client to enter information only relevant to them and persist it for them (for later retrieval on reports or invoices or whatever). You also want the labeling of these "customer fields" to be configured by the customer. So one customer might have a field called "Invoice Number" another customer might have 2 fields called "Invoice#" and "Invoice Date" etc...
I can think of a few ways to do this. You could have a customerfields table with some reasonable number of varchar fields related to each transaction and then another table clientcustomerfields which contains the meta data about how many fields a customer uses, what the field names are etc. Alternatively you could use XML to persist the customer data so you don't have to worry about filly up X # of fields, you'd still need some table to describe the customers meta data (maybe through an XSD).
Are there any standard ways of doing this type of thing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
您应该阅读最佳实践用于性能和可扩展性的语义数据建模。这正是链接中的白皮书所解决的问题。
You should read Best Practices for Semantic Data Modeling for Performance and Scalability. This is exactly the question addressed by the white paper in the link.
我会使用的一种策略：
customer_fields
- 字段 ID
- customer_id
- 字段名称
客户交易字段值
- 交易 ID
- 字段 ID
- 字段值
One strategy I'd use:
customer_fields
- field_id
- cusomer_id
- field_name
customer_transaction_field_values
- transaction_id
- field_id
- field_value
作为概括，我建议不要使用不透明的 XML blob 在关系数据库中存储面向字段的数据。
最好的解决方案是在应用程序中拥有一些“用户”字段和配置，以设置如何使用和呈现这些字段。如果字段是 varchar，则空字段的开销相当小，IIRC 每个字段大约 1 个字节。尽管这看起来不优雅并且字段数量有限，但它的查询和填充最简单，这使得它速度最快。一种选择是使配置与字段数量无关，只需运行脚本即可在需要时添加更多字段。
另一个选择是使用一个“编码”表来悬挂用户可配置字段的实体。它具有“实体 ID”、“字段类型”和“字段代码”列，其中“字段类型”列表示实际内容。特别的缺点是，它使查询变慢，因为它们必须多次连接该表。
我实际上已经看到（1）和（2）在同一个系统上使用。供应商最初从 (2) 开始，但后来发现它很麻烦，应用程序的后续子系统转而使用 (1)。这种方法的改变是基于痛苦的经历。
对 XML blob 的主要打击是它们不是数据库模式中的一等公民。 DBMS 本身无法强制 Blob 上的引用完整性，它无法对 Blob 中的各个列进行索引，并且从 Blob 中查询数据更加复杂，并且报告工具可能不支持。另外，blob的内容对于系统数据字典来说是完全不透明的。任何试图从系统中提取数据的人都依赖于应用程序的文档来深入了解内容。
As a generalisation I would recommend against using opaque XML blobs to store field-oriented data in a relational database.
The best solution is to have some 'user' fields and configuration within the application to set up how these fields are used and presented. If the fields are varchars the overhead for empty fields is fairly minimal, IIRC about 1 byte per field. Although this looks inelegant and has a finite number of fields, it is the simplest to query and populate which makes it the fastest. One option would be to make the configuration agnostic to the number of fields and simply run a script to add a few more fields if you need them.
Another option is to have a 'coding' table hanging off entities which user-configurable fields. It has 'entity ID', 'field type' and 'field code' columns where the 'field type' column denotes the actual content. The particular disadvantage is that it makes queries slower as they have to potentially join against this table multiple times.
I've actually seen both (1) and (2) in use on the same system. The vendor originally started with (2) but then found it to be a pain in the arse and subsequent subsystems on the application went to using (1). This change in approach was borne out of bitter experience.
The principal strike against XML blobs is that they are not first class citizens in the database schema. The DBMS cannot enforce referential integrity on the blob by itself, it cannot index individual columns within the blob and querying the data from the blob is more complex and may not be supported by reporting tools. In addition, the content of the blob is completely opaque to the system data dictionary. Anyone trying to extract the data back out of the system is dependent on the application's documentation to get any insight into the contents.
除了您自己的建议之外，另一种方法是查看 ASP.net 中的 Profile Provider 系统（假设有 MS 技术堆栈）。您可以扩展 ProfileBase 以包含 2 个代表用户定义的键的数组和另一个代表相应值的数组。此时，SqlProfileProvider 将处理此类的存储和检索，并创建 ProfileBase 对象的实现。最终，这类似于您尝试在 Web 应用程序项目而不是网站项目（使用不同的构建管理器）中使用 ProfileProvider 系统。
In addition to your own suggestions, another way is to look at the Profile Provider system in ASP.net (Assuming a MS tech stack on this). You can extend the ProfileBase to include 2 arrays representing your user defined keys and another for the corresponding values. At that point, the SqlProfileProvider will handle the storage and retrieval of such and create your implementation of the ProfileBase object. Ultimately, this would be similar to if you were trying to use the ProfileProvider system in a Web Application project and not a Web Site project (which use different build managers).
我过去曾这样做过并使用了用户定义字段的概念。我将为基本类型创建四个表：
然后我将有一个描述字段及其类型的表：
CustomField - id - int, customer_id - int （链接到客户表），fieldType - 'UDFCharacter 、UDFNumber 等、名称 - varchar 和其他元信息
对字段的响应位于 UDF 表中。字段根据 CustomField 表显示在页面上。我们的系统可能更复杂并且需要更多的表，但这似乎可以工作。
I have done this in the past and used the concept of user defined fields. I would create four tables for the basic types:
I would then have a table that described the fields along with their type:
CustomField - id - int, customer_id - int (linked to customer table), fieldType - 'UDFCharacter, UDFNumber, etc', name - varchar, and other meta info
The responses to the fields go in the UDF tables. The fields get displayed on the page based on the CustomField table. Our system was may more complex and required more tbales but this seems like it would work.