I want to build a system where an admin can create forms with the program and users enter data to those forms. Also, admins should be able to enter any field names to their forms, even selectable multiple choice questions. Only answer I can come up with is creating a huge text block for every form type generated and storing form fields, names, choices, etc serializing them from an array or object. I would keep the users answers in serialized arrays in another text block, assigning each one with their positions in arrays.
我想构建一个系统，管理员可以使用该程序创建表单，用户可以向这些表单输入数据。此外，管理员应该能够在其表单中输入任何字段名称，甚至是可选择的多项选择题。我能想到的唯一答案是为每个生成的表单类型创建一个巨大的文本块，并存储表单字段，名称，选项等，从数组或对象序列化它们。我会将用户在序列化数组中的答案保留在另一个文本块中，为每个文本块分配它们的位置。
Is there a better approach for this problem?
这个问题有更好的方法吗？
3 个解决方案
#1
4
An EAV data model may suit your purposes here. Just be aware that you'll pay for the flexibility with some downsides, such as lack of referential integrity and long queries to retrieve data (every attribute you want to return requires another JOIN).
EAV数据模型可能适合您的目的。请注意，您将为某些缺点付出灵活性，例如缺乏参照完整性和长查询以检索数据（您要返回的每个属性都需要另一个JOIN）。
#2
2
There are many ways you can go on this. Since you're working with a relational database, one option is to look at the Entity-Attribute-Value model. In effect, this approach switches your data structure from a predefined schema (columns) into rows. There are many downsides to this flexibility, and you often end up building a bunch of views to present some of this "rotated" data in the form of a more traditional table.
您可以通过多种方式进行此操作。由于您正在使用关系数据库，因此可以选择实体 - 属性 - 值模型。实际上，这种方法将您的数据结构从预定义的模式（列）切换为行。这种灵活性有许多缺点，您通常最终会构建一组视图，以更传统的表格形式呈现一些“旋转”数据。
Some relational databases provide noSQL-type features, so you might find a solution down that road. PostgreSQL, for example, has the hstore type, which allows you to store sets of key-value pairs in a single value. I realize you didn't mention PostgreSQL in your question, but just so you're aware of what's out there, they're also working on a native JSON datatype and function suite for 9.2. Combined with functional indexes, this could provide some interesting relational/noSQL hybrid possibilities without something like Redis or MongoDB.
某些关系数据库提供了无类型的SQL功能，因此您可以找到解决方案。例如，PostgreSQL具有hstore类型，允许您将键值对集合存储在单个值中。我意识到你没有在你的问题中提到PostgreSQL，但只是因为你知道那里有什么，他们也在研究9.2的原生JSON数据类型和功能套件。结合功能索引，这可以提供一些有趣的关系/ noSQL混合可能性，而不需要像Redis或MongoDB这样的东西。
#3
0
Could you create a table in the database called something like "Fields"? Each field has a type and text. Then also have a table called options, which contains different a text field for the options as well as a fk pointing back to the "fields" table. There could also be a form table which the fields table has an fk to. In the code for the app you may then look at a field and determine if you need to fetch an option. This is a relational database approach. Some of the object databases like Mongo may handle this much better.
你能在数据库中创建一个名为“Fields”的表吗？每个字段都有一个类型和文本。然后还有一个名为options的表，它包含选项的不同文本字段以及指向“fields”表的fk。还可能有一个表格表，字段表有一个fk到。在应用程序的代码中，您可以查看一个字段并确定是否需要获取选项。这是一种关系数据库方法。像Mongo这样的一些对象数据库可以更好地处理这个问题。
To summarize:
总结一下：
Table: Form Column: Form_ID PK
表：表单列：Form_ID PK
Table: Field Column: Field_ID PK Column: Text Column: Type Column: Form_ID (FK)
表：字段列：Field_ID PK列：文本列：类型列：Form_ID（FK）
Table: Options Column: Option_ID Column: Text Column: Field_ID (FK)
表：选项列：Option_ID列：文本列：Field_ID（FK）
#1
4
An EAV data model may suit your purposes here. Just be aware that you'll pay for the flexibility with some downsides, such as lack of referential integrity and long queries to retrieve data (every attribute you want to return requires another JOIN).
EAV数据模型可能适合您的目的。请注意，您将为某些缺点付出灵活性，例如缺乏参照完整性和长查询以检索数据（您要返回的每个属性都需要另一个JOIN）。
#2
2
There are many ways you can go on this. Since you're working with a relational database, one option is to look at the Entity-Attribute-Value model. In effect, this approach switches your data structure from a predefined schema (columns) into rows. There are many downsides to this flexibility, and you often end up building a bunch of views to present some of this "rotated" data in the form of a more traditional table.
您可以通过多种方式进行此操作。由于您正在使用关系数据库，因此可以选择实体 - 属性 - 值模型。实际上，这种方法将您的数据结构从预定义的模式（列）切换为行。这种灵活性有许多缺点，您通常最终会构建一组视图，以更传统的表格形式呈现一些“旋转”数据。
Some relational databases provide noSQL-type features, so you might find a solution down that road. PostgreSQL, for example, has the hstore type, which allows you to store sets of key-value pairs in a single value. I realize you didn't mention PostgreSQL in your question, but just so you're aware of what's out there, they're also working on a native JSON datatype and function suite for 9.2. Combined with functional indexes, this could provide some interesting relational/noSQL hybrid possibilities without something like Redis or MongoDB.
某些关系数据库提供了无类型的SQL功能，因此您可以找到解决方案。例如，PostgreSQL具有hstore类型，允许您将键值对集合存储在单个值中。我意识到你没有在你的问题中提到PostgreSQL，但只是因为你知道那里有什么，他们也在研究9.2的原生JSON数据类型和功能套件。结合功能索引，这可以提供一些有趣的关系/ noSQL混合可能性，而不需要像Redis或MongoDB这样的东西。
#3
0
Could you create a table in the database called something like "Fields"? Each field has a type and text. Then also have a table called options, which contains different a text field for the options as well as a fk pointing back to the "fields" table. There could also be a form table which the fields table has an fk to. In the code for the app you may then look at a field and determine if you need to fetch an option. This is a relational database approach. Some of the object databases like Mongo may handle this much better.
你能在数据库中创建一个名为“Fields”的表吗？每个字段都有一个类型和文本。然后还有一个名为options的表，它包含选项的不同文本字段以及指向“fields”表的fk。还可能有一个表格表，字段表有一个fk到。在应用程序的代码中，您可以查看一个字段并确定是否需要获取选项。这是一种关系数据库方法。像Mongo这样的一些对象数据库可以更好地处理这个问题。
To summarize:
总结一下：
Table: Form Column: Form_ID PK
表：表单列：Form_ID PK
Table: Field Column: Field_ID PK Column: Text Column: Type Column: Form_ID (FK)
表：字段列：Field_ID PK列：文本列：类型列：Form_ID（FK）
Table: Options Column: Option_ID Column: Text Column: Field_ID (FK)
表：选项列：Option_ID列：文本列：Field_ID（FK）