如何在数据库中存储自定义用户字段
我正在尝试找到一个教程来指导我了解基于用户的自定义字段如何工作。就像在调查网站中一样，它们允许用户创建自定义字段并存储它们，更重要的是存储通过这些字段输入的数据。
我正在寻找一些描述如何在数据库上完成此操作的内容。我在寻找一种方法时遇到了一些麻烦，一旦用户将其提取到 Excel/cvs 文件，就不会永远检索这些数据。
I am trying to find a tutorial that will guide me on how user based custom fields works. Like in surveys sites, where they let users create custom fields and store them and more importantly store the data entered through those fields.
I am looking for something that describes how this is done on database. I am having a little trouble finding a way that would not take forever retrieving this data once the users whats to extract it to excel/cvs files.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Entity-Attribute-Value 模型通常用于在关系中处理这种情况数据库。快速搜索“EAV 模型”将产生比您不知如何处理的更多信息。
The Entity-Attribute-Value model is normally used to handle this scenario in a relational database. A quick search for "EAV model" will yield more info than you'll know what to do with.
第六范式是实现这一点的正式方法。对所有表使用 3NF，对需要在不更改 DDL 的情况下添加列的一两个表使用 6NF。谨慎使用。
EAV是6NF的私生子。这意味着，这样做并撰写相关文章的人并没有对 6NF 有正式的理解，因此常常会创造出怪物。
当然，你必须保留良好的标准：使用数据类型；声明性引用完整性（外键）；等等。不要放弃那些放弃获得任何东西的人。拼命逃离任何告诉你必须放弃他们的人。
6NF/EAV速度非常快，不妨碍使用服务器的集合处理能力。再次，如果有人告诉您必须使用逐行处理或游标，或者您无法轻松地从行构建列，请远离任何人。如果您有具体问题，请再次发帖。
这需要超越SQL当前的能力（控制、DDL）；为了以受控的方式做到这一点并避免创建难以维护的怪物，您需要一个小目录来包含元数据。如果你聪明的话，你可以用它生成查询所需的 SQL，从而消除大量的手工劳动。
周围有很多错误信息，一些有“代表”的人毫无头绪。为了在技术上取得成功，我们需要准确的信息，而不是谣言和散布恐惧。您可能对最近的一篇文章感兴趣，其中我尝试 直接设置第二个。
Sixth Normal Form is the formal way to implement this. Go with 3NF for all tables, and 6NF for the one or two tables that you need to add columns without DDL changes. Use sparingly.
EAV is the bastard son of 6NF. What that means is, people who do it, and write about it, do not have a formal understanding of 6NF, so often the create monstrosities.
Of course, you must retain good standards: use datatypes; Declarative Referential Integrity (Foreign Keys); etc. Do not give those up obtain anything. Run like hell away from anyone telling you you have to give them up.
6NF/EAV is very fast, there is no hindrance to using the set-processing capability of the server. Again, run like hell away from anyone who tells you that you have to use row-by-row processing or cursors or that you cannot build the columns from the rows easily. Post again if you have specific problems.
This requires going beyond the current capability (controls, DDL) of SQL; in order to do that in a controlled fashion and avoid creating unmaintainable monsters, you need a small catalogue, to contain the meta-data. If you are clever, you can use it generate the SQL reruired for querying, and thus eliminate a lot of otherwise manual labour.
There is a lot of misinformation around, and some people with "rep" are clueless. In order to succeed technically, we need accurate info, not myths and fear mongering. You may be interested in a recent post in which I tried to set the recond straight.
我们为此使用 3 个表，每个表需要支持用户定义的字段。
例如，如果您想将其应用到 SURVEY 表，您可以创建：
SURVEY_ATTRIBUTE 表为每个自定义属性存储一条记录。
SURVEY_ATTRIBUTE_VALUE 表存储实际分配给调查的属性。因此，如果某个属性不适用于某个服务，则不会存储任何内容。
SURVEY_ATTRIBUTE_CHOICE 表存储“LIST”类型属性的所有允许选择。
SURVEY_ATTRIBUTE 表中的 SurveyAttributeType 字段用于描述属性的类型。我们只使用少量允许的类型，例如 CHAR、DATE、NUMBER、LIST。根据该值，我们的应用程序知道如何处理 SurveyAttributeValue 字段中存储的值。当然，您可以进一步将其形式化以允许更广泛的范围，指定最大字段长度等......，这一切都取决于您希望给予最终用户的自由程度。我们尝试使其尽可能简单，因为我们的目标受众不是数据库管理员，而是最终用户，他们通常不关心字段长度等。
您还可以选择跳过 SURVEY_ATTRIBUTE_CHOICE 表，并将允许的值存储在 SURVEY_ATTRIBUTE 字段的 XML 字符串中。这将取决于您要在应用程序中实现的方式。
We use 3 tables for this per table where we need to support user defined fields.
So for instance, if you want to apply this to your SURVEY table, you could create:
The SURVEY_ATTRIBUTE table stores one record per custom attribute.
The SURVEY_ATTRIBUTE_VALUE table stores the attributes that are actually assigned to surveys. So if an attribute does not apply to a servey, nothing is stored.
The SURVEY_ATTRIBUTE_CHOICE table stores all allowable choices for attributes of type 'LIST'.
The SurveyAttributeType field in the SURVEY_ATTRIBUTE table is used to describe the type of the attribute. We only use a small number of allowable types like CHAR, DATE, NUMBER, LIST. Depending on that value, our application knows what to do with the value stored in the SurveyAttributeValue field. You could of course formalize this further to allow a broader range, specify maximum fieldlengths etc..., it all depends on the level of freedom you wish to give your end-user. We try to keep it as simple as possible for our target audience is not database administrators but end-users, they usually don't care about fieldlengths and such.
You could also choose to skip the SURVEY_ATTRIBUTE_CHOICE table and store your allowable values in an XML string in the SURVEY_ATTRIBUTE field. That will depend on the way you are going to implement in your application.