在数据库中表示具有输入依赖性的径向

Question

I would just like to ask how to best represent in database schema the forms with radial button (Y/N) that if yes, would have an input field. Like "Are you taking medications right now?" (o) Yes or (o) No. If yes is selected, the input field would become available and they have to write it down. Currently I have this very simple schema:

tblUser
-----------
user_id

tblAnswers
--------------
answer_id
user_id
question_id
answer


tblquestions
----------------
question_id
question_text

I think the schema I have right now is fine for most things: multiple checkboxes as an answer, radial buttons, etc. But for radial buttons with dependent input field, I'm not sure. I would like to have it straightforward and simple by the way...I only have 1 survey per 1 user. And the questions won't change for a long long time; perhaps even never. Then again, maybe my current schema would work for radial with input dependency...instead of Yes as an answer, I'll have the input field value instead? So in tblanswer I could have an answer like Stress pills (instead of Yes) or No. Would that be acceptable? Or is that a bad idea? Any help would be greatly appreciated.

Answer 1

I will offer a solution to the radio button, i will not adress your table structure as it is irrelevant for this answer, but i'll leave a tip here: forget normalization rules, they are impractical in most cases.

Now, on to your problem, you are describing 3 possible outcomes (YES, NO, and TEXT) but in fact you have only 2 (NO and TEXT) as the existence of TEXT means already the answer was YES.

So, what you should do is prepare your inputs to send "null" or "text", use some javascript to show/hide a text input and get some logic behind the INSERT query to detect if it should save the input as null (for radio off) or as $_POST['foo'] (for radio on).