I am working on a questionnaire application. The
application collects details about the user and stores
them in a database. The Admin can add/edit and delete
the questions as well as the options for the dropdown.
While working on this application, for 2 problems i
came up with 2 solutions each. I have mentioned them below. Could you please tell me which solution would be better?

Problem1:
I use a Response table to store the responses of all the users and a Question table to store all the questions.

Table: Question

Q-NO Questions Active Dropdown
1 Enter your name yes no
2 Location Yes yes
. . . .
. . . .


Active column is used by the Admin to soft delete (i.e. no more active) the question.
Dropdown column indicates if the response is dropdown or descriptive.


Now, the problem is how to store the responses. I have 2 options
Option 1:

Table: Response

Q_1 | Q_2 | Q_3 | Q_4 . . .
     | | |
     | | |


Here each time the Admin adds a new question, the question will be added in the Question table and a new column corresponding to the question will be added to the Response table.

Option 2:

Table: Response

Emp_no Q_NO Response
53542 1 --------
53542 2 --------
53542 25 --------
53547 1 --------
53547 2 ---------

Here there is no addition of columns. So each time the admin adds a question it will be stored in the Questions table. But no columns will be added to the Response table. The question number and the response is stored.

Could you please tell me which option would be better for Response table considering adding columns dynamically(option1) and the retrieval efficiency(option 2)?


Problem 2:

Problem 2 is how to store the dropdown values for the different questions. Even here I came up with 2 solutions.

Option 1:
A single table for all options.


Q_no Values
1 Accept
1 Decline
1 Tentative
3 US
3 UK
3 AUS



Option 2:
Maintain separate table for each dropdown. I.e. if the added question is of dropdown type, then create a table for that question with a single column.

Table: Q-1
Values
Accept
Decline
Tentative



Table: Q-3
Values
UK
US
AUS
IND


Could you tell me which of the above 2 options would be better considering the space utilization (option 2) and retrieval efficiency(option 1)?

Thanks