从多个候选键中进行选择
我可能缺乏数学背景来为自己找到正确的答案。我的表格设置如下（省略了不相关的列）：
Questions
queID
Answers
queID
ansID
用户可以通过选择某些问题来创建测验。答案是用户可以从每个问题中选择的可能答案。
Sessions
sessID
当客户选择要回答的一组问题时，就会创建一个“会话”。
SessionQuestions
sqID
sessID
queID
这些是用户为给定会话选择的问题。
我遇到的障碍是在 SessionAnswers 级别。问题的答案顺序可以是随机的。即，默认回答顺序为A、B、C的选择题可以向用户显示为C、B、A。不过，每当他们再次查看该问题时，它仍然需要是 C、B、A，因此需要存储最终顺序。我的暂定表是这样的：
SessionAnswers
sqID
ansID
saOrder
问题是 sqID 指向 queID，但 ansID 也指向 queID。这意味着我可以在此表上使用 sessID 或 sqID。我不确定该选哪一个。此设置仍然可以将 ansID 映射到一个答案，而该答案映射到一个甚至不在 SessionQuestions 上的问题，这是不正确的。我可以改进设置来避免这种情况吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
菱形依赖关系受益于自然键（而不是代理）。自然键可以在“菱形”的底部“合并”，产生正确的引用完整性行为。
在您的情况下，使用
SessionQuestions中的自然键（{sessID, queID}而不是代理{sqID}），可以传播所有父键“组件”沿着菱形的两个“边缘”，使得不可能有问题不在同一会话中的会话答案。您的模型应如下所示：
注意：由于每个会话的答案也可以按顺序识别，因此您在
SessionAnswers中需要一个额外的备用密钥（上面用“U1”表示）。请注意，queID和ansID都不应该包含在该键中 - 否则将允许两个不同的答案占用相同的“槽”。--- 编辑 ---
@tandu，我看到你接受了我的答案，所以我可能应该在领先时停下来;)但我仍然想提出一个替代设计：
这应该使您能够保留两者历史记录和顺序。当用户输入一组答案时，它们会记录在
SessionAnswers中，版本=1。如果一个或多个答案发生更改，则会创建 version=2，依此类推...每个会话版本都有 2 个与之关联的集合：
由于两者都包含在同一个表中，这意味着每个问题恰好映射到一个插槽，并且每个插槽恰好映射到一个问题。
顺便说一句，这还允许您根据需要更改版本之间的顺序。
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 neitherqueIDnoransIDshould 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
SessionAnswerswith 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:
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.