Consider the following.
- My Favorite Dog Breed Is
- Beagle
- Basset Hound
- Boxer
- Other, please specify _____________
You can have an answer to a question option which also has a value.
Now consider that both your answer and your question option have their own references to the question. It is possible to have the answer and the answer's question options refer to different questions! This could be constrained with triggers, but it's better to eliminate the redundancy.
That redundancy is there because, as you've designed option 1, a "closed" answer has to refer to the question and an "open" answer will refer to the question via its options.
What I would do is make a "closed" answer to be a question option with no name. An I'd mark whether or not question options can have a value.
In addition, I would remove the enum from question. It is possible to have a question marked as closed and yet have multiple options. The question options are the single source of truth, look at them. If necessary later for optimization, you can add an update/delete/insert trigger on question_option to update the enum.
-- You didn't specify a database, so I'll use PostgreSQL.
create table users (
id bigserial primary key,
name text not null
);
create table questions (
id bigserial primary key,
-- Which is the text of the question? Consider question_text.
-- It's unclear what a description of a question is. Extra
-- explanatory text?
name text not null,
description text
);
create table question_options (
id bigserial primary key,
question_id bigint not null references questions,
-- name is an odd way to describe the text of
-- the option. Label?
-- I went with not null here because this forces
-- you to choose a label or an empty string.
label text not null,
-- Does the option accept a value?
type enum('with value', 'without value') not null
);
create table answers (
id bigserial primary key,
user_id bigint not null references users,
value text,
question_option_id bigint references question_options,
-- It's useful to know when an answer was given.
answered_at timestamp not null default current_timestamp(),
-- Presumably a user can't choose the same to the same question multiple times.
-- If they can, lift the constraint later.
unique(user_id, question_option_id),
constraint answers_filled_in_check check(
values is not null or question_option_id is not null
)
);
An "open question" has one option with a blank label which accepts a value. A closed question can have any number of options with any settings.
A design consideration I did not address: what if a user can answer a question multiple times and you want to store the history of their answers?
