mysql - Storing dynamic form data in DBMS, looking for the optimal approach -

While working on a project that will completely store (completely different) forms. To store values.

Brief description: Each 'document' has a variable amount of questions (though a constant amount in each type of document) and matching answers.

The most useful usable approach is the following, here I have grouped the documents by 'type', which tells which questions are related to the document, in return for the answers to the matching questions .

+ --------------- + 1n + ----------- + | Document type ---------- | Questions | + --------------- + There are so many + ----------- + | 1 1 | | Is of N type. + --------------- + 1 n + ----------- ++ belongs to the documentinterity | ---------- | Answer: + --------------- + + There are so many ----------- +

The flaw here is that To get the documents that have a question, Answer B is complex and potentially slow when the database gets bigger, which will do it faster.

I am thinking that I have stumbled in the store's optimal view of data or if there is some clean solution that I may remember.

You encountered a common problem: some dynamic (bunch of some different data sets One thing in general: they come from forms) are trying to use some stable (predefined structure database). Do you want to do cobbler with the database, but without it would be easy enough, since ever since I think that you really want to use a database for this, what do I want to do here

• You have a document (or questionnaire ) which contains multiple questions are both common And your own table is required, as you've done so far
• Each question has a type that defines what type of question it is ( select multiple selections, freeforms, ... ) And of course there are also options in question. So this is two more tables here. The argument is that this decoupling from the actual question allows for some abstract intangible existence and possibly loooooong though your questions will be a bit simpler. Therefore, in each document 1. For questions, in each question 1 type and 1 .. N options are a little skipping, here I am thinking about link tables etc.

    document bigint id document queries bigint document_id bigint question_ id bigint id varchar question type type bigint question_ id bigint type_id type [with pre-filled table id: type pairs, like 1 = freeform, 2 = Select one etc.) Q & A point ID big question Question: varchar details varchar value answer bigint id bigint document_id [etc. Such as user_id] allows such a variety of designs:  
   
   Reusable yourself, very easy if you ' 
   without breaking the existing types New types can be easily added.  
   You can always always navigate through the structure, for example " what is the name of the document for answering this single question? " or "how many people Has the wrong answer to this question? "  
   Types are separated, so you can create a web UI that easily shows the state in the database - better yet, if the type changes, you can touch your UI code at all Does not fall.  
   Since every possible option for the question is its line in the  questionsonline  table, so you can get real value very easily. 
   
   
   T It is a clear problem with the fact that it requires a very strong coupling between the tables for integrity and in the beginning it will be painful to walk properly. Since then, the  value  has  questions> varchar, you should be able to purge the content and you may want to apply another area for conversion signals too. 
   
   Hope it helps, even if you do not agree with your solution at all.