数据库规范化:如何将数据制成表格? [英] Database normalization: How can I tabulate the data?

问题描述

我的问题是关于数据标准化.

My question is in regards to normalizing data.

信息

我正在尝试将数据库中的测试结果制成表格.我要记录的信息是test_instance，user_id，test_id，完成的(日期/时间)，测试的持续时间，分数，不正确的问题和已复习的问题.

I'm trying to tabulate test results in a database. The information I'd like to record is test_instance, user_id, test_id, completed(date/time), duration (of test), score, incorrect questions and reviewed questions.

在大多数情况下，我认为我是根据表1来组织信息的，但是我想尽办法找出记录错误或已审查问题的最佳方法有些不为所动.请注意，我不要按照表2将所有不正确的问题放到一个条目中.

For the most part, I think I'd organise the info according to TABLE 1, but I've come a little unstuck trying to work out the best way to record incorrect or reviewed questions. Please note that I DON'T want to put all the incorrect questions together in one entry as per TABLE 2.

我想为每个错误标记的问题(或复习的问题)分别输入一个条目.

I'd like to make a separate entry for each incorrectly marked question (or reviewed question).

注意::已审核的问题是一次或一次被错误标记的问题，因此需要再次进行测试.

NOTE: Reviewed questions are ones that at one time or another were marked incorrectly and hence need to be tested again.

表1

-------------------------------------------------------------   
|  instance   | user_id | test_id |completed |duration|score|   
-------------------------------------------------------------   
|      1      |   23    |   33    | 2JAN2012 |  20m   |  75 |   
|      2      |   11    |   12    | 10DEC2011|  35m   | 100 |   
|      3      |   1     |    3    | 3JUL2008 |  1m    |   0 |   
|      4      |   165   |   213   | 4SEP2010 |  10m   |  50 |   
------------------------------------------------------------- 

表2

------------------------
|  instance   ||wrong Q|
------------------------
|      1      || 3,5,7 |
------------------------

最终，我想知道一段时间以来用户将特定问题弄错了多少次.另外，我需要跟踪错误测试来自哪个测试.对于已审查的问题也是如此. 顺便提一句，有可能在同一情况下对问题进行复查和发现错误.

Ultimately, I'd like to know how many times a user has gotten a particular question wrong over time. Also, I need to keep track of which test the wrong questions came from. This is the same for the reviewed questions. Incidentally it's possible for questions to be reviewed AND wrong in the same instance.

我想出了2种不同的方式来表示数据，但我都不喜欢它们中的任何一种.

I've come up with 2 different ways to represent the data, but I don't like either of them.

------------------------------------------------- 
|  instance   | Q number |  Wrong  |  Reviewed  |
-------------------------------------------------

OR

---------------------------------------------------
| user_id | test_id | Q number | Wrong | Reviewed |
---------------------------------------------------

注意:错误/已审核"类别是在计算Q数属于该类别的次数.

Note: Wrong/Reviewed category is counting how many times the Q number falls into that category.

我的问题已解决

如何有效地在表格中表示错误/复习的问题?表1是否设置有效?

How can I efficiently represent wrong/reviewed questions in a table? Is TABLE 1 set up efficiently?

错误回答的问题可用于生成新测试.测试只会使用不正确的问题.如果进行了生成的测试，则测试的问题将被标记为已复习.分数不会被更新，因为它将是一个新测试并且将生成一个新的test_id.

EDIT : Questions that have been answered incorrectly can be used to generate new tests. Only incorrect questions will be used for the tests. If a generated test is taken, the questions tested will be marked as reviewed. The score will not be updated as it will be a new test and a new test_id will be generated.

注意-可以重新参加旧的考试，但分数不会更新.将为每个参加的测试创建一个新实例.

NOTE-It is possible to retake old tests, but the score will not be updated. A new instance will be created for each test that is taken.

关于生成的测试，我想这意味着我将需要再包含一个表格来跟踪问题最初来自哪个测验.抱歉-我一直都没有想过.

In regards to the generated tests, I guess this means I will need to include one more table to keep track of which quiz the questions originally came from. Sorry- I hadn't thought it all the way through to the end.

谢谢

我很难选择答案，因为每个人都给了我非常有用的信息.我的最终设计将考虑您所说的一切.再次感谢.

It was difficult for me to choose an answer as everyone gave me really useful information. My final design will take into consideration everything you have said. Thanks again.

推荐答案

谈论归一化，只是为了确保您可以从数据库中检索所有类型的计算数据，我建议使用一个更复杂的模型，该模型最终会变得更易于管理...

Talking about normalization, and just to make sure that you can retrieve all kind of calculated data out of your database, I'd propose a more complex model, which will end up in something easier to manage...

您需要以下表格

test_table
    PK: id_test
    testDescription

question_table
    PK: id_question
    FK: id_test
    questionDescription

instance_table *please note that duration and scores will be calculated later on
    PK: id_instance
    FK: id_user
    FK: id_test
    startingTime
    endingTime

question_instance_table
    PK: id_question_instance
    FK: id_instance
    FK: id_question
    questionResult (Boolean)
    (please note here that the PK could be id_instance + id_question ...)

回到您的需求，我们将提供以下内容:

Back to your needs, we then have the following:

• 持续时间是通过instance_table的startingDate和EndingDate计算的
• 分数是来自questionResult字段的真值的总和
• 您可以随时间跟踪和比较一个用户在同一问题上的答案
• 因此，您的已审核问题可以定义为针对特定用户具有至少一个错误值的问题
• 如果您的数据库支持布尔字段为空值，则可以跟踪未回答的问题(questionResult = Null).否则，我建议您使用或构建三个状态字段(允许为Null的整数，例如，加上0和1值)，以跟踪未回答的问题(null)，错误的答案(0)和正确的答案(1).
• 得分为100 *(好答案数)/(测试中的问题数)，可以通过SQL聚合轻松计算出来.
• 您甚至可以将部分分数计算为测试中的好答案数/已回答的问题数.
• 该模型接受任意数量的测试，每项测试任意数量的问题，任意数量的实例，任意数量的用户...
• 当然，可以通过向表中添加缺少的属性(例如testNumber，questionNumber字段)来进一步改进
• 等...

• duration is calculated with startingDate and endingDate of instance_table
• score is calculated as the sum of True values from questionResult field
• you can track and compare answers on same question over time for one user
• thus your reviewed questions can be defined as questions with at least one false value for a specific user
• if your database supports null values for boolean fields, you'll have the possibility to follow unanswered questions (with questionResult = Null). Otherwise, I advise you to use or build a three states field (integer with Null allowed, plus 0 and 1 values for example) to follow unanswered questions (null), wrong answers (0), and correct answers (1).
• Score, being 100 * (number of good answers)/(number of questions in the test), can easily be calculated via SQL agregates.
• You could even calculate partial scores as number of good answers/number of questions answered in the test.
• This model accepts any number of tests, any number of questions per test, any number of instances, any number of users...
• Of course, it can be further improved by adding missing properties to tables (testNumber, questionNumber fields for example)
• etc...

这篇关于数据库规范化:如何将数据制成表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！