How to transform complex form/poll questions to SQL tables?

Question

I need to make system design out of some big form/poll and transform all of that in SQL tables and make db out of it. My problem are questions in which user can choose answer or if the answer is not preset to write it down. So for example question is:

1. Where did you bought your groceries?

1 - walmart

2 - kaufland

3 - lidl

4 - something else: _____________

My problem is that answer "4 - something else" where they can write anything. I will have big table that holds all the answers from the poll but I don't really know how to implement this type of question.

My first solution was to just make column for the question like: "groceries_place varchar(500)" inside my poll table and to just insert value of answer (so walmart or kaufland or lidl, not id of answer) or I just insert value that they answered (so value of answer 4 that they input).

Can somebody please help me and answer if there are any solutions where I can inside my poll table get the answer by id somehow and not write it down?

Answer 1

I am thinking that for this particular example you want an answer key like this:

create table userAnswers (
    userAnswerId int generated always as identity primary key,
    questionId int not null references questions(questionId),
    choice int,  -- or perhaps char(1)
    userInput varchar2(255)
);

You probably want an Answers table with valid answers and choice would refer to that table.

I should note that designing a database for survey responses is much more complicated than this. This only addresses the question that you have asked here.