我有一个应用程序，允许用户创建不同的形式（调查），然后填充。 （所以它代替纸）。

I have an application that lets users create different forms (surveys) and then fill them. (so its a substitute for paper).

下面是我使用的应用程序当前模型：

Here's the current model i'm using in the app:

 Table 1)
+-------------------------+
|      SURVEYS TABLE      |   
+----+------+-------------+
| ID | name | description |  
+----+------+-------------+

 Table 2)   
+-----------------------------------+
|       $[name_of_the_survey]       |
+----+-------+------+-------+-------+
| ID | field | type | value | items |
+----+-------+------+-------+-------+


 Table 3)
+--------------------------------------+
|    $[name_of_the_survey] _records    |
+----+---------------------------------+
| ID | columns specific to each survey |
+----+---------------------------------+

所以基本上当用户创建的一项调查显示，在程序中插入的调查表中的记录，然后创建两个表：

so basically when a user creates a survey, the programs inserts a record in Surveys Table and then creates 2 tables:

表（2）为形式的字段 台（3），用于记录，就可以存储，其中，所述列对应于表（2）的行。

table (2) for the fields of the form table (3) for the records that will be stores, in which the columns correspond to table (2) rows.

它的工作原理，但有一定的局限性。例如，当你要字段添加到表（2），它具有读取表（3）内容，将其保存到一个虚拟的表，删除previous表（3），并创建一个新的。这可以是一个性能问题时，表（3）具有很多的记录。

It works but has some limitations. For instance, when you which to add a field to table (2), it has to read table (3) contents, save it to a virtual table, drop previous table (3) and create a new one. This can be a performance issue when the table(3) has a lot of records.

所以我的问题是...有没有更好的数据库设计？

So my question is... Is there a better database design?

推荐答案

使用一个单独的表，每次调查使用的数据库几乎无效。你还不如结果只存储在文件中。

Using a separate table for each survey nearly invalidates the use of a database. You might as well just store the results in files.

您这样做，不过，需要三个表：调查定义，调查问题，并调查答案。它可能是这个样子......

You do, however, need three tables: Survey Definition, Survey Questions, and Survey Answers. It may look something like this...

Surveys:
ID; name; description

Questions:
ID; text; surveyID

Answers:
ID; answer; questionID

您可以从那里增加复杂性来处理枚举答案...

You could add complexity from there to handle enumerated answers...

Surveys:
ID; name; description

Questions:
ID; text; surveyID

Choices:
ID; choice; questionID

Answers:
ID; choiceID

您使用的每个表之间的关系聚集到一个最高级别，使您可以从任何的问题，调查结果，或任何其他属性您选择，但不尝试抽象掉的来源，为您选择添加任何模型声明。这也可以让你把它们添加到您的模式后，汇总每位用户或稍后测量组织答案。如果每次调查都有自己的表结构，在整个调查汇总的数据变得非常不现实的应用的增长。

You use the relationships between each table to aggregate to the next highest level, allowing you to get results from any question, survey, or any other attributes for any model you choose to add without trying to abstract away the source for your select statements. This also allows you to aggregate answers per user or surveying organization later on after adding them to your schema. If each survey has its own table structure, aggregating data across surveys becomes hugely impractical as your application grows.