问题描述
我可能缺乏数学背景来为自己找到正确的答案。我有这样的表设置(不相关的列省略): 问题
queID
答案
queID
ansID
用户可以通过挑选某些问题来创建测验。答案是用户可以从每个问题中选择的可能答案。
会话
sessID
当客户选择一组问题回答时,会创建一个会话。
SessionQuestions
sqID
sessID
queID
这些是用户为给定会话选择的问题。
我在哪里碰到一个障碍是在$ code > SessionAnswers 级别。问题的答案顺序可以是随机的。也就是说,具有A,B,C的默认应答顺序的多选题可以被显示为C,B,A给用户。每当他们再次看到这个问题时,它仍然需要是C,B,A,以便最终的订单需要存储。我的暂定表是这样的:
SessionAnswers
sqID
ansID
saOrder
事实是,sqID指向queID,但是ansID也是这样。
这意味着我可以在这个表上使用 sessID
或 sqID
。我不知道要挑哪一个。此设置仍然使ansID可以映射到映射到甚至不在SessionQuestions上的问题的答案,这将是不正确的。可以改进设置以避免这种情况吗?
钻石形依赖关系受益于自然键(而不是代理)。自然键可以在钻石的底部合并,产生正确的引用完整性行为。
在您的情况下,使用 SessionQuestions
( {sessID,queID}
而不是代理 {sqID}
) ,使得所有母键组件能够传播到钻石的两个边缘,使得不可能有一个会话答案,其问题不在同一个会话中。
您的模型应如下所示:
注意:由于每个会话的答案也可以通过订单进行识别,因此您需要在 SessionAnswers
(由上面的'U1'表示)。请注意,该密钥中不应包含 queID
和 ansID
,否则将允许两个不同的答案占用相同slot。
---编辑---
@tandu ,我看到你接受了我的回答,所以我应该在前进的时候停下来),但是我仍然想提出一个替代设计:
这应该可以让你保持历史和秩序。当用户输入一组答案时,它们记录在版本= 1的 SessionAnswers
中。如果一个或多个答案被更改,则创建一个版本= 2,等等...
每个会话版本都有2个集合:
- 一组唯一的问题,每个都有一个答案(通过PK强制执行)。
- 一组用于订购的插槽(通过备用键执行)。
由于两者都包含在同一个表中,这意味着每个问题映射到一个插槽,每个插槽只有一个问题。
BTW,这也使您可以根据需要更改版本之间的顺序。
I may lack the mathematical background to find the right answer for myself. I have tables set up like so (irrelevant columns omitted):
Questions
queID
Answers
queID
ansID
Users can create quizzes by picking certain questions. Answers are the possible answers users can choose from per question.
Sessions
sessID
When a customer picks a group of questions to answer, a "Session" is created.
SessionQuestions
sqID
sessID
queID
These are the questions a user selected for a given session.
Where I'm hitting a snag is at the SessionAnswers
level. The order of answers for a question can be random. That is, a multiple-choice question with default answer order of A,B,C can be displayed as C,B,A to a user. Whenever they view that question again, though, it still needs to be C,B,A, so that final order needs to be stored. My tentative table is this:
SessionAnswers
sqID
ansID
saOrder
The thing is that sqID points to queID, but so does ansID. That means that I could use sessID
on this table or sqID
. I'm not sure which one to pick. This setup still makes it possible for ansID to be mapped to an answer that is mapped to a question that is not even on SessionQuestions, which would be incorrect. Can I improve the setup to avoid that?
Diamond-shaped dependencies benefit from natural keys (as opposed to surrogates). Natural keys can "merge" at the bottom of the "diamond", producing the correct referential integrity behavior.
In your case, using the natural key in SessionQuestions
({sessID, queID}
as opposed to surrogate {sqID}
), enables propagation of all the parent key "components" down both "edges" of the diamond, making it impossible to have a session answer whose question is not in the same session.
Your model should look like this:
NOTE: Since per-session answer can be identified by order too, you need one additional alternate key in SessionAnswers
(denoted by 'U1' above). Note that neither queID
nor ansID
should be included in that key - doing otherwise would allow two different answers to occupy the same "slot".
--- EDIT ---
@tandu, I see you accepted my answer so I should probably stop while I'm ahead ;) but I'd still like to propose an alternative design:
This should enable you to keep both history and order. When user enters a set of answers, they are recorded in SessionAnswers
with version=1. If one or more of the answers are changed, a version=2 is created and so on...
Each session version has 2 sets associated with it:
- A set of unique questions, each with one answer (enforced through PK).
- A set of "slots" used for ordering (enforced through alternate key).
And since both are contained in the same table, this implies that each question maps to exactly one slot and each slot to exactly one question.
BTW, this also enables you to change the order between versions if needed.
这篇关于从多个候选键中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!