Storing and Displaying Questionnaire Data, Easier Solution ?

Question

Basically this is a questionnaire, but that does not only ask Yes/No type of questions. There are questions that are asked in the form of a table. Here is an example of one of the step pages for the questionnaire:

The questionnaire allows for a client to save what they have entered, log out, come back at a later point in time and continue filling out the rest, then submit. An admin will also be reviewing the questionnaire and allowing the vendor access to only some of the questions in case any corrections are required.

The following text describes my solution to store the data, but i was wondering if there was a simpler way of doing this. Here is also the Database Design for the Questions and Answers table, located on the right side of the image.

The column names for the question should be stored as separate questions as well, except now for instance questions 4.a, will have

4.a.1, "Standard"  
4.a.2, "Certifying Organization"  
4.a.3, "Date of Last Certification"

So to display this would be pretty simple. If we set the type for the question as a new type, for instance TABLECOL, we would know to create a table and table column". Also since the data is going to be pulled out in ascending order then it should not be a problem to create the html for this. Anyhow, right now I think we will be fine with all the cells as text input types. (Maybe in the future, if and when the time comes, one of the columns in a table might not be a text input field, it could for instance be a drop down, so we would then need a way to describe what to use).

Now also when displaying the table in html, question 4.a has three rows as a default. Other questions have a different number. Also i was thinking about validation as well for table columns. So for all of this, i was thinking about creating a new table called QuestionAttributes. This will serve as a way to store many attributes for a question id. So an example of this use would be, question 4.a is a TABLE and should display 3 rows. In the attributes table there would be an entry such as: idof 4.a, "MINROWS", 3

For storing the data in the answers table, we would have to put a new field in the answers table that would give the answer uniqueness and ability to be sort as well. So instead of using an autoincrement value, i would say storing UTC time stamp which would also describe when the answer was given, if need be. This will allow sorting to help us display the data in the correct order in the table of the web page. Basically, in the answers table we should have a different integer value for every answer.

The query to retrieve the answers should have a sort on the Questions table sort_order, and the Answers table utc_timestamp. The result of this query will look something like:

4.a.1, "Answer1", 9878921  
4.a.2, "Answer2", 9878923  
4.a.3, "Answer3", 9878925  
4.a.1, "Answer1", 9878926  
4.a.2, "Answer2", 9878928  
4.a.3, "Answer3", 9878929

Any help would be greatly appreciated.

Answer 1

You're probably going to disagree, but I think the design is way overengineered, especially for a first version. I'd go with as simple a design as possible:

QuestionaireId
Status           f.e. "Pre-Approval"
StatusDate
Answerer         f.e., "Mike Mayhem"
Question1        f.e., "Yes"
Question2        f.e., "Option6"
Question3        f.e., "Blah Blah Blah"
...

Then you can have a log table that says when someone approved an item, answered a question, and so on:

LogId
QuestionaireId
LogDate
LogEntry         f.e., "Questionaire approved by Bill"

For new iterations past the first version, add one-to-many or many-to-many relations only when it adds huge business value. Relations are expensive in terms of complexity, and keeping complexity to a minimum is the essence of a good design.