My real question is when it comes to normalizing data.


I am attempting to tabulate test produces a database. The data Let me record is test_instance, user_id, test_id, completed(date/time), duration (of test), score, incorrect questions and examined questions.

Typically, I believe I'd organise the data based on TABLE 1, but I have come just a little unstuck working out the easiest method to record incorrect or examined questions. Please be aware which i DON'T wish to invest the wrong questions together in a single entry according to TABLE 2.

Let me create a separate entry for every improperly marked question (or examined question).

NOTE: Examined questions are the ones that at some point were marked improperly and therefore have to be examined again.


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


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

Ultimately, Let me know the number of occasions a person has become a specific question wrong with time. Also, I have to keep an eye on which test the incorrect questions originated from. This is actually the same for that examined questions. Incidentally it is possible for inquiries to be examined AND wrong within the same instance.

I have develop 2 various ways to represent the information, however i can't stand either of these.

|  instance   | Q number |  Wrong  |  Reviewed  |


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

Note: Wrong/Examined category is counting the number of occasions the Q number falls into that category.


How do i effectively represent wrong/examined questions inside a table? Is TABLE 1 setup effectively?

EDIT : Questions which have been clarified improperly may be used to generate new tests. Only incorrect questions is going to be employed for the tests. If your produced test is taken, the questions examined is going to be marked as examined. The score won't be up-to-date as it will likely be a brand new make sure a brand new test_id is going to be produced.

NOTE-You'll be able to retake old tests, however the score won't be up-to-date. A brand new instance is going to be produced for every test that's taken.

Regarding the produced tests, I suppose what this means is I will have to include yet another table to keep an eye on which quiz the questions initially originated from. Sorry- I had not thought it completely right through to the finish.


It had been difficult that i can choose a solution as everybody provided really helpful information. My final design will consider all you have stated. Many thanks.