对于在线问卷，如何设计一个数据库来跟踪所有用户的尝试？ [英] For an online questionnaire, how to design a database for keeping track of all users attempts?

问题描述

我们有一个网络应用程序，用户可以在其中进行在线考试。

We have a web app where users can take online exams.

考试管理员将创建一个调查表。问卷可以包含许多问题。每个问题都是一个选择题（MCQ）。

Exam admin will create a questionnaire. A questionnaire can have many Questions. Each question is a multiple choice question (MCQ).

让我们说一个管理员创建了一个包含10个问题的问卷。用户尝试这些问题。现在，与真实考试不同，用户可以尝试多次问卷。我们必须跟踪他的所有尝试。

Lets say an admin creates a questionnaire with 10 questions. Users attempt those questions. Now, unlike real exams users can attempt single questionnaire multiple times. And we have to keep track of his all attempts.

例如

    User_id     Questionnaire_id    question_id answer  attempt_date    attempt_no
1       1           1       a   1 June 2013 1
1       1           2       b   1 June 2013 1

1       1           1       c   2 June 2013 2
1       1           2       d   2 June 2013 2

现在，如果用户两次尝试相同的问题，管理员可以从同一调查表中删除一个问题，但是用户尝试历史记录仍应参考该问题，这样用户就可以看到自己的问题了。尽管管理员删除了该问题，但他的尝试历史记录中仍存在该问题。

Now it can also happen that after user has attempted same questionnare twice, admin can delete a question from same questionnaire, but users attempt history should still have reference to that so that user can see his that question in his attempt history in spite of admin deleting that question.

如果用户现在尝试使用此已更改的问卷，那么他应该只会看到一个问题。

If user now attempts this changed questionnaire he should see only 1 question.

    User_id     Questionnaire_id    question_id answer  attempt_date    attempt_no
1       1           1       a   3 June 2013 3

此外，在此用户修改了问题的某些部分后，用户尝试历史记录应在修改之前显示问题，而任何新尝试都应显示修改后的问题。

Also, after this user modified some part of question, users attempt history should show question before modification while any new attempt should show modified question.

我们如何在数据库级别进行管理？

How do we manage this at the database level?

我的第一个直觉是

对于删除，请不要进行物理删除，只需将问题设为非活动状态，以便历史记录仍可跟踪用户的尝试。

For deletes, do not do physical delete, just make a question inactive so that history can still keep track of users attempt.

要进行修改，请为问题创建版本，每次新尝试均应参考每个问题的最新版本，并在尝试时始终参考该问题的历史记录。

For modifications, create versions for questions and each new attempt refres to latest version of each question and history keeping reference to version of question at attempt time.

推荐答案

（对不起，我用考试代替了调查表，后者对我的图表来说太笨拙了。）

是的，您必须进行某种形式的版本控制。孤立地对对象进行版本控制很容易，但是对象之间的 links 版本控制可能会很快变得复杂。为了使它（相对）简单，可以执行以下操作：

Yes, you'd have to do some form of versioning. Versioning objects in isolation is easy enough, but versioning links between objects can get complicated in a hurry. To keep it (relatively) simple, you could do something like this:

这形成了一个层次结构，可以根据变化以自下而上的方式进行版本控制：

This forms a hierarchy, that can be versioned in a bottom-up fashion in response to changes:

• 修改答案文字，添加或删除答案或修改问题文字均视为对问题的修改。


• 将其与问题的添加或删除一起视为对考试的修改。


• 这是通过创建新的考试版本并复制旧考试下的完整树来完成的版本，同时应用适当的更改。

这种分层的版本控制方法，即根据响应创建整个树的新版本修改任何树节点可能会有点浪费，但是对于推理和实现来说却非常简单。

This "hierarchical" approach to versioning, i.e. creating a new version of the whole tree in response to modification of any tree node, can be a bit wasteful, but is fairly straightforward to reason about and implement.

将明确显示子对象和链接的版本，然后仔细查询，以便仅检索特定的及时快照。显然，这将需要完全不同的数据库架构...

An alternative would be to explicitly version child objects and links, and then carefully query so only the specific "snapshot in time" is retrieved. This would obviously require a significantly different database schema...

上面偶然地，您会注意到对识别关系以及由此产生的复合主键。为了正确地建模位于USER_ANSWER底部的菱形依赖关系，这是必需的，因此用户无法提供不属于该考试的答案。

Incidentally to above, you'll notice a prodigious use of identifying relationships and the resulting composite primary keys. This is necessary for correctly modelling the diamond-shaped dependency that bottoms at USER_ANSWER, so the user cannot provide an answer that doesn't belong to the exam.

QUESTION.ANSWER_NO有助于识别正确的答案（这假设 MATCH SIMPLE 外键-另请参见此信息）。

QUESTION.ANSWER_NO helps identify the correct answer (this assumes MATCH SIMPLE foreign keys - also see this post).

ATTEMPT.TIMESTAMP是用户的日期/时间开始了她的尝试。连同给出单个答案的时间，可以重新创建完整的时间表。考试的时间是从尝试开始到最后一个答案之间的时间。

ATTEMPT.TIMESTAMP is the date/time at which the user started her attempt. Together with the times at which individual answers were given, the complete "timeline" can be recreated. The length of exam is the period between attempt start and the last answer.

USER_ANSWER.ANSWER_NO保留在主键之外，因此没有两个主键可以在同一尝试中为同一问题提供不同的答案。

USER_ANSWER.ANSWER_NO is kept outside the primary key, so no two different answers can be provided to the same question on the same attempt.

DELETED标志存在于考试版本中，而不是考试本身，因此如果您在那里取消删除考试将是一个历史记录。您甚至可以多次删除和取消删除同一项考试。

The DELETED flag exists in the exam version, not the exam itself, so if you undelete the exam there will be a historical record of that. You can even delete and undelete the same exam multiple times.

这篇关于对于在线问卷，如何设计一个数据库来跟踪所有用户的尝试？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！