how to associate columns between differnt tables So i'm designing a database for my application which is basically a qustionaire. I designed my db as best as i could and i think it kinda works.
The questionair isn't straightfoward. by this i mean there are multiple questionairs and the questions are not a simple (yes/no). some questions are multiple choice (lists) some are a yes/no (enum) and for some qusetions i have a other 'option' in which users can input string.
Here are some questions which i think illustrate the returned data types:
q1) do you like cats?

yes
no

q2) pick toppings on your burger?

onions
tomatoes
cheese
pickles

q3) what is your favourite number?

one
two
three
other    [ ' enter your favourite number ' ]

What i am trying to figure out
How can i associate questions with their possible &quot;options&quot;?
While a better solution might exist, a simple way would be to have a Questions table with QuestionId as the primary key, QuestionText column, and an QuestionType column so your consuming application can render the appropriate control (e.g. Q1 likely should be a radio button vs Q2 which would be a checkbox).
And have an Answers table with an AnswerId primary key column, the QuestionId it belongs to as a foreign key reference, and an AnswerText column so it can hold any data type of answer. It may be helpful to also have a column called AnswerDataType so you can dynamically cast the AnswerText to the underlying data type when needed.