将动态表单数据存储在DBMS中，寻找最佳方法 [英] Storing dynamic form data in DBMS, looking for the optimal approach

问题描述

在处理一个将存储一大堆（完全不同）表单的项目时，我面临着一个关于如何存储值，同时保持数据库可用的设计问题。

简要说明：每个文档包含可变数量的问题（尽管每种类型的文档具有一致的金额）和匹配的答案。

我想出了以下，在这里我已按类型分组文档，它标识哪些问题属于文档，作为回报有匹配的问题的答案。

  + --------------- + 1 n + ----------- + 
 | DocumentType | ---------- |问题| 
 + --------------- +有很多+ ----------- + 
 | 1 1 | 
 | n是类型n |属于
 + --------------- + 1 n + ----------- + 
 | DocumentEntry | ---------- |答案| 
 + --------------- +有很多+ ----------- + 
   
 
 
我想知道我是否偶然发现存储数据的最佳方法，或者如果有一些整洁的解决方案，我可能会错过。你遇到了一个常见的问题：尝试使用静态（具有预定义结构的数据库）的动态（一堆个体数据）集只有一个共同点：它们来自形式）。你想要的是与数据库，但是明显更容易做，但是，因为我假设你真的想使用数据库为这，这里是我会做：
 
 
 
 
 
您有文件（或问卷），其中包含多个 questions 。 
 
 
每个问题都有一个类型定义了什么样的问题（多个选择，自由格式，选择一个... ），当然问题也有选项。这是两个表更多。这里的推理是，将这些与实际问题解耦允许存在一定程度的抽象，并且您的查询仍然会有些简单，尽管可能会非常简单。
 
 
 
 
 
所以，每个文档都有1..n的问题，每个问题有1种类型和1..n选项。跳过一点，这里是我正在想的链接表等。
 文档
 bigint id 
 DocumentQuestions 
 bigint document_id 
 bigint question_id 
 Question 
 bigint id 
 varchar question 
 QuestionType 
 bigint question_id 
 bigint type_id 
键入[使用id：type对的预填充表，例如1 = freeform，2 =选择一个。] 
 QuestionOptions 
 bigint id 
 bigint question_id 
 varchar description 
 varchar value 
 
 Answers 
 bigint id 
 bigint document_id 
 [etc.例如user_id] 
 QuestionAnswers 
 bigint answer_id 
 bigint question_id 
 bigint questionoptions_id 
  
这种设计允许多种操作：
 
 
 
 
 
问题本身是可重用的， 
  
 
 
 
 
可以轻松添加新类型，而不会破坏现有类型。
 
 
您可以随时浏览整个结构，例如这个单个问题答案的文档名称是什么？或有多少人回答错误一个问题？
 
 
由于类型是分开的，您可以创建一个（网络）UI，轻松地反映数据库中的状态 - 更好的是，如果类型更改， 
 
 
由于问题的每个可能选项都是 QuestionOptions 表中的自己的行，因此您必须触摸您的UI代码。 
 
  
 
 
这是一个明显的问题，它需要表之间相当严格的耦合，是一个痛苦，以在开始正常运行。此外，由于 QuestionOptions 中的 value 是varchar，所以您需要能够解析很多东西，为转换提示引入另一个字段。
 
 
 
希望这有助于即使你不同意我的解决方案。
 
While working on a project that will store a whole bunch of (completely different) forms I'm facing a design issue on how to store the values while keeping the database usable.

Brief description: each 'document' contains a variable amount of questions (though a consistent amount per type of document) and matching answers.

The most usable approach I've come up with is the following, here I've grouped documents by 'type', which identifies which questions belong to the document, which in return has the answers to the matching questions.
  +---------------+ 1      n +-----------+
  | DocumentType  |----------| Questions |
  +---------------+ Has many +-----------+
         |1                      1|
         |n Is of type           n| Belongs to
  +---------------+ 1      n +-----------+
  | DocumentEntry |----------|  Answers  |
  +---------------+ Has many +-----------+
The drawback here is that queries on fetching the documents that have question A with answer B become rather complex and likely rather slow when the database grows larger, which it rapidly will.

I'm wondering if I've stumbled across the optimal approach to store the data or if there is some neat solution out there that I might've missed.
解决方案
You've faced a common problem: Trying to use something static (database with predefined structure) for something dynamic (bunch of individual data sets which only have one thing in common: they come from forms). What you want is doable with databases but would be significantly easier to do without, however since I assume you really do want to use a database for this, here's what I'd do:


You have a document (or questionnaire) which contains multiple questions. These both are generic enough and require their own tables, just as you've done so far.
Each question has a type which defines what kind of question it is (multiple select, freeform, select one...) and of course the question also has options. So that's two tables more. The reasoning here is that decoupling these from the actual question allows for certain level of abstraction exist and your queries will still be somewhat simple although possibly loooooong.


So, each document has 1..n to questions, each question has 1 type and 1..n options. Skipping a bit, here's what I'm thinking of with link tables etc.
Document
    bigint id
DocumentQuestions
    bigint document_id
    bigint question_id
Question
    bigint id
    varchar question
QuestionType
    bigint question_id
    bigint type_id
Type [pre-filled table with id:type pairs, such as 1=freeform, 2=select one etc.]
QuestionOptions
    bigint id
    bigint question_id
    varchar description
    varchar value

Answers
    bigint id
    bigint document_id
    [etc. such as user_id]
QuestionAnswers
    bigint answer_id
    bigint question_id
    bigint questionoptions_id
This sort of design permits several things:


Questions themselves are reusable, very handy if you're making a generic "answer these x random questions from a pool of y questions".
New types can be added easily without breaking existing ones.
You can always navigate through the structure quite easily, for example "What was the name of the document for this single question answer I have?" or "how many people have answered incorrectly to this one question?"
Because types are separated, you can create a (web) UI which reflects the state in the database easily - better yet, if the type changes you may don't even have to touch your UI code at all.
Since each possibly option for a question is its own row in the QuestionOptions table, you can get the actual value very easily.


The obvious problem with this is that it requires quite strict coupling between the tables for integrity and will be a pain to get running properly at start. Also since value in the QuestionOptions is varchar, you need to be able to parse stuff a lot and you may even want to introduce another field for conversion hints.

Hope this helps even though you wouldn't agree with my solution at all.

                        
这篇关于将动态表单数据存储在DBMS中，寻找最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！