由于 ChoiceId
已经识别出 ,所以如果你想在数据中完全正常化,你可以在 Answer
表格中清除 问题Id
。
我将采用以下结构:
create table questions (
question_id number primary key,
question_text text
);
create table questions_choices (
question_id number references questions(question_id),
choice_number number,
choice_text text,
primary key(question_id, choice_number)
);
create table answers (
answer_id number primary key,
user_id number references users(user_id),
question_id number,
choice_number number,
foreign key (question_id, choice_number) references questions_choices(question_id, choice_number)
);
这与您拥有的类似,但在 questions_choices
表格上没有替代键。