Database normalization: How can I tabulate the data?

Question

My question is in regards to normalizing data.

INFO

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.

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.

TABLE 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 |   
------------------------------------------------------------- 

TABLE 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.

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 |
---------------------------------------------------

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

MY QUESTIONS SUMMARISED

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

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.

THANKS

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.

Answer 1

Revisiting my answer after you updates, I came up with this kind of layout which I think would work quite nicely.

As a prerequisite, I'm assuming you have your tests and questions somewhere. For consistency, I'm including them (with only relevant columns) in my layout.

USERS
- user id

TESTS
- test id

QUESTIONS
- question id
- test id

Then for the interesting part. Considering how you say:

Questions that have been answered incorrectly can be used to generate new tests. Only incorrect questions will be used for the tests

You don't mention how many times a test can be retaken, I assume an indefinite or at least more than one time.

TEST INSTANCE
- instance id [PK]
- revision id [PK]
- user id
- completed
- duration

COMMENT: you may want to consider replacing completed and duration with 
a start and end timestamp. They will serve the same purpose without 
the need for any calculations at insert/update.

TEST INSTANCE SCORE
- instance id [FK, TEST INSTANCE (instance id)]
- score

FAILED QUESTIONS
- question id [FK, QUESTION (question id)]
- instance id [FK, TEST INSTANCE (instance id)]
- reviewed [FK, TEST INSTANCE (revision id)]

Then to my comments.

As I see it, a new actual test for the failed questions wouldn't make sense, so instead I added a revision id to the TEST INSTANCE table. Each time a test is retaken, a new record for the same instance id with a revision id (e.g. a running number sequence) is created.

Any failed questions would be stored in FAILED QUESTIONS along with the instance id and initially a NULL value for reviewed. When a failed question is considered reviewed, its reviewed column would be updated with the revision id of the latest test instance for instance id.

With this approach, you will have a complete history of how many times a failed question has been attempted before it was successfully reviewed.

Furthermore, I chose in my revised answer to move the score to its own table, because you said scores won't be updated despite reviewing the failed questions and my proposed model would have introduced data duplication. You'll notice I left out the revision id from that table, because for a test instance (and any number of revisions) there is only one score.

Answer 2

From the above example I assume that instance correlates directly to user_id+test_id combination. If that is so, you can consider having table 2 in the following format:

Instance | question_id | status | date

PK for the table should be on instance, question_id and status.

entries in this table will not be updated, only inserted. That way you can have:

Instance | question_id | status | date
   1           3            W     1/1/2011
   1           3            R     1/5/2011

this will allow you complete tracking of wrong and reviewed questions, and the date of review. if you don't need the date of review, don't define this column :)

You can add a unique index on instance and status fields, so when you access the table your search will be more efficient.

*Additional data that can be added to the 2nd table is "new test_id" and "new question_id" for reviewed questions, so that you can check if for the same question (assuming question_id is generated each time) you still have failures.

Answer 3

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...

You'll need the following tables

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:

• 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...

Answer 4

Assuming the number of questions on a test don't change, and that each question is worth the same number of marks, I suggest the following tables:

test
----
test_id
number_of_questions

test_instance
-------------
instance_id
user_id
test_id
completed
duration

notable_questions
-----------------
instance_id
question_id
status (W - Wrong, R - Reviewed)

So, for example:

test:
---------------------------------
| test_id | number_of_questions |
---------------------------------
|     3   |         50          |
|    12   |        100          |
|    33   |         78          |
|   213   |         50          |
---------------------------------

test_instance:
-------------------------------------------------------
| instance_id | user_id | test_id |completed |duration|
-------------------------------------------------------
|      1      |   23    |   33    | 2JAN2012 |  20m   |
|      2      |   11    |   12    | 10DEC2011|  35m   |
|      3      |   1     |    3    | 3JUL2008 |  1m    |
|      4      |   23    |   213   | 4SEP2010 |  10m   |
-------------------------------------------------------

notable_questions:
------------------------------------
| instance_id |question_id| Status |
------------------------------------
|      1      |     3     |    W   |
|      1      |     5     |    W   |
|      1      |     7     |    W   |
|      4      |     2     |    R   |
------------------------------------